Excel to CSV

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
288
Office Version
  1. 365
Hi,
I have an excel file and cells A1 to A3 look like

"data1" "data2" "data3" etc if cell is empty then it shows as ""

When I save the excel as a CSV file, and open in Notepad, I get

"""data1""","""data2""","""data3""", etc if cell is empty then it shows as """"""

But I want

"data1","data2","data3", etc if cell is empty then it shows as ""

The "" are added to the data in each cell in a range by means of a macro - can i remove the macro and use a file format to ge tthe required result ?
What am I doing wrong ?
thanks
Steve
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi Andrew,
Thanks for the help, my excel file consists of a header record (A1:E1), data (A2:Z100) the number of recods can vary, and a footer record (A101:D101) row number depends on number of records in the data section.
What I'm finding is that the csv file is only showing the number of columns based on the header, so I am only getting 5 columns saved as the csv file.
I've added "xxx" in to all columns in the header so the column count will equal the data count of columns, this then means I get all the columns saved as a csv but some are now showing as "xxx".
What I can't then figure out is how to delete the "xxx" entries from the final csv file so I end up with a header of 5, data of 26, and footer of 4 columns - any ideas ? I've tried to add find/replace into the macro you provided but it doesn't work for me - the macro still runs but ignores the find/replace
 
Upvote 0
The code uses A1's CurrentRegion, so you should have the same number of fields for each record. This is what I got for header in 5 columns, data in 8 columns and footer in 4 columns:

"Head1","Head2","Head3","Head4","Head5","","",""
"Data1","Data2","Data3","Data4","Data5","Data6","Data7","Data8"
"Foot1","Foot2","Foot3","Foot4","","","",""
 
Upvote 0
Hi Andrew,
Yes, that's what I'm getitng - the file is for Direct debits and requires a header and footer record - is there any way to incorporate a find/replace into the macro to delete these entries or are you saying a csv must have the same number of columns in each row ?
thanks for your help, the macro works perfectly onthe main data
 
Upvote 0
Can I set the header row to have the same number of columns as the data, currently header is 6 and data is 26 columns, when I run the macro I only get 6 columns for all data ?
 
Upvote 0
What does?

MsgBox Range("A1").CurrentRegion.Address

return? The number of columns in that range is the number of columns you will get in the csv file. Blank columns will contain "".
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top