Solved

Import of ANSI CSV with linebreaks in datafields

Posted on 2012-04-13
9
483 Views
Last Modified: 2012-05-10
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
Comment
Question by:abfinfo
  • 4
  • 3
  • 2
9 Comments
 
LVL 39

Expert Comment

by:als315
ID: 37841793
I've selected 65001 unicode (UTF-8) and it was imported
unicodeBut what you like to do with line breaks?
0
 
LVL 10

Expert Comment

by:plummet
ID: 37841893
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
 

Author Comment

by:abfinfo
ID: 37841960
@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
 

Author Comment

by:abfinfo
ID: 37842051
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 10

Expert Comment

by:plummet
ID: 37842129
Hmmm. How large is the CSV file likely to be?
0
 

Author Comment

by:abfinfo
ID: 37842156
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
 
LVL 39

Expert Comment

by:als315
ID: 37842349
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
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
ID: 37842380
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
 

Author Closing Comment

by:abfinfo
ID: 37950802
Thanks a lot for your help!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Outlook Free & Paid Tools
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now