Excel data import problem 1

[henio]

Hi,

I'm having problems importing a delimited file (c10k rows) into Excel 2003.

Below is a simple example using the pipe as a delimiter (you can guess this data comes from a mainframe report).

003.1270|aaa|bbb |12.00
AA|AAA|BBB|17

I formatted all cells as text prior to the import.
The issue is that Excel chooses to format the first field in the first line as a numerical field (3.172) whereas if I type in the exact same characters, Excel accepts it as an alpha.

I'm taking data from two different sources (downloaded mainframe report as per example), and a Business Objects report, and doing vlookups between them to produce a report with data from both sources. The BO report loads into Excel correctly (all leading and trailing 0s are present). Excel data import from a flat ascii file seems to lose them.

Can anyone suggest a workaround?

h

 

[SkipVought]

Hi,

If you are TRUELY using the IMPORT wizard, Data > Import External Data > IMPORT..., then you can specify the [PIPE] delimiter and also specify each column's Column data format, one of which is TEXT.

Having selected TEXT for ALL columns, your data will be as you have defined.

BTW, pre-setting the column range formats before your import has no effect on the parsing results!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[henio]

Skip,

I was truly importing data - just never noticed that option.

You're a star.

h

 

[SkipVought]

BTW, If you import the same file, or other files with the same Parsing Spec, all you need do, once you have added your IMPORT QueryTable to the sheet, is to select in the table and Data > Refresh If you do not have the prompt turned off, it will ask you to verify the file/path and the data will be imported in accordance with the previously defined Parsing Spec.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]