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!

 

[Andrew Poulsom]

I can't test in Excel 2007 on Windows 7, but I can't reproduce your problem in Excel 2003 on Windows Vista. If I save this as CSV it is reproduced faithfully if I open it in Excel:

Excel Workbook
	A	B	C	D	E
1	Col1	Col2	Col3	Col4	Col5
2	123
3		234
4			345
5				456
6					567
Gaps

The CSV file looks like this in Notepad:

Col1,Col2,Col3,Col4,Col5
123,,,,
,234,,,
,,345,,
,,,456,
,,,,567

 

[sathish_fun]

hi
why cant you add "|" between each cell in a row and make it as one cell before saving it as .CSV and while importing Use "|" as delimiter and import hope it will solve you problem

try as below

[TABLE="class: cms_table_grid, width: 407"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD]R200[/TD]
[TD]F342[/TD]
[TD]F321[/TD]
[TD]F222[/TD]
[TD]R200|F342|F321|F222|[/TD]
[/TR]
</tbody>[/TABLE]


Note:- after concatenate done with "|" do copy past (in my case Column "E") and delete A to D

[TABLE="class: grid, width: 151"]
<tbody>[TR]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]R200|F342|F321|F222|[/TD]
[/TR]
</tbody>[/TABLE]

 

[Oliver Dewar]

Thank you both for your thoughts, especially you Andrew for trying to reproduce the problem.

The plot thickens... or perhaps gets clearer...

It seems the 'CSV 101' tip that I didn't know about was "CSV's don't like data with carraige returns in it!"

Some of my headers had carraige returns... not a problem - took them out and the data is at least importing with the right columns.

Problem 2... a lot of my data is text strings with carraige returns. How do I go about saving then importing a csv table full of carraige returns?

I've read that surrounding everything in "" sorts it... is there an easy way to put these around every cell in a massive table?

Cheers guys.

 

[Oliver Dewar]

ooh, just found my own answer - simply format the table to custom format \"@\"

Going to try the CSV process now and see if that does the trick...

 

[Oliver Dewar]

That's half an answer - this format doesn't put "" around numbers - which throws off the csv import. So on I look...

 

[Andrew Poulsom]

What is the ASCII number of your carriage returns?

 

[Oliver Dewar]

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?

 

[Oliver Dewar]

Andrew... I'm struggling to find a way to put a double quote around every cell value - number, text or even the odd period (.) with nothing else... without fail.

If you can give me a solution to this - put double quotes around everything - I can leave the carriage returns, make sure there are no double quotes in the text strings by replacing them with inverted commas and everything will be sweet... (I hope!)

 

[Oliver Dewar]

Actually, I may have solved it... does this look any good? I'm going to test now:

With Selection
.Value = Evaluate("=if(len(" & .Address & "),""""""""&" & .Address & "&"""""""","""")")
End With