Blank cells but commas in Notepad

Originally Posted by john_mc

Hi all,

I am saving an excel worksheet as a csv file. There is data in columns A to F and then nothing until column T which has some data.

I delete cells from G1 to T26 and then save the file as a CSV. When i open the CSV file using notepad, i get a long list of commas after the last entry e.g.

column A data, column B data,column F data,,,,,,,,,,,,,,

There are 13 commas, which is basically one for every column until T. But i've already deleted all the info in those columns.

Does anybody know how i can get rid of the extra commas?

Cheers,

John Mc

Commas are field separators in a .csv file, as it's name implies Comma Separated Values.

You could use a tab delimited format, however that would have a tab for each field separator and may be no better for your purpose.

The contents of the field are irrelavant, the field separatos are maintained to correctly position any following fields within the list.

hth
---

Originally Posted by john_mc

Hi Bryan,

thanks for taking the time to reply and try to help me out. I understand what you mean that the csv files are by definition comma seperated, but the file is still not appearing as expected.

For example, I've got 26 rows of data, and only the first 16 have all the extra commas.

If it was meant to have a comma for every column, then the whole file should have 250 commas after them (for the 256 max columns, less the 6 that actually have data on them), not just the 13 commas after the last field.

I've attached a file that will (hopefully) help me explain a bit more clearly. As i couldn't upload a csv file, i've put a screen shot on the word document.

Cheers,

John

No - as stated, "the field separators are maintained to correctly position any following fields within the list.", ie, the NewLine separates each row, and there is no reason to fill in training commas where no data exists.

Your data view would leave me to believe that you have some spaces in cells for the first few rows. Perhaps a =Len(T2) would show that.

To upload any file, in Explore (My Computer) rightmouse the file and Add to Archive, set as a .zip.

hth
---
.

Originally Posted by hoopz

I am having a similar problem. I have an excel file that has five columns, and the fifth column is almost always blank. I expect that my CSV file will look like:
DATA1,DATA2,DATA3,DATA4,
DATA1,DATA2,DATA3,DATA4,
DATA1,DATA2,DATA3,DATA4,
etc.
and occasionally I will have:
DATA1,DATA2,DATA3,DATA4,DATA5
But the problem is, the first 16 rows are correct (16! same as john_mc had correct) and the rest of the rows don't have the last comma needed to indicate that there is nothing in column 5. So I'm getting:
DATA1,DATA2,DATA3,DATA4,
for 16 rows, and then everything after is:
DATA1,DATA2,DATA3,DATA4
This totally screws up my CSV file; I need that last comma after DATA4.
Any ideas?

Hi,

My first idea is that you should open a new thread rather than steal john_mc's thread, and refer back to this thread.

Data put to a .csv file is 'format' set each 16 rows (or so), thus if you setup a dummy file and put a space (or other character) in the 5th column you will get a batch of 16 rows where the 4th comma appears.
This will occur for each 16 (or so) row batches.

John_mc appears to have a space or other character in the 5th column of one of his first 16 rows to produce the result shown.

Your file is correct as I would expect the .csv to be.

If you really need the 5th column separator, you could put ##$## in the 6th column, then edit your .csv and replace ,##$## with nothing.
(on the assumption that ##$## is not valid data in your file)
Or,
You could put a formula into the 5th column [ if(1=2,"","") ] etc, this will evaluate to nothing but will force the field separator, overwrite the formula with any data as required.
Or,
of course, prepare a little VB code to do the output for you, this would depend on the frequency that you produce the .csv file (effort required vs results returned)

Hope this helps.
---

Originally Posted by hoopz

Well, I had gotten this to work by just putting an extra column at the end and then replacing, but I still don't see why Excel creates its CSV's this way.

Unknown, but I guess a more Excel-wise person would be able to enlighten you.

I was trying to import the data into MySQL and when it comes across a row that has less commas than it should, it gets confused.

Can your SQL not recognise that a NewLine means a new record and blank-fill any remaining fields?

Easy workaround, but it would be nice if Excel did the CSV file in a uniform way, rather than 16 rows and then just deciding to not include the comma. Or at least give some kind of options for how the CSV is saved...

. . . that you need to log with MS, here it has no effect.

Sorry for stealing your thread John, I didn't think you were using it any more

Hopefully the next edition . . .

---