How do I allow blank columns in a .CSV file?

[RPG7906]

I have a faily basic excel worksheet that I am saving as a csv file. The file
contains c400 rows and 15 columns. The last 5 columns of data are currently
empty. When I save the file and open it in Notepad to check it is ok, I can
see that only the first 15 rows are being saved correctly and that the rest
are not producing the required 5 commas (to depict the empty columns) as they
should. See below for a paste of the Notepad file. You can see the 5 commas
at the end of records 14 and 15 but not for subsequent rows where there are
no commas:

LOR991587,Fred Bloogs,Male,United Kingdom,Cleveland,LOR
UK,RpLoRb1039,NormalUser,ARRIVE,1,[email protected],Fred,Engineering
Surveyor,Male,22/05/1957,United Kingdom,,,,,,
LOR991588,Fred, Grogrs,Male,United Kingdom,Essex,LOR
UK,RpLoRa1039,NormalUser,ARRIVE,[email protected],Fred fred,Project
Director,Male,03/02/1955,United Kingdom,,,,,,
LOR991589,Janice, JolinMale,United Kingdom,Hertfordshire,LOR
UK,RpLoRb1040,NormalUser,ARRIVE,1,[email protected],Janice Joplin,Senior
Quantity Surveyor,Male,18/10/1957,United Kingdom
LOR991590,Julian, Jolpi,Male,United Kingdom,Norfolk,LOR
UK,RpLoRa1040,NormalUser,ARRIVE,1,[email protected],Julian Jolpi,Regional
Commercial Manager,Male,29/08/1958,United Kingdom

I have tried entering data into the last row/last column but this only seems
to correct the 2/3 rows above.

Any help appreciated!

 

[Dave Peterson]

Are you sure you need those additional commas? Lots of programs (excel itself)
won't care when you import it.

But if you actually do need those extra commas, I'd cheat. I'd put a formula
that makes the cell look blank:

=""

It's unique enough to remind me why I did it (when I see it in the formulabar).

And some background saved from a previous post:

This might describe the problem of too many commas in CSV files:

http://support.microsoft.com/default.aspx?scid=77295
Column Delimiters Missing in Spreadsheet Saved as Text

(It actually describes missing delimiter, but if some are "missing", maybe the
ones appearing are "extra".)

(But a lot of programs (excel included) don't care about those extra columns.
Maybe you don't have to care, either???)

Maybe you could write your own exporting program that would behave exactly the
way you want:

Here are some sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

 

[RPG7906]

Hi Dave,

thanks for your very prompty reply.

Unfortunately, I will need the columns eventually and any data entered will
have to be in a specific format to upload so I don't think the cheat you
mentioned will work.

I can't understand why the first 15 rows are ok and the rest aren't!!!

 

[RPG7906]

Hi Again Dave,

Actually the thread you posted explains it.

Not sure how to get round my formatting issue but at least I now understand.

Thanks for your help.

 

[Dave Peterson]

Did you try putting that formula (="") in the last column of each row that is
empty.

What happened when you created the .csv file?

 

[RPG7906]

I did.

However, when I saved the file and repopened it the formula had disappeared
in every row of the column.

So I tried just typing <space> into every row of the last column and that
did the trick.

 

[Dave Peterson]

I didn't know you were importing to excel. If that's what you want, then you're
not buying much by making sure that there are extra commas in the file.

If you're importing to another file, you should use a different program
(notepad????) to check how that .csv file looks.

I did.

However, when I saved the file and repopened it the formula had disappeared
in every row of the column.

So I tried just typing <space> into every row of the last column and that
did the trick.

 

[RPG7906]

Sorry Dave,

I didn't explain myself very well.

I am using the csv file to upload data from excel to a website - the website
programme sends email invites to candidates to take an online profile.

When I tested it, it only uploaded 15 records and rejected the rest, hence
my question.

I used notepad to see if all of the rows had the same formatting and
discovered the difference between the first 15 records and the rest was the
last 5 columns (depicted in notepad as commas) lacking data - hence why I
needed the commas to appear.
As your thread pointed out xl only searches for 16 rows in the last column -
which is why something has to be in it at least every 16 rows!

 

[Dave Peterson]

And I was pointing out that you may not want to import space characters into
your other program. But that's just a guess.

I'd still suggest ="" to create those extra commas.

Sorry Dave,

I didn't explain myself very well.

I am using the csv file to upload data from excel to a website - the website
programme sends email invites to candidates to take an online profile.

When I tested it, it only uploaded 15 records and rejected the rest, hence
my question.

I used notepad to see if all of the rows had the same formatting and
discovered the difference between the first 15 records and the rest was the
last 5 columns (depicted in notepad as commas) lacking data - hence why I
needed the commas to appear.
As your thread pointed out xl only searches for 16 rows in the last column -
which is why something has to be in it at least every 16 rows!