Re: CSV import, excel corrupts data

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.

Rene wrote:

Dave Peterson <petersod@xxxxxxxxxxxxxxxx> wrote:
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 work*** 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é


Rene wrote:

Dave Peterson <petersod@xxxxxxxxxxxxxxxx> wrote:
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?


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 wrote:

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 work***, 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é

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

--

Dave Peterson
.