Excel automatically converting Text field to Date - not wanted-

[gtaborda]

Hi

We receive daily .CSV reports from our mainframe.
These reports include text fields like this "03/1234".
These are PO numbers.

The files (which arrive via email with a CSV attachment) are opened automatically by Excel when users double click on the attachments.

These fields are automatically converted by Excel to dates, which are not, and end up look like 03-Feb for example.

Changing the cell format to Text gives back a number (instead of the original PO number) so that that doesn't work.

The original data (created by mainframe) cannot be changed, or cannot add apostrophy in front of text, so that's not an option

Any other ideas ?

thanks in advance for your help

 

[xlbo]

redefine .csv extension as .txt and use the text file import wizard - setting the column type to text for the problematic column

.csv files are imported without any kind of wizard as they are meant to contain VALUES (csV) only. If they contain text, excel assumes it is meant to be a value and converts appropriately

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gtaborda]

hi, thanks for input

We cannot save as .txt and then use import wizard...the idea is that users don't have to do anything, apart from opening the file by double clicking on it.

I am looking for perhaps an Excel macro or similar I can add to convert it back to its original text value or perhaps some Setting/option in Excel that will stop from converting it automatically...

thanks

 

[GlennUK]

We receive daily .CSV reports from our mainframe.

... and you are saying that the mainframe process cannot name these files as xxx.TXT? That would be a really simple solution, and not hard to implement from what I know.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[combo]

Excel has built-in csv interpreter, so there is no much freedom in case of file with this extension. So you can either try to generate excel files instead and send them to end users, or, as Geoff proposed, change file extension and use text import wizard. In the latter case you can do it manually or by code (excel macro), but in any case excel will open it with double-click of the mail attachment.

combo

 

[xlbo]

Spot on Glenn. To be honest, that is by far and away the easiest and best solution to implement

There is no setting you can change that will stop a csv file opening in excel as it does. It is a comma seperated VALUES file. If you choose to put text in there then you do so at your own risk that excel will see it as a date and convert it





Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"...convert it back to its original text value ..."

That cannot be done either. FYI, faq68-5827.

Excel, for instance can take any of these formats and CONVERT to the SAME DateValue (39492)...
[tt]
2/14
2/14/08
02/14/2008
2008/2/14
2008/02/14
feb 14, 2008
february 14, 08
[/tt]
So which string do you convert the DateValue back to???

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}