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
abfinfoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
I've selected 65001 unicode (UTF-8) and it was imported
unicodeBut what you like to do with line breaks?
0
plummetCommented:
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
0
abfinfoAuthor Commented:
@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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

abfinfoAuthor Commented:
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:
    ' 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

Open in new window

0
plummetCommented:
Hmmm. How large is the CSV file likely to be?
0
abfinfoAuthor Commented:
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 ;)
0
als315Commented:
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:
unicode
0
plummetCommented:
You could import it in a different way, maybe using VBA:

Sub test()


Dim ifile As Integer
Dim sLine As String
Dim sAll As String
Dim i As Integer
Dim iRow As Integer
Dim iCol As Integer
Dim sArr() As String

ifile = FreeFile

Open strGeplBrgesPath For Input As ifile

Do While Not EOF(ifile)

    Line Input #ifile, sLine
    sAll = sAll & sLine

Loop

sAll = Replace(sAll, ";" & String$(2, 34) & ";", ";;")
sAll = Replace(sAll, ";" & String$(2, 34) & ";", ";;")
sAll = Replace(sAll, ";" & String$(2, 34) & ";", ";;")
sAll = Replace(sAll, String$(2, 34), Chr(34) & ";" & Chr(34))

sArr = Split(sAll, ";")

iRow = 1
iCol = 1
For i = 0 To UBound(sArr)

    activesheet.Cells(iRow, iCol) = Replace(sArr(i), Chr(34), "")
    iCol = iCol + 1
    If iCol = 9 Then iRow = iRow + 1: iCol = 1

Next

End Sub

Open in new window


It's worth a try! I have used your variable strGeplBrgesPath for the location of the CSV file, you may need to change that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abfinfoAuthor Commented:
Thanks a lot for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.