Missing commas after Excel Converts to CSV format (Delimiter)

I have save an excel file as a CSV file. Out of 11 columns, 8 are occupied with data. The CSV file would reflect:

Column1Data,Column2Data,Column3Data,Column4Data,Column5D ata,Column6Data,Column7Data,Column8Data,,,

The last three commas denotes the last three columns that have no data. However, the last three commas are shown only till the 16th line. It happens on other excel file converted to CSV format.

Perhaps you can try on any excel file and see what I mean.

Is this a known problem? What should I do? I need the commas reflected in the CSV file. Right now, I have to do it manually on each converted CSV file, by typing ,,, 17th line onwards.

Can I create a VBA programme or other language to read the file and input the three commas?
attcywh1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

geofflilleyCommented:
attcywh1:

I tried what you said...ran into the same problem.

This is the best workaround I've found so far:

1)  In the last three columns, put the word "nonsense," or something, and then auto-fill that down on each column, for all of your data.
2)  Save it as a native Excel file.
3)  Import the Excel file into Access.
4)  Run an update query in Access to set the value of the last three columns to NULL.
5)  Export the Access table to a CSV.

That should work.  Let me know if you have any questions.

Cheers,
geoff
0
amaronjobCommented:
You can also try,
1. copy range (1st row, last three columns)
2. paste "format only"  on all rows after 17th row for last 3 columns. (Using Paste special option on right click)
0
attcywh1Author Commented:
Hello

Thank you all for your replies. I don't know how to do Step 3,4,5 of Geoff's suggestion. I tired to do what amaronjob said, but it didn't work.

Pls advise again. Thank you.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

geofflilleyCommented:
OK.  To clarify steps 3-5:

1)  In Access, start a new database.  Call it whatever you want.
2)  Go to the "File" menu.  Choose "Get External Data."  Under "Get External Data," choose "Import."
3)  The Wizard should walk you through the steps.  If your Excel file has the column names in the first row, make sure you check the box that says, "First Row Contains Column Headings."
4)  Start a new query by clicking on the "query" button, and choose "Create a new query in Design view."  
5)  You should get a dialog that says something like, "Show Table."  Just grab the table you imported.
Click "Close" on the "Show table" dialog.
5)  In the "Query" menu, change the query type to "Update Query."
6)  Double-click on the last three fields to choose them.
7)  Under "Update To," put the word NULL in three squares.
8)  Click the red exclamation point that says, "Run."
9)  You'll get a warning that you're about to update so many rows.  Say "OK."
10) Close the query without saving.
11) Go back to your table by clicking "Tables."  Right-click on your table, and choose "Export."
12)  Change your "Save as Type" to "CSV."  Make sure you append your file with the "CSV" file extension.

That should do it.  Let me know if you have any questions.

Cheers,
geoff
0
attcywh1Author Commented:
I did everything you taught. Got an error at the last part when I tried to do an export. Error window read:

Microsoft Access can't find the database file 'C:\Sheet1.csv.mdb.'

Make sure you entered the correct path and file name.

I tired to save in different folders, but it won't accept.

Also, in the save-as field at the export window, it does not have CSV in the Save As Type.

Pls advise again. Thank you very much.

0
geofflilleyCommented:
I apologize...I was having a "not this program" problem...in Access, what you do is change the Files of Type to "Text Files" and manually append the "CSV" filename extension.  If you just let Access export the file, it'll save it as an mdb...my bad.  I apologize.
0
mekaneck84Commented:
Here's an easy workaround:

In the last three columns, put an apastrophe ( ' ) in each cell, and autofill it all the way down.
When you export into csv format, it'll include the commas but not the apostrophes!
0
moduloCommented:
PAQed - no points refunded (of 20)

modulo
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.