Help saving and importing CSV in VBA... with accuracy!

[Oliver Dewar]

Hi All.

For my businesses CMS - I need for several excel workbooks to communicate with each other.

I'm trying to save a sheet as a csv... then import it into another workbook.

The problem I'm encountering is that no matter whether I comma or tab delimit, when I try to import the sheet, excel puts some column headers as rows under other column headers, and totally mixes the row data up.

If it makes any difference, my csv sheet has columns A to HN and each row has a large number of blank cells. There's currently about 5,000 rows but that will increase hugely in actual use.

I would have thought that an excel saved csv imported back into excel would be seemless... but it can't even get the column headers right.

There must be something simple I'm missing here. Am I unaware of a 'CSV 101' tip like "always fill blank spaces with a placeholder" or something? (I actually tried that to no avail!).

All help will be welcomed.

I'm in windows 7 and excel 2007.

Thanks all!

 

[Oliver Dewar]

results are in... it did indeed put double quotation marks around everything... problem is that carriage return is still putting the middle of the text string on a new row in the csv import.

 

[Andrew Poulsom]

I've got no idea Andrew... they were all created in form textboxes with return enter response set to true. How can I work out what the ASCII number is?

You can use the CODE function to find out. If it's the fourth character in A1:

=CODE(MID(A1,4,1))

I expect its char 10. If that's the case the text should automatically be surrounded with double quotes when you save as CSV.

 

[Oliver Dewar]

Hi Andrew.

I did your test and the character returned as 110. Does that sound right to you?

I also did a test with the following VBA code with surprising results.

Code:

Cells(1, ActiveCell.Column).Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr(ActiveCell.Value, Chr(10)) > 0 Or ActiveCell.Row = 5000
If Not ActiveCell.Row = 5000 Then
MsgBox "I found a chr(10)"
End If
Cells(1, ActiveCell.Column).Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr(ActiveCell.Value, Chr(13)) > 0 Or ActiveCell.Row = 5000
If Not ActiveCell.Row = 5000 Then
MsgBox "I found a chr(13)"
End If

On the same cell as your formula test, this vba code told me there was BOTH a chr(10) and a chr(13)! I'm guessing there must be a single character that = both of those two, because there's only one carriage return, or line break in that cell. Bizarre.

Now we have an idea of the codes involved, how should I proceed? Should I replace all those codes with placeholders, then put them back in again in the destination workbook? Or is there a more elegant method?

 

[Oliver Dewar]

Andrew... I just did your test again, because I thought I may have had it wrong, and I was right (that I was wrong, if you get me!) Anyway... It turns out that I have a char(13) as character 2 and then a char(10) as character 3. This appears to be the case for all my 'carrige retuns'. It's 13 followed by 10.

 

[Oliver Dewar]

OK - just replaced:
chr(10) with chr(167) - a placeholder no one will input into the data
chr(13) with chr(164) - a placeholder no one will input into the data
and also chr(34) with chr(39) - double comma with inverted comma - to guard against problems with double quotes used in the csv process.

The save and import worked flawlessly - then at the destination workbook I'll simply replace the placeholders with the characters again and we have successful data transfer!

Thanks Andrew for your help with this. You are an excel rock star!