Import CSV to Excel 2010 does not work correctly

[Parcival21]

Hi all,

I have a problem when converting an CSV into a xls file.
I have narrowed down the problem to the following:
The last column in the csv is a text field which contains several carriage returns.
Problem is now that as soon as the carriage return is detected a new line is started and the text is written in the first column again.
Replacing all carriage returns is also not an option because then it will write only one line in total.

Funny thing is that Excel 2003 on another PC does the translation perfectly but it is not working with Excel 2010 on my PC (and that is one requirement I have).

Is there any option I can use to transform this?
Any ideas? Doing some search/replace upfront would actually not be an issue for me.

BR
Tobias

 

[kjv1611]

How comfortable are you with VBA? There may be an easier route, but that may be the best route. Iniitially, I was thinking you could do a find/replace in the text file format of the carraige returns, but I don't really think that will work. My thought was replace one carriage return with two. However, that will still likely give you 2 records, I'm guessing.

But using VBA, you could read from and write to text. So...

You could use VBA to control the line breaks, etc, and then copy in the text, field by field, cell by cell... or perhaps row by row, not sure if it would work with this one (row by row)..

Here's a blog post talking about the very idea that I'm mentioning:

http://vbadud.blogspot.com/2007/06/vba-read-text-files-with-leading.html

And here's another with a module setup that you can just copy/paste in, and start using it, basically:

http://www.exceluser.com/explore/questions/vba_textcols.htm

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

hi,

Please explain how you IMPORT the .csv.

If you OPEN the .csv in Excel, you have a problem

If first open Excel, and in an empty workbook, you use Data > Get Extrenal Data > From Text... you will have better control over each column of data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

True... make sure you meet Skip's demands first. The other mention is more of an if all else fails approach.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Demands? Yikes! Sounds tyranical!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

[BLUSH] oops


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Parcival21]

Hey,
Thanks for all the replies.
@SkipVought: I tried that already and it didn't work.
Might need to go with the VBA thing. I will be able to manage that I guess but it will give me a hard time .

Only thing I am really confused about is: Why is it working with Excel 2003 and not with Excel 2010? What did they change? That is really confusing for me. I tried a lot already with replacing this and that but in the end I found no algorithm that will really do it.

If there is no other idea I will go with the VBA approach.

Thanks everyone,

BR
Tobias

 

[SkipVought]

and it didn't work.
[/qoute]
Exactly what does that mean, as there is a whole spectrum of possible interpretations, from ABSOLUTELY not one thing occurred, to the thing that did occur, was somewhat close but not precisely what was expected, in a manner as specified by, and then a description of the result ensues?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}