Excel to CSV

Swaroon

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

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Swaroon

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

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,363
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top