CSV numeric strings

[MyndPhlyp]

I'm having a little "fun" creating a CSV file from an ASP page for use with
Excel and hope someone here knows of a trick that doesn't involve user
intervention.

I am familiar with the format of CSV files - strings containing a comma must
be enclosed with double quotes, otherwise just separate all the columns with
a comma.

But ...

Excel tries to be smart about things. If a string, quoted or unquoted,
contains leading spaces and/or trailing spaces and/or leading zeros and is
otherwise numeric (number groupings, decimal points, positive/negative sign,
currency sign, and the characters 0-9) it converts the value to numeric.

For example, the CSV may contain:

000823

The resulting cell will be numeric containing:

823

I know the trick when manually entering a value in a cell that is numeric
but you want treated as a string. Preceed the value with a single quote.

For example, manually entering the cell value:

'000823

The resulting cell will be text containing:

000823

But ...

Doing so in a CSV causes Excel to use that preceeding single quote as part
of the (now definitely non-numeric) string.

Is there a way (or another easily generated file format) to get these
seemingly numeric values to be treated as a string by Excel without
resorting to user intervention (such as the process of importing a text file
where the user is prompted for column placements, delimiters and value
type)?

 

[Tony Steane]

MyndPhlyp,

Try using the ="000823" so a CSV record/line would look something like
this

Part No.,="000823",Cost,12.34,Replacement Part,="09546",Cost,12.45

etc,etc

HTH

Cheers

Tony

 

[MyndPhlyp]

MyndPhlyp,

Try using the ="000823" so a CSV record/line would look something like
this

Part No.,="000823",Cost,12.34,Replacement Part,="09546",Cost,12.45

Cool. Learn something new every day. Is the "=" prefix to the string an
Excel-ism or is it actually a global thing?

(And I thought I might have to resort to SYLK.)

 

[Tom Ogilvy]

the equal sign causes Excel to see entry as a formula

="000823"

so the cell contains a formula returning the string 000823.

 

[MyndPhlyp]

the equal sign causes Excel to see entry as a formula

="000823"

so the cell contains a formula returning the string 000823.

Ah - Excel-ism. I'll have to watch for the inevitable fallout and may be
doing SYLK in the not-too-distant future.

Thanx again.

 

[Tony Steane]

MyndPhlyp,

It's been a while since I used SYLK files, but the following code is
the bare
minium to do the same as the CSV example above.

ID;PWXL
C;Y1;X1;K"Part No."
C;X2;E"000823"
C;X3;K"Cost"
C;X4;K12.34
C;X5;K"Replacement Part"
C;X6;E"09546"
C;X7;K"Cost"
C;X8;K12.45
E

Like you said in your first post, you are "having a little Fun"
hope you have fun creating a SYLK file.

Cheers

Tony

 

[MyndPhlyp]

Like you said in your first post, you are "having a little Fun"
hope you have fun creating a SYLK file.

Thanx. I managed to find one text file propagated throughout the web on SYLK
file layouts. An ancient plagiarism from Wang's (?) original documentation,
undoubtedly; cryptic, to be sure; complete, hardly. But comparing it with
the output from Excel should get me through. If I'm going to plow through
the trouble of creating a SYLK file you just know I'm going to also include
font and cell format specifications. I'm just not yet sure I have to specify
the number of rows (Y1 in your example above). If I can get away without
specifying it, and all indications so far are that I can unless the file is
to be used for MultiPlan input, the task becomes a little bit easier.
Otherwise I'll have to build an array of the output first, count the rows
and then build the SYLK file. Given the number of tables I have to traverse
and the data being changed in real time I can't afford the luxury of using
the current web page row count.

Thanx yet again. [and that rhythmic thumping you hear is nothing to fear; it
is only my head beating the desk to a pulp]