CSV import, excel corrupts data

[Rene]

Hello group,

I'm one of the many struggling with excel's csv-import functionality. After
reading through this group and trying out various things I'm not much wiser
so I try to ask for a solution.

I have an application that creates a cvs file for the user with some data.
One of the columns can contain data that looks like this: "12/76" -
unfortunately excel violates the cvs-spec and does interpret this as a
(incomplete) date. It then displays "01.12.1976" for example, which is just
plain wrong and must not happen.

I tried the trick with a macro that marks all cells and sets the cell-type
to "text". Now Excel utterly corrupts the data and displays some numeric
value, probably derived from the date. The transmitted data was "12/76" and
excel now has "28095", whatever that should mean. In any case, data got
corrupted and is unusable.

Excel imports the data correct if and only if I open excel with an empty
worksheet, use the data import wizard to select the csv file, select csv
mode, select the separator char, select the afflicted column and switching
its type from standard to text and finally confirm the import at cell A1.
This works and works correctly, but is far too many steps for the users
which are not computer professionals. Also this needs to be repeated for
all csv files of that type.

Additionally, the data is server generated within a web-application and
upon generation the user can open or save the csv file directly. Opening it
directly is the one convenient way but apparently does not work without
causing data corruption through excel.

I also read about some other tricks like putting a '-sign in front of the
column-data which also works, but alters the data which again must not
happen since this data has some specific meaning and there are other
programs working on the same csv files. Also copy&pasting the line
containing this workaround also copies this char with it.

I further tried in vain to redefine the "standard" cell formatting to be
the same as "text" but this doesn't work either.

So my question to the group is: Is there any way to prevent Excel to
assume/convert data from a csv file, while still allowing the csv file to
be opened by double-clicking it which opens/starts Excel or by sending a
server-generated csv file from the webserver ?

thanks in advance

René

 

[Dave Peterson]

If you rename your .csv file to .txt, you can have complete control over each
field--you can choose Text for the fields that excel thinks are dates (but
really aren't).

If your data is always the same layout, you could record a macro when you do it
once--defining each field the way you want (with the file named *.txt--that's
very important).

Then you could share that macro with your users.

If you add headers/print layout/filtering/subtotals/pivottables, then that macro
maybe something that makes the user's life much easier to analyze that data.

 

[Rene]

If you rename your .csv file to .txt, you can have complete control over
each field--you can choose Text for the fields that excel thinks are
dates (but really aren't).

Changing the file extension doesn't do me any good. The file can be opened
but Excel takes all data into a single column. No matter if the separator
char is a ";" or a ",".

If your data is always the same layout, you could record a macro when you
do it once--defining each field the way you want (with the file named
*.txt--that's very important).

Doing the macro trick corrupts the data as described when the file is a
".csv". Using .txt doesn't read it in usable in the first place.

This is with Excel 2003. What am I doing wrong?

 

[Dave Peterson]

Save the file to a local drive. Rename it to .txt. Then use File|open to
import it.

You'll see the text to columns wizard open and you can specify whatever you
want.

 

[Rene]

Save the file to a local drive. Rename it to .txt. Then use File|open
to import it.

You'll see the text to columns wizard open and you can specify whatever
you want.

Thanks for the hint. It _does_ bring up the data import wizard. But I can
do that already without renaming the file by just opening excel empty and
select import data. What I am seeking is a way to open a CSV with a
double-click or to open it directly from the web-browser which gets its CSV
file server-generated from a web application.

I can easily import the data correctly, it's about ease of use for my
users.

There really seems to be no way to accomplish this in excel. CSV declares
all fields as "opaque", applications must not do any conversion, but excel
does. Additionally, correcting the problem _after_ loading causes excel to
corrupt the data, as I have described.

If you want to test it, here's a small test CSV that serves fine to
demonstrate this behaviour:

***** SNIP *****
TEXT1;DATA1
contains a string that is not a date but excel automatically makes a date
out of it;12/76 contains another string that is not converted;324FG12
contains another string that is converted, but not to a date;.232
select column B and reformat it as TEXT to see what happens now;00023
create an empty worksheet and use the data import wizard. Select Text for
second column on import;see the difference ***** SNIP *****

So the only way to get the data into excel correctly seems to be an
extremely cumbersome series of manual interaction compared to the normal
way. Can I remove all these manual steps by using some VBA magic/macros?
(Again, if I set the columns to text AFTER they have been loaded and
converted, the data is corrupted and unusable, they must be loaded into
excel correctly, everything coming later seems not to be able to correct
the problem)

Thanks

René

 

[Dave Peterson]

I guess if you want to use excel, you'll have to play by its rules.

If opening .csv files directly doesn't work for you, then you'll have to find
another way/application.

 

[moonwalker]

i'm also having some problems using the text import wizard

if there is carriage return in the columns of the csv file..
the carriage return would take up a whole row after i import using the
text import wizard..

it doesnt have this problem if i just open up the csv file..

anyone have any idea how to solve the problem and yet still using the
text import wizard?

thanks

 

[Dave Peterson]

I think I'd try to fix the raw data--maybe you can change that embedded carriage
control character to something else--then import the data.