• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

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
0
abfinfo
Asked:
abfinfo
  • 4
  • 3
  • 2
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
abfinfoAuthor Commented:
Thanks a lot for your help!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now