Here is a question that nobody knew the answer...

[Galsaba]

I have a .CSV file that is automatically generated by a program.
Apparently, when the file is opened with Excel, the numeric fields that are
enclosed in double quotes in the file and look like "123,456.1234" (in the cvs
file) are being formatted by Excel to two-decimal-number and displayed with
only 2 decimals. The cause of the problem seems to be the comma in the number,
which I don't really want to remove. This comma in the number tells the excel
that the numbe is a "number" format, so the excel formats the field to
"number".

Is there any way to suppress automatic cell formatting in Excel, so the
contents could be displayed just the way it appears in the source file?

I do no want to do anything in the excel. The csv file (using excel) will be
opened by customers and I dont want them to use the tools to change format.
What I want is, to set the csv file in such a way that when the excel file is
opon, the columns will not be formatted to the "number" format, but to
"general".

May be there is a code in that I can add to the csv file tellling excel to
disregard formatting?

Thanks

(e-mail address removed)

 

[BrianB]

If you change the file suffix to .txt you get more options when doin
the import into Excel

 

[Galsaba]

Your statement is correct, but it is not an answer to my question.
My question was how to make it that when the user open the excel file he will
not need to play with formatting the cells. Opening a txt file indeed gives you
more options, but then the user will have to "play" with all type of parameters
to set it up before he/she can read the file. While opening a csv file, the
file will open immidiately. And this was one of th erequirements.
Thanks.

 

[Frank Kabel]

Hi
the problem with this format is that *.CSV files do not store any
formating info. So you don't have any control about the presentation in
Excel on a different PC (depends on his regional settings, Excel
options, etc.)
So as you have stated you don't want the user to interfere (it should
happen automatically without any user interaction) and this file is
opend on different PCs I think there's no solution for you if you have
to use a *.csv file

And in addition as this is a *.csv file you can't add any code to it.
So either you have to change the file format or you have to live with
the drawbacks of this file format.


P.S.: please don't multipost this question as nearly all regulars read
all groups and multiposting may scatter your answers. also it creates
additional effort for the people trying to answer your question

 

[JE McGimpsey]

Since a .csv file *by definition* doesn't contain formatting
information, you can't change the default behavior of XL when opening
it. However, you can write/record a macro that formats the cells
appropriately after import, or you could write/record a macro to import
the file, formatting on the fly. Neither would require the user to play
with any settings, but they would require the user to load a workbook or
add-in (preferably add-in) with the macro.

Would either of these be of interest?