Forcing Excel to Import .CSV data

  • Thread starter Thread starter Cortez
  • Start date Start date
C

Cortez

Does anyone know if it is possible to configure Excel to recognize
that when opening a .CSV file, the Import Wizard should be used?

We use a lot of .CSV files here, and many users have problems when
they double-click the file icon to open it. If there are columns with
numerical data, excel will convert them to numbers and then will often
change them by truncating leading zeros, or converting them to
exponential values if they are many digits.

The standard solution for this is to open Excel and then initiate the
import wizard, but it would be nice if Excel could be set to
automatically recognize, by file type, that the import wizard should
be used.

I am reasonably capable with Excel myself and doubt that this option
exists, but the expert knowledge within this forum gives me hope that
if this can be done, someone here will know how.

Thanks very much,
TK
 
Change the extension to .txt and open in Excel............the text
import wizard will appear.


Gord Dibben Microsoft Excel MVP
 
Change the extension to .txt and open in Excel............the text
import wizard will appear.


Gord Dibben Microsoft Excel MVP
Click to expand...

The problem here is that your CSV files are malformed. Quotes around
numerical data held as strings have to be escaped into the CSV file.
If numeric data with many digits and leading zeroes is intended to stay
as strings it must be represented in a CSV file as """000001234"""
I think the problem lies elsewhere. The files may have commas to delimit
the data fields but they are not correct CSV files.

Copy and paste these into a black worksheet and save as CSV
QWERTY 3.1
1234 "0001234"

You will find that saved as CSV it will come out as

QWERTY,3.1
1234,"""0001234"""

XL2007 gets this right when you use Save As .CSV so I would say the
problem lies here with whatever you are using to export malformed files
with a .CSV extension. Renaming them as .TXT files would get the wizard
but a better solution would be to fix the defective data source.

Regards,
Martin Brown
 
Back
Top