DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Re: maintaining data when importing into excel

  1. #1
    denis pelletier Guest

    Re: maintaining data when importing into excel


    I am trying to figure out a way to import an xml file into excel and keep
    it from dropping leading zeros in one of the fields. The data should be,
    let's say, 029456, but when I open the excel file after importing it I am
    only seeing 29456. Is there a way to tell excel not to drop the leading
    zeros?

  2. #2
    MarkN Guest

    Re: maintaining data when importing into excel


    Tell the field is text. Or put a format on it.

    "denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >
    >I am trying to figure out a way to import an xml file into excel and keep
    >it from dropping leading zeros in one of the fields. The data should be,
    >let's say, 029456, but when I open the excel file after importing it I am
    >only seeing 29456. Is there a way to tell excel not to drop the leading
    >zeros?



  3. #3
    denis pelletier Guest

    Re: maintaining data when importing into excel


    "MarkN" <enterprise.@127.0.0.1> wrote:
    >
    >Tell the field is text. Or put a format on it.
    >
    >"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>
    >>I am trying to figure out a way to import an xml file into excel and keep
    >>it from dropping leading zeros in one of the fields. The data should be,
    >>let's say, 029456, but when I open the excel file after importing it I

    am
    >>only seeing 29456. Is there a way to tell excel not to drop the leading
    >>zeros?

    >



  4. #4
    denis pelletier Guest

    Re: maintaining data when importing into excel


    I am creating the xml on the fly and then opening it in an excel wooksheet
    in the browser from a dataset in .Net. The datatype for the field coming
    from the database is varchar2 into the dataset but if it only contains numbers
    it drops the leading zeros in excel. Not sure how to format or tell the field
    is text on the fly.

    "MarkN" <enterprise.@127.0.0.1> wrote:
    >
    >Tell the field is text. Or put a format on it.
    >
    >"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>
    >>I am trying to figure out a way to import an xml file into excel and keep
    >>it from dropping leading zeros in one of the fields. The data should be,
    >>let's say, 029456, but when I open the excel file after importing it I

    am
    >>only seeing 29456. Is there a way to tell excel not to drop the leading
    >>zeros?

    >



  5. #5
    MarkN Guest

    Re: maintaining data when importing into excel


    So how are you opening xml in Excel? What version of Excel? You might try
    using a template with the data (???).

    "denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >
    >I am creating the xml on the fly and then opening it in an excel wooksheet
    >in the browser from a dataset in .Net. The datatype for the field coming
    >from the database is varchar2 into the dataset but if it only contains numbers
    >it drops the leading zeros in excel. Not sure how to format or tell the

    field
    >is text on the fly.
    >
    >"MarkN" <enterprise.@127.0.0.1> wrote:
    >>
    >>Tell the field is text. Or put a format on it.
    >>
    >>"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>>
    >>>I am trying to figure out a way to import an xml file into excel and keep
    >>>it from dropping leading zeros in one of the fields. The data should

    be,
    >>>let's say, 029456, but when I open the excel file after importing it I

    >am
    >>>only seeing 29456. Is there a way to tell excel not to drop the leading
    >>>zeros?

    >>

    >



  6. #6
    Denis Pelletier Guest

    Re: maintaining data when importing into excel


    I am running Office 2000 version of Excel. I have tried opening the file
    just as open/file. I have tried importing the xml and in the properties
    section of the import, have the preserve cell format checked off and I have
    also tried importing the file into a template with all the cells formatted
    as Text to preserve the format of the data coming and all of these methods
    continue to drop the leading zeros on my fields. If you have any other idea's
    or any examples that you know of that work please let me know. I am looking
    into xsl stylesheets but haven't found anything yet.

    "MarkN" <enterprise.@127.0.0.1> wrote:
    >
    >So how are you opening xml in Excel? What version of Excel? You might

    try
    >using a template with the data (???).
    >
    >"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>
    >>I am creating the xml on the fly and then opening it in an excel wooksheet
    >>in the browser from a dataset in .Net. The datatype for the field coming
    >>from the database is varchar2 into the dataset but if it only contains

    numbers
    >>it drops the leading zeros in excel. Not sure how to format or tell the

    >field
    >>is text on the fly.
    >>
    >>"MarkN" <enterprise.@127.0.0.1> wrote:
    >>>
    >>>Tell the field is text. Or put a format on it.
    >>>
    >>>"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>>>
    >>>>I am trying to figure out a way to import an xml file into excel and

    keep
    >>>>it from dropping leading zeros in one of the fields. The data should

    >be,
    >>>>let's say, 029456, but when I open the excel file after importing it

    I
    >>am
    >>>>only seeing 29456. Is there a way to tell excel not to drop the leading
    >>>>zeros?
    >>>

    >>

    >



  7. #7
    MarkN Guest

    Re: maintaining data when importing into excel


    Weird. I have Office 2k and it knows nothing about xml. And I can't find
    any 'import' commands - Just open|file which then becomes import if necessary.
    But XML is not listed as a type.

    It seems that it recognizes and treats it like a number until it imports
    it (so it chops off the zeros) and then converts to a string. Trying using
    a custom format on the field in your template (ie: 000000).

    "Denis Pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >
    >I am running Office 2000 version of Excel. I have tried opening the file
    >just as open/file. I have tried importing the xml and in the properties
    >section of the import, have the preserve cell format checked off and I have
    >also tried importing the file into a template with all the cells formatted
    >as Text to preserve the format of the data coming and all of these methods
    >continue to drop the leading zeros on my fields. If you have any other

    idea's
    >or any examples that you know of that work please let me know. I am looking
    >into xsl stylesheets but haven't found anything yet.
    >
    >"MarkN" <enterprise.@127.0.0.1> wrote:
    >>
    >>So how are you opening xml in Excel? What version of Excel? You might

    >try
    >>using a template with the data (???).
    >>
    >>"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>>
    >>>I am creating the xml on the fly and then opening it in an excel wooksheet
    >>>in the browser from a dataset in .Net. The datatype for the field coming
    >>>from the database is varchar2 into the dataset but if it only contains

    >numbers
    >>>it drops the leading zeros in excel. Not sure how to format or tell the

    >>field
    >>>is text on the fly.
    >>>
    >>>"MarkN" <enterprise.@127.0.0.1> wrote:
    >>>>
    >>>>Tell the field is text. Or put a format on it.
    >>>>
    >>>>"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>>>>
    >>>>>I am trying to figure out a way to import an xml file into excel and

    >keep
    >>>>>it from dropping leading zeros in one of the fields. The data should

    >>be,
    >>>>>let's say, 029456, but when I open the excel file after importing it

    >I
    >>>am
    >>>>>only seeing 29456. Is there a way to tell excel not to drop the leading
    >>>>>zeros?
    >>>>
    >>>

    >>

    >



  8. #8
    mark Guest

    Re: maintaining data when importing into excel


    There is another way to approach this. Precede your field data with a leading
    apostrophe. Excel interprets this as a text field without displaying the
    apostrophe to the user.
    Mark.

    "MarkN" <enterprise.@127.0.0.1> wrote:
    >
    >Weird. I have Office 2k and it knows nothing about xml. And I can't find
    >any 'import' commands - Just open|file which then becomes import if necessary.
    > But XML is not listed as a type.
    >
    >It seems that it recognizes and treats it like a number until it imports
    >it (so it chops off the zeros) and then converts to a string. Trying using
    >a custom format on the field in your template (ie: 000000).
    >
    >"Denis Pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>
    >>I am running Office 2000 version of Excel. I have tried opening the file
    >>just as open/file. I have tried importing the xml and in the properties
    >>section of the import, have the preserve cell format checked off and I

    have
    >>also tried importing the file into a template with all the cells formatted
    >>as Text to preserve the format of the data coming and all of these methods
    >>continue to drop the leading zeros on my fields. If you have any other

    >idea's
    >>or any examples that you know of that work please let me know. I am looking
    >>into xsl stylesheets but haven't found anything yet.
    >>
    >>"MarkN" <enterprise.@127.0.0.1> wrote:
    >>>
    >>>So how are you opening xml in Excel? What version of Excel? You might

    >>try
    >>>using a template with the data (???).
    >>>
    >>>"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>>>
    >>>>I am creating the xml on the fly and then opening it in an excel wooksheet
    >>>>in the browser from a dataset in .Net. The datatype for the field coming
    >>>>from the database is varchar2 into the dataset but if it only contains

    >>numbers
    >>>>it drops the leading zeros in excel. Not sure how to format or tell the
    >>>field
    >>>>is text on the fly.
    >>>>
    >>>>"MarkN" <enterprise.@127.0.0.1> wrote:
    >>>>>
    >>>>>Tell the field is text. Or put a format on it.
    >>>>>
    >>>>>"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>>>>>
    >>>>>>I am trying to figure out a way to import an xml file into excel and

    >>keep
    >>>>>>it from dropping leading zeros in one of the fields. The data should
    >>>be,
    >>>>>>let's say, 029456, but when I open the excel file after importing it

    >>I
    >>>>am
    >>>>>>only seeing 29456. Is there a way to tell excel not to drop the leading
    >>>>>>zeros?
    >>>>>
    >>>>
    >>>

    >>

    >



  9. #9
    Joey Brenn Guest

    Re: maintaining data when importing into excel


    Look at the msdn.microsoft.com and look for XML-SS format. There are articles
    and an example on the website. This is something I want to do but haven't
    had time to start on it yet.

    Joey Brenn

    "Denis Pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >
    >I am running Office 2000 version of Excel. I have tried opening the file
    >just as open/file. I have tried importing the xml and in the properties
    >section of the import, have the preserve cell format checked off and I have
    >also tried importing the file into a template with all the cells formatted
    >as Text to preserve the format of the data coming and all of these methods
    >continue to drop the leading zeros on my fields. If you have any other

    idea's
    >or any examples that you know of that work please let me know. I am looking
    >into xsl stylesheets but haven't found anything yet.
    >
    >"MarkN" <enterprise.@127.0.0.1> wrote:
    >>
    >>So how are you opening xml in Excel? What version of Excel? You might

    >try
    >>using a template with the data (???).
    >>
    >>"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>>
    >>>I am creating the xml on the fly and then opening it in an excel wooksheet
    >>>in the browser from a dataset in .Net. The datatype for the field coming
    >>>from the database is varchar2 into the dataset but if it only contains

    >numbers
    >>>it drops the leading zeros in excel. Not sure how to format or tell the

    >>field
    >>>is text on the fly.
    >>>
    >>>"MarkN" <enterprise.@127.0.0.1> wrote:
    >>>>
    >>>>Tell the field is text. Or put a format on it.
    >>>>
    >>>>"denis pelletier" <pelletierdr@npt.nuwc.navy.mil> wrote:
    >>>>>
    >>>>>I am trying to figure out a way to import an xml file into excel and

    >keep
    >>>>>it from dropping leading zeros in one of the fields. The data should

    >>be,
    >>>>>let's say, 029456, but when I open the excel file after importing it

    >I
    >>>am
    >>>>>only seeing 29456. Is there a way to tell excel not to drop the leading
    >>>>>zeros?
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center