Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing data with wizard, interpretes as date (XP)

    We are downloading data from the mainframe and opening in Excel. The Text to column wizard is utilized, data is fixed length. The heading row contains data like

    42-3 45-2 3-1

    The first two work fine. However 3-1 is interpreted as a date.

    Can I avoid this? The rows below contain numeric values, so loading columns as text is not an option.

    Thanks.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Parsing data with wizard, interpretes as date (XP)

    If you are opening a text file in Excel, the Text Import Wizard is launched to guide you through the process. With this wizard You first choice is to select Delimited or Fixed width columns. Clicking Next then allows you to either select the Delimiter(s) or create the fixed width columns. Clicking Next then allows you to select how each column should be interpreted as it is imported. This also applies when using the Text to Columns wizard. Step 3 allows you select each column and specify how it should be formatted, General, Text, Date etc.

    See attached graphic.

    Andrew C

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parsing data with wizard, interpretes as date (XP)

    In addition to Andrew's reply: you'll need to run the Text to Columns wizard twice:
    - For the header row: set all columns to text using the method described by Andrew
    - For the data rows: either let Excel decide the type, or set it to generel, text or date as needed.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing data with wizard, interpretes as date (XP)

    Thanks.

    Telling Excel that the whole column is text is the only solution I could find too. A two step parsing exersize will not work either, since this file is in sections, with a header row, approx 10 data rows, another header row, approx 10 data rows, etc.

    One other solution I thought of is, import all as text then copy the value of 1, do a "paste-special" - "Multiply". However, the results are the same, 3-1 converts to a date while 13-1 does not.

    This means I will have to pound code, which I do not have time to do at the moment.

    Oh well, code it is!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Parsing data with wizard, interpretes as date (XP)

    You could also import the entire thing as one "big" text column. Then delete the "header row" sections and then use text to columns.

    You could possibly even have a macro do it by reading in the text file line by line and parsing in VB. If the the files have some "pattern" it could be coded relatively easily and then have this "spit out" the parsed data.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •