Reading csv file with strange line deliminter in VBA

Question

I have a input file which I am struggling to read in line by line, The file can be found here and is also shown below:

I would like to add the first value as key and the third value as item in a dictonary

Then later I can do this: a = myDictonary("CREATED_BY") and this will then return "Eigil..." (Order and number of lines my vary from time to time..)

But somehow I can not get the split to work:

Dim hf As Integer: hf = FreeFile
Dim lines() As String, i As Long

Open FileName For Input As #hf
    Line Input #hf, dataLine
    lines = Split(dataLine, vbNewLine)
    lines = Split(dataLine, "\n")
    lines = Split(dataLine, "CR")
    lines = Split(dataLine, "LF")
Close #hf

I also tried to follow this thread

For people who like to use dictinary here is my code for that:

 Set getProjectDictionary = CreateObject("Scripting.Dictionary")
    Dim item As String
    Dim key As String
    Dim dataLine As String

    Open FileName For Input As 1

    While Not EOF(1)
        On Error Resume Next
        Line Input #1, dataLine
        temp = Split(dataLine, ",")
        If Not temp(0) = "" Then
            getProjectDictionary.Add temp(0), temp(3)
        End If
        Wend
    Close 1

I added some debug output below:

score 2 · Answer 1 · edited May 23 '17 at 11:58

I think this has the answer - split on vbcrlf?

CRLF in VBScript

Of the 4 examples you gave, "CR" and "LF" would look for the literal strings "CR" and "LF", which is not what you want. VB doesn't recognize "\n" like most C-like languages, so that's out. vbnewline was the closest to working, but I think this might help you:

http://www.jaypm.com/2012/08/the-difference-between-vbcrlf-vbnewline-and-environment-newline/

score 2 · Accepted Answer · edited May 23 '17 at 11:52

2

The screenshot you attached shows that the file uses CR LF as linebreaks but the file I downloaded from your Google Drive link actually uses LF only, so you might want to use:

lines = Split(dataLine, vbLf)

Also, the file uses Little Endian UCS-2 encoding with BOM. If you simply open the file using the Open statement, you are likely to run into corrupt characters and other encoding related problems. I would suggest using Filesystem object instead.

edited May 23 '17 at 11:52

Community

1
1

answered Apr 27 '16 at 12:36

Petr Srníček

2,296
10
22

VBLf did the trick, thanks heaps. I will now quickly edit the code and upload the complete code – skatun Apr 27 '16 at 12:44

score 2 · Answer 3 · answered Apr 27 '16 at 12:57

Here is my code that currently seems to work well:

Option Explicit

Sub test()
    Dim a As Object
    Set a = getPropertiesDictionary("c:\Temp\Creo\param_table.csv")
    Debug.Print a.item("PTC_WM_CREATED_BY")

End Sub


' populate dictinoary with document types based on input file
Function getPropertiesDictionary(FileName As String) As Object

    Set getPropertiesDictionary = CreateObject("Scripting.Dictionary")
    Dim temp() As String
    Dim dataLine As String
    Dim hf As Integer: hf = FreeFile
    Dim lines() As String, i As Long

    Open FileName For Input As #hf
        Line Input #hf, dataLine
        lines = Split(dataLine, vbLf)
    Close #hf

    For i = 0 To UBound(lines) - 1
        temp = Split(lines(i), ",")
        If Not temp(0) = "" Then
            getPropertiesDictionary.Add temp(0), temp(2)
        End If
    Next

  End Function

Reading csv file with strange line deliminter in VBA

3 Answers3