Comma Separated Value Files

[dr_damo]

Hope this makes sense.

I have a worksheet of 80-90 records, I need to import it via a web portal which accepts .csv files.

However, if I save this sheet in Excel as a .csv file, it appears not to be saving with double quotes and commas.

What am I doing wrong and what, if anything, can I do to create a properly marked up .csv file? Without using notepad?

Excel 2003 btw.

 

[Joe4]

If you save an Excel file as a CSV file, it should properly place the commas between each field. It will only place quotes around fields that have commas as part of their data (not every single field). This satisfies the requirements to be a legitimate CSV file.

Note, to see what you resulting CSV file actually looks like, do NOT open it in Excel (which is often the default). Use a Text Editor like NotePad if you want to see what the resulting CSV REALLY looks like.

 

[dr_damo]

I have a template file, which I can see in Notepad has quotes around the fields and the data. And they're separated by commas.

If I open this file and populate it with more data in Excel, then save as .csv, then open in Notepad: The commas and quotes have gone.

Do I have to replace the commas and quotes manually? Please say that's not the case?

 

[Joe4]

If I open this file and populate it with more data in Excel, then save as .csv, then open in Notepad: The commas and quotes have gone.

The quotes may disappear from the fields that do not have literal commas in the data, but that shouldn't really matter - they are not necessary in that case.

However, the commas should NOT disappear if you are doing it right. Can you explain out the exact steps you are taking in re-saving your file, and then viewing it?

 

[dr_damo]

Sure:

1. Open X.csv file in excel
2. populate file with data
3. save as Y.CSV file (comma delimited) in excel
* book contains features not compatible warning*
select yes
4. open file in notepad
no commas etc

Original X.csv in notepad = has commas etc
Y.csv = does not.

 

[Joe4]

I use this method many times, and have never experienced any issues like this.
Are you sure that the file you are saving has data in more than one column?

Can you post a small sample of the before CSV file and the after CSV file?

 

[dr_damo]

There are 98 columns. Therefore, each row is an individual record.

When opened in Notepad
BEFORE
"1.01","1.02","1.03"
"YYY. Example ","012345","0766548431"

AFTER
1.01,1.02,1.03
YYY. Example ,12345,0766548431

 

[Joe4]

You kept saying that the "after" file does not have commas in them, but the samples you posted of your "after" file look like they actually do have commas. So I don't see any problem there.

As I was explaining, quotes are usually not necessary unless your data actually has a comma (that is not a delimiter) in it. All the quotes do is tell the program importing that file that if there are any commas between quotes, treat them as data as not as a delimiter. If there are no physical, literal commas in your data, quotes are usually not necessary.

So I would say that your "after" file looks like a good CSV file that most programs will accept. Try importing the file into your web portal and see if it really is a problem or not.

 

[dr_damo]

Ah, yes, sorry. I meant the quotes have been stripped out. Semi colons are used to separate those fields that have multiple options (i.e. 1+2 is coded 1;2).

This is the guidance in the notes that come with uploading:
"CSV files will open in Excel, but saving them as a csv file in Excel causes all of the double quotes to be removed. You will not then be able to import the file. Instead, use Notepad to edit and save any changes. Notepad does not strip out double quotes."

I've had a look into this a bit more. We have two template files, long and short.
Long has text, 4 cells have commas in the text. i.e. the cell could be populated by
"1.Yes, this happens" or "0. No, this doesn't"

Short is just Numbers. i.e. "1" or "0"
This to me means that the short file will not be affected by the removal of the quotes. Right?


This is my thinking:
use the short template file. this has no commas.
populate.
save as csv
upload.
works/not works

 

[Joe4]

I see. It sounds like you are working with one of those rarer "restrictive" programs that requires double quotes to be there for all fields, regardless of content.

You can export the records using VBA instead of "SaveAs" and explicitly tell it to use double-quotes. Here a few links to other threads on the board that deal with that:
http://www.mrexcel.com/forum/showthread.php?t=247134
http://www.mrexcel.com/forum/showthread.php?t=509087

Otherwise, you can "build" the record you want to export yourself in Excel.
For example, let's say that your values are in columns A, B, and C and start on row 1. On Sheet2, enter this formula in cell A1:
=CHAR(34) & Sheet1!A1 & CHAR(34) & "," & CHAR(34) & Sheet1!B1 & CHAR(34) & "," & CHAR(34) & Sheet1!C1 & CHAR(34)
and copy down for all rows.
Then export this data as text file.

Or, as the program suggests, use a Text Editor to add/edit your data.