Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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