Excel to CSV

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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","","","",""
 

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Yes, normally, a csv has the same number of columns in each row.
 

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
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 ?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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 "".
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,875
Members
431,771
Latest member
CoryMelth

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
Top