Incorrect formatting opening .csv file in Excel

[Ulen]

When opening a database generated .csv file with the following data:

"1","10E-024","1"

Excel opens it incorrectly formatting the text as a scientific number:

1 1.0E-24 1

If I save the file as .txt, and open in excel using the import wizard,
I can specify that the column should be text, and the item code
10E-024 appears correctly. However this is far from ideal, as it
relies on a manual step
and the file cannot be saved as .csv again - otherwise the same fault
happens next time the file it opened.

Is there a way of forcing Excel to open .csv files in raw data format,
without treating them as excel files and formatting the columns
incorrectly?

Thanks,
Ulen.

 

[Doug Kanter]

Record a macro as you go through the manual process, and then fine-tune the
code afterward if necessary.

 

[Earl Kiosterud]

Ulen,

Change the extension from csv to txt. File - Open (change the file type to
txt so you'll see it). This will kick off the text import wizard. Specify
delimited, comma, and in one of the steps, you can specify the formatting
for each field. Specify text for this one.

If you'll be reading this file regularly, and have Excel 2002 (and maybe
2000 -- don't know), you can do this instead:

Leave the extension csv (doesn't matter). Data - Import External Data -
Import Data. This will kick off the same text import wizard. But now when
you want to reread the text file, just right-click the existing imported
table and choose Refresh. It remembers.

 

[Ulen]

Ulen,

Change the extension from csv to txt. File - Open (change the file type to
txt so you'll see it). This will kick off the text import wizard. Specify
delimited, comma, and in one of the steps, you can specify the formatting
for each field. Specify text for this one.

If you'll be reading this file regularly, and have Excel 2002 (and maybe
2000 -- don't know), you can do this instead:

Leave the extension csv (doesn't matter). Data - Import External Data -
Import Data. This will kick off the same text import wizard. But now when
you want to reread the text file, just right-click the existing imported
table and choose Refresh. It remembers.

Thanks - that doesn't really help though. It used to be simple to
generate .csv text files, passing them from one person to another, and
then using the final .csv to import data back to the database. It is
not possible to force users to remember to use the text import wizard
at every stage, and always check and resave the data correctly - so
this problem will occur any time one of these item codes is used.
Everyone is so used to simply double-clicking the .csv file to open it
directly in Excel.

I think the problem really is using Excel as a text editor, when it
always defaults to being a spreadsheet tool, and will always enforce
its formatting.

We will just have to encourage users to use a text editor application
instead of Excel....

..... unless someone knows of a simple global setting to switch off
Excel's auto-formatting?

 

[Doug Kanter]

"Earl Kiosterud" <[email protected]> wrote in message

Thanks - that doesn't really help though. It used to be simple to
generate .csv text files, passing them from one person to another, and
then using the final .csv to import data back to the database. It is
not possible to force users to remember to use the text import wizard
at every stage, and always check and resave the data correctly - so
this problem will occur any time one of these item codes is used.
Everyone is so used to simply double-clicking the .csv file to open it
directly in Excel.

I think the problem really is using Excel as a text editor, when it
always defaults to being a spreadsheet tool, and will always enforce
its formatting.

We will just have to encourage users to use a text editor application
instead of Excel....

.... unless someone knows of a simple global setting to switch off
Excel's auto-formatting?

I have similar issues on a regular basis, and the worst part is that Excel's
formatting decisions often seem to be made based on factors that we cannot
determine. But, here's something that works with one of my imported files.
It may be worth trying with yours.

Open one of the csv files. Now, open a new, blank sheet. Select a few cells
in any column, and format as text. As you may know, this means Excel should
accept the content of those cells "as is", without second-guessing the user.
Now, try using copy, paste special, value, to copy some of the "problem
values" from your csv file. For me this works with some data, but not all,
and it's a mystery as to why that is.

Example: I have a text file containing UPC codes like this: 1600010710. But,
some begin with zeros, like this: 0005444515. All my files surround each
field with quotes and separate them with commas, like yours. But, with some
imports, Excel turns the 2nd UPC into: 5444515. With others, it does not.
This has been going on for years. I've spent untold hours staring at text
files trying to understand the issue, to no avail. This is why I do 99% of
my database work in Paradox.