Excel CSV Files; Creating Of

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hello,

I have created a small Excel file that I wish to be saved as a CSV.

Question: do I have to put " marks around the beginning, and end, of
the text in each cell, or does Excel automatically do what is required
for CSV when I tell it to Save As a CSV ?

I did not put any " marks around any text, and when I opened it up in
Notepad++, there were a single set of double quotes (e.g., "Bob") marks
around the text.

Is the initial Excel file I created without any " text marks
a "true" CSV ? Or,...?

Working with, and creating, a CSV seems a bit confusing, at least to me.
I did read many of the CSV articles via a Google, but still not too clear.

Any clarifications on what is exactly required when creating in Excel
would be most appreciated.

Thanks,
Bob
 
Hi Bob,

Am Tue, 19 Nov 2013 14:27:40 -0500 schrieb Bob:
I have created a small Excel file that I wish to be saved as a CSV.

Question: do I have to put " marks around the beginning, and end, of
the text in each cell, or does Excel automatically do what is required
for CSV when I tell it to Save As a CSV ?
Click to expand...

there is no need to put quote marks around the text. If your separator
is into the text of a cell Excel put quote marks around the text of that
cell automatically.


Regards
Claus B.
 
Adding to Claus' reply...

CSV stands for Comma Separated Values. That precludes, then, that you
values should not contain commas. But if they do happen to contain
commas then Excel will wrap those values in quotes to indicate the
contained comma is not a delimiter.

IMO, it's best to replace contained commas with another character when
creating CSVs using SaveAs. Preferably, I'd dump the data into an array
and write that to a text file with a .csv extension (after replacing
contained commas, OR using a different delimiter so the contained comma
issue is mute)! This avoids having to deal with (or code for) Excel's
built-in export/import issues because it allows using standard VB[A]
I/O functions.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Bob said:
I have created a small Excel file that I wish to be saved as a CSV.
Question: do I have to put " marks around the beginning, and end,
of the text in each cell, or does Excel automatically do what is
required for CSV when I tell it to Save As a CSV ?
Click to expand...

Excel puts double-quotes around text where Excel deems it is necessary.
Generally, that is text that contains the CSV separator (usually comma) and
text that contains double-quotes. However, there might be other situations.


Bob said:
I did not put any " marks around any text, and when I opened it
up in Notepad++, there were a single set of double quotes
(e.g., "Bob") marks around the text.
Click to expand...

That is odd. Excel does not double-quote simple text like Bob when I try
it, even if the cell is formatted as Text.


Bob said:
Is the initial Excel file I created without any " text marks
a "true" CSV ? Or,...?
Click to expand...

Yes, as long as columnar data is separated using commas (or the List
Separator configured in the Regional and Language Options control panel).


Bob said:
Working with, and creating, a CSV seems a bit confusing, at least to me.
I did read many of the CSV articles via a Google, but still not too clear.
Click to expand...

Understandably so.

Creating (saving to) CSV files should not be confusing. But getting Excel
to interpret (read) CSV files as you intended might be tricky.

In particular, even if you surround text with double-quotes, Excel still
might not interpret the data as text. For example, "000123" is interpreted
as a number and the cell is formatted as General. So the leading zeros will
not appear in Excel by default, despite the double-quotes in the CSV file.

Sometimes, we must import the CSV file instead of opening it directly using
Excel. By "import", I mean: use the Get External Data / From Text
operation. That allows us to specify a column of data to be formatted as
Text (or Date), not General.

However, even that might not be sufficient if the data to be treated as text
is not columnar, and Excel insists on interpreting it as numeric.

Other applications might interpret (read) the CSV file differently, always
treating the double-quoted data as text.

There are no standards for reading a CSV file. Only for writing it (RFC
4180).
 
Back
Top