?
Solved

Missing commas after Excel Converts to CSV format (Delimiter)

Posted on 2003-03-24
9
Medium Priority
?
2,219 Views
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?
0
Comment
Question by:attcywh1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 1

Expert Comment

by:geofflilley
ID: 8200980
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
 

Expert Comment

by:amaronjob
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)
0
 

Author Comment

by:attcywh1
ID: 8207666
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
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.

 
LVL 1

Expert Comment

by:geofflilley
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.

Cheers,
geoff
0
 

Author Comment

by:attcywh1
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.

0
 
LVL 1

Expert Comment

by:geofflilley
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.
0
 

Expert Comment

by:mekaneck84
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!
0
 

Accepted Solution

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

modulo
Community Support Moderator
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

800 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