Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Missing commas after Excel Converts to CSV format (Delimiter)

Posted on 2003-03-24
Medium Priority
Last Modified: 2009-03-01
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?
Question by:attcywh1

Expert Comment

ID: 8200980

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.


Expert Comment

ID: 8201014
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)

Author Comment

ID: 8207666

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.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Expert Comment

ID: 8208380
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.


Author Comment

ID: 8208593
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.


Expert Comment

ID: 8212957
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.

Expert Comment

ID: 8793525
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!

Accepted Solution

modulo earned 0 total points
ID: 10321167
PAQed - no points refunded (of 20)

Community Support Moderator

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question