Loading a CSV file - stopping automatic date conversion

[philipk]

I want to load a CSV file.

In this file I have entries that Excel thinks are dates due to their formatting.

So I load 2-3-98 and it becomes a date formatted cell displaying: 2/3/98

I tried double quoting these as "2-3-98" but Excel still converts these automatically (and somewhat annoyingly).

I'd like to tell Excel not to do this somehow in the CSV format because I want to be able to open these files from Win Explorer and not have to trouble with VB macros etc.

If there is a solution to this then please please let me know.

Regards,
PK

 

[Mark W.]

Change the "Short date format" in the Regional Options control panel to M-d-yy.

 

[philipk]

More explanation ...

I'm trying to load a CSV file with columns such as A-B-C where A,B and C are 1 to 3 digit numbers.

When Excel sees any combination that fits the MM-DD-YY format it automatically sets this to a date.

I'm attempting to send this file via e-mail to several people and I do want these people to have to modify their PCs or the Excel options in any manner. I would like the CSV file to somehow tell Excel that these columns are not dates - is this possible?

Can I place any formatting rules etc. in the "header" of the CSV file to tell Excel NOT to convert columns that are very obviously (to me) NOT dates.

Many thanks in advance.

Regards,
PK

 

[philipk]

Surely Bill and the gang up there in Seattle didn't design this so that everything that looks like a date is loaded as a date.

This would be nearly as ludicrous as assuming every number with two digits after the decimal point was common currency and putting a $ symbol in front of it (or other currency symbol of course).

Is it more difficult than I realize?

Regards,
PK

 

[Mark W.]

Why aren't you sending an .xls document?

 

[mdipalma]

I think that he is sending it as an .xls, but he is getting CSV data to start with, pulling it into Excel, presumably doing some manipulation on it, then saving it as an .xls and distributing it.

If I understand correctly, Philip's problem is that when he pulls in the CSV data, it includes data in the format "##-##-##" that is NOT a date. Of course, Excel, with the best of intentions, I'm sure, interprets this cell as a date and converts it to date format. Probably more importantly, in the process it also converts the actual value of the cell. So, even if he then goes through and reformats the cells back to text or something, they've lost their value. He is trying to get Excel to simply leave the data alone, so that the data will stay formatted as it was in the CSV file, he can save it as an .xls and send it to several other folks who can view the data in that format.

FWIW, I've run into this myself with some data that I pull off of web pages. The best I could do was apply a workaround. In my case, my data could be "4-8" (which Excel converts to a date) or "31-22" (which it leaves alone). Since I am trying to extract the "4" & "8" (or "31" & "22") and place them in seperate cells, I was able to solve my problem by testing the cell, and either using MONTH() & DATE() for the cells that are formatted as dates, or LEFT() & RIGHT() (with a LEN() and FIND() looking for the "-") for the ones that were not dates. I still have an occasional issue with things like "12-36" which Excel converts to a date and sees as "December, 1936" which I could probably resolve by placing additional tests against that cell, but it doesn't happen that often. Plus, I think that my situation is different than Philip's in 2 ways; 1) I am the only one who sees the sheet where I run into this problem, so when I run into something that is an exception, I can figure it out as I go. 2) I am trying to extract elements from the automatically reformatted cell, and can do that via my workaround, but the cell itself still looks like a date, and it seems that that is the issue that Philip needs to resolve.

Anyway, I got for more verbose than I intended on this post. I would welcome insight from anyone who knows how to get Excel to stop doing the auto reformatting in the first place. I doubt that Philip and I are the only ones to haverun into this...

-Mike D

 

[philipk]

Mark, Mike,

thank you for you comments.

Apologies if I did not expand more on the explanation.

What happens ...

Database spits out CSV file onto Unix server and this is e-mailed to various recipients who then view/process the data.

I wanted the recipients to be able to double click the Excel CSV icon in their mailer and up would pop the well formatted fields.

In any case I managed this quite successfully by placing a single quote (') in front of the fields that I had trouble with but now the fields have a quote in front of them that I do not need (not bad relative to the data format).

It's likely we will place an intermediate application between the database and Excel such as Crystal Decisions (or similar) that can provide automatic scheduling and e-mailing. Perhaps I could do that with a VB script also ...?

Thanks for your help - on with the fun!

Regards,
PK