Saving CSV files that have spaces in some fields

[Bob Richardson]

I want to I save a CSV file created in Excel 2003. If a field has spaces in
it, I want it surrounded with quotes.

For example, I want the output file to look like:

ID,FirstName,LastName
10,Bob,Jones
11,"Mary A",Smith
etc.

TIA

 

[Harlan Grove]

I want to I save a CSV file created in Excel 2003. If a field
has spaces in it, I want it surrounded with quotes.

For example, I want the output file to look like:

ID,FirstName,LastName
10,Bob,Jones
11,"Mary A",Smith
etc.

Did you try doing this first and get different results? If each of your
fields were in separate but adjacent columns, this is exactly how Excel (at
least previous versions) would save in CSV format.

 

[David McRitchie]

Actually Excel generally only uses the double quotes if there is
a delimiter within a cell (in the US that is a comma),
or if there is a double quote within the cell.

Excel will *not* place double quotes around the item with the
spaces simply because it contains a space. And PC applications would
have no problem with this. I expect that someone wrote their own
application to read these files without really looking at what would
be generated for them. If a cell is empty you will only see the
commas on each side. If the cell only contains a space you would again
only see commas on each side.

So if you absolutely need something special, suggest you look at
Chip Pearson's page on exporting text files.
http://www.cpearson.com/excel/imptext.htm#Export

Just found out the difference between
saving as DOS csv and MAC csv
is what is used at the end of a line

The DOS version of .csv uses CRLF and
the MAC version of .csv uses only CR

So if taking the data to a mainframe, I would expect you would
certainly want the DOS version of .CSV.

Some suggestions when opening the file in Excel, first
change it's file extension to .txt so that Excel will invoke the
import wizard and not just what it thinks you wanted from a previous time, then
..
1) open with delimiter
2) comma (or your regional delimiter) as the delimiter
3) make sure you do not have space as an additional delimiter,
4) make sure you do not have the box checked that says -- treat
consecutive delimiters as one



--

 

[Harlan Grove]

Actually Excel generally only uses the double quotes if there is
a delimiter within a cell (in the US that is a comma),
or if there is a double quote within the cell.

....

You're right, I'm wrong.

The DOS version of .csv uses CRLF and
the MAC version of .csv uses only CR

So if taking the data to a mainframe, I would expect you would
certainly want the DOS version of .CSV.

Not necessarily. IBM mainframes at least use EBCDIC rather than ASCII, so
newlines would need to be translated into EBCDIC equivalents, and I suspect
that EBCDIC/ASCII translators are very accomodating, accepting Mac (CR), DOS
(CR/LF) and Unix (LF) newlines in ASCII files. Note that the EBCDIC/ASCII
translators would be built into the Mainframe terminal emulation software.

 

[David McRitchie]

And I thought those codes were the same. You're right they are not
http://www.macdonald.egate.net/CompSci/Pascal/hdatatypes.html#asciicontrol

but I don't recall having any problem between mainframe and PC when
everything was CRLF and I don't remember where I first encountered just CR or just
LF but it turned out to be okay (an not talking about Excel, which I didn't have at the time).
For pure text source on one machine. But just have part of CRLF was a problem
somewhere or I would not have thought twice about it.

 

[Harlan Grove]

. . . But just have part of CRLF was a problem
somewhere or I would not have thought twice about it.

....

On non-Mac machines LF alone is usually no big deal since so much software
is written in C, and the first thing C text mode file input does is replace
all CR/LF with LF. It's almost certainly Mac CR alone that causes problems
for PC apps.

 

[Dave Peterson]

If I transfer a Unix style file to a PC (with just lf's). And then upload it to
the mainframe, I don't get new lines in the mainframe dataset.

I have to add the CRs in an interim step on the PC.

And for transfers from the mainframe to the pc, I get crlf in my ASCII
transfers. I don't recall what happens with mainframe to Unix.

 

[Harlan Grove]

If I transfer a Unix style file to a PC (with just lf's). And then
upload it to the mainframe, I don't get new lines in the mainframe
dataset.

I have to add the CRs in an interim step on the PC.

....

Yeah, that's how IBM's own IND$FILE works. The last time I transferred
anything to a mainframe was to a VM/CMS host using Kermit, and it had no
problems with LF only. Now my own Kermit-VM/CMS experience is very likely
irrelevant to IND$FILE-MVS/TSO, so CR/LF is very likely safest as it's also
the HTML standard.