abfinfo
asked on
Import of ANSI CSV with linebreaks in datafields
Hi,
I want to import a csv file via VBA which contains linebreaks in datafields. Each datafield is using double quotes. I've tried to use chr(13) instead of chr(10) but this doesn't work at least in Excel 2007. Also I can't use UTF-8 or something like this.
I know there is a possibilty to use a placeholder and replace it via search/replace, but I don't like this solution because it's only a workaround.
Are there any other possibilities? (please post some code)
I've attached an example.
Best regards,
abfinfo
GES.txt
I want to import a csv file via VBA which contains linebreaks in datafields. Each datafield is using double quotes. I've tried to use chr(13) instead of chr(10) but this doesn't work at least in Excel 2007. Also I can't use UTF-8 or something like this.
I know there is a possibilty to use a placeholder and replace it via search/replace, but I don't like this solution because it's only a workaround.
Are there any other possibilities? (please post some code)
I've attached an example.
Best regards,
abfinfo
GES.txt
Hi,
If you're using VBA are you checking and replacing just chr(13) or chr(10) or both? Often it's both characters - vbCrLf or chr(13) & chr(10).
If you want to post your VBA here I am happy to have a look at it and see if I can help.
Regards
John
If you're using VBA are you checking and replacing just chr(13) or chr(10) or both? Often it's both characters - vbCrLf or chr(13) & chr(10).
If you want to post your VBA here I am happy to have a look at it and see if I can help.
Regards
John
ASKER
@als315: Thats no the correct import. It should have 8 colums and two of them are multiline.
@plummet: Thanks for your kind words. I've tried it with chr(10) OR chr(13) seperately, but i'll try it with both together and give a feedback.
@plummet: Thanks for your kind words. I've tried it with chr(10) OR chr(13) seperately, but i'll try it with both together and give a feedback.
ASKER
I've tried the combination of chr(13) together with chr(10) but it didn't work. I think I remember this works in Winword, but it doesn't work in Excel for a multiline cell.
Here is my import code:
Here is my import code:
' Importieren der Statistik Daten und trennen der Verbindung
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strGeplBrgesPath, Destination:=Range("A3"))
.Name = "STATISTIK"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Delete
End With
Hmmm. How large is the CSV file likely to be?
ASKER
About 10-20 records... Not really large... I know I could open it and rewrite the chars with a placeholder and do a search/replace again, but this is ugly ;)
I've edited file with Akelpad editor:
http://akelpad.sourceforge.net/en/index.php
1. Replace "\n with \t
2. Replace \n with nothing
3. Replace \t with \n
4. Import as unicode.
Result:
http://akelpad.sourceforge.net/en/index.php
1. Replace "\n with \t
2. Replace \n with nothing
3. Replace \t with \n
4. Import as unicode.
Result:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot for your help!
But what you like to do with line breaks?