Is there a way to read a file one line at a time using a different delimiter than VBA's default (vbCr/vbCrLf)?

user_8408135 0
Viewed 319

I am trying to read a multi-line .csv file and put each token inside its own cell within Excel. The issue is that I have no control over the incoming .csv files.

They are all the same in terms of number of columns (variable number of rows, though, but that's easy to account for). However, for whatever reason the source of the .csv files sometimes puts a CRLF at the end of the header line and sometimes it puts only an LF. Again, I have no control over this so I must code around the variability.

As far as I understand, the VBA line Line Input #1, strLine uses either CR or CRLF to delimit lines, which makes sense. I have attempted to place the entire .csv file's contents into a single string and then use Replace strFileContents, vbCr, "" to eliminate all CR characters, but it seems as though this got rid of all later occurrences of CRLF as well and leaves me where I started.

So, is there a way for me to accept one line at a time using Line Input #1, strLine but a LF delimiter? Or is there another way I can manipulate the entire contents of a string (i.e. getting rid of only CR and not the instances of CRLF) and Split() the string from there?

Here is a shortened version of my code:

Dim Column, Row As Integer 'used Sheets().Input(Row, Column).Value to insert strings
Open strFilePath For Input Access Read As #1
Do Until EOF(1)
    Line Input #1, strLine
    strLine = Split(strLine, vbLf)(1)
    arr = Split(strLine, ",")
    For Column = 1 to 8
        'insert stuff
    Next Column
    Row = Row + 1
Loop

However this doesn't quite work for me, and I believe if it did I would still have to deal with the extra CR chars.

Thank you.

csv vba excel
1 Answers
asc(vbLF)=10
asc(vbCR)=13
asc(vbCRLF)=13

So Replace vbLF only.

Example

Sub test()

    Dim strTest  As String

    strTest = "First Line " & vbLf & "Second Line" & vbCrLf & "Third Line" & vbCr & "Fourth Line"
     strTest = Replace(strTest, vbCrLf, vbCr) '/ Gets rid of double line breaks caused by next replacement
    strTest = Replace(strTest, vbLf, vbCr)

    MsgBox strTest


End Sub
user_39617080
Related