Excel importing of csv and xml

Bernie Yaeger · Jul 28, 2004

OK, I see that Excel in various versions (2000, xp) is stupid when it
imports .csv and .xml in certain ways. Specifically, a string of '01503'
comes in as numeric data of 1503 in both formats, notwithstanding that in
both files they appear as '01503'. However, if the csv file has a .txt
extension, it will first require that you specify certain import
instructions, such as delimiter, data type, etc, and then it will import it
correctly!

Here's my question: how can I automate this process, so that it can be
imported correctly without having to 'trick' Excel (and, by the way, an xml
version does not give me any 'trick the system' opportunity).

Thanks for any help,

Bernie Yaeger

JE McGimpsey · Jul 28, 2004

Well, I supposed some might consider those "smart" features - i.e., not
turning numbers into Text, just because they have a leading zero, but...

You can certainly automate the process - start by recording a macro
(Tools/Macro/Record new macro...).

As for XML, XL imports according to the MS Spreadsheet schema. If you
want to import numbers in a different format, the generating application
should generate the file IAW that schema.

Alternatively, you could easily use another app to perform a transform.
I use Applescripts to do this with my Mac clients.

hgrove · Jul 29, 2004

JE McGimpsey wrote...

Well, I supposed some might consider those "smart" features -
i.e., not turning numbers into Text, just because they have a
leading zero, but...

...

It might be considered 'smart' *IF* there were any way of *PREVENTING
this from happening before the fact when opening CSV files. Fo
example, if the following single line were in foo.csv,

foo,"foo",012,"012"

Excel imports the first two fields as text (OK), and the next tw
fields as numbers (*not* OK for the last one).

There's no way to import sequences of numerals AS TEXT in CSV files
That's **STUPID**! There's no option to allow users to choose whethe
to run the text file import wizard when opening CSV files. That'
**STUPID**! And CSV files aren't a separate file type in the Ope
dialog, so it's not possible to open CSV files so they trigger the tex
file import wizard when opening other than by changing their fil
extensions. That's **STUPID**!

That Excel lacks any way to turn off these 'helpful' features is eithe
an indicator that Microsoft doesn't believe any users are smart enoug
to decide for themselves when not to use these 'features' or (my pe
theory) they *want* to inflict pain

JE McGimpsey · Jul 30, 2004

hgrove said:
It might be considered 'smart' *IF* there were any way of *PREVENTING*
this from happening before the fact when opening CSV files. For
example, if the following single line were in foo.csv,

foo,"foo",012,"012"

Excel imports the first two fields as text (OK), and the next two
fields as numbers (*not* OK for the last one).

It may be "clear" that the last field is to be input as Text, but purely
typographically, this is less so:

foo,"foo",1012,"1,012"

There's no way to import sequences of numerals AS TEXT in CSV files.
That's **STUPID**! There's no option to allow users to choose whether
to run the text file import wizard when opening CSV files. That's
**STUPID**! And CSV files aren't a separate file type in the Open
dialog, so it's not possible to open CSV files so they trigger the text
file import wizard when opening other than by changing their file
extensions. That's **STUPID**!

I agree. In MacXL, you can open .csv files using the Data/Get External
Data/Import Text File command, which opens the Import manager, and
correctly interprets the fields above. I have no idea why WinXL doesn't
have that capability, too.

That Excel lacks any way to turn off these 'helpful' features is either
an indicator that Microsoft doesn't believe any users are smart enough
to decide for themselves when not to use these 'features' or (my pet
theory) they *want* to inflict pain.

I continue to follow Napoleon's dictum "never ascribe to malice what can
adequately be explained by incompetence."