Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Import of ANSI CSV with linebreaks in datafields

Posted on 2012-04-13
9
Medium Priority
?
549 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 40

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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
 
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 40

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 2000 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

704 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