VBA to Import CSV - Code problem with CR/LF vs LF only

[jhrBanker]

I've recently begun to have problems with my VBA code to import CSV files. Most CSV files contain Carriage Return & Line Feed codes at the end of each record, and my code works fine with those files. However, I've recently been getting files that only contain LF codes at the end of the record. In this case, my code seems to interpret the entire file as one record, and the code doesn't work.

Can someone suggest a code modification work-around for this? I would like to avoid having to manipulate (fix) each file before importing, since there can be a large number of files each time.

Here's my code:

Private Sub cmdBacCRD_Click()
DoCmd.Hourglass True
CurrentDb.Execute "DELETE * FROM BacCRD;"

Dim rs1 As DAO.Recordset
Dim strFileDesc As String
Dim strFileExt As String
Dim strFilter As String


Dim strFileName As String
Dim FileNum As Integer
Dim InputString As String
Dim strArray() As String

strFileDesc = "Comma Separated Value (*.csv)"
strFileExt = "crd*.csv"
strFilter = ahtAddFilterItem(strFilter, strFileDesc, strFileExt)

strFileName = ahtCommonFileOpenSave( _
InitialDir:="C:\Users\jhrAcer\Documents\Banking\St mts", _
Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:="Select File for Import... crd*.csv", _
Flags:=ahtOFN_HIDEREADONLY)
FileNum = FreeFile()

Set rs1 = CurrentDb.OpenRecordset("BacCRD")

Open strFileName For Input As #FileNum
Do While Not (EOF(FileNum))
Line Input #FileNum, InputString
strArray = Split(InputString, ",")

If IsDate(strArray(0)) Then
rs1.AddNew
rs1.Fields("RecDate") = strArray(0)
rs1.Fields("RecTime") = strArray(1)
rs1.Fields("RecLastName") = Mid(strArray(2),1,18)
rs1.Fields("RecFirstName") = Mid(strArray(2),19,18)
rs1.Update
End If
Loop
rs1.Close
Close #FileNum
End Sub

Thanks,
John

[June7]

Maybe check if the string has Cr code and run different code depending on the result.

[jhrBanker]

Thanks. I'll try to figure out how to test for CR code, and then add code to change LF TO CR LF when no CR's are found.

[June7]

This test work in the VBA immediate window:

x = "HKJHHI" & vbCrLf & "iyuy"
?x
HKJHHI
iyuy
?InStr(x,vbCr)
7

So maybe:
If InStr(x, vbCr) = 0 Then
Replace(x, vbLf, vbCrLf)
End If

[jhrBanker]

I tried just adding a line of code to my above code to see if it would work, and it doesn't seem too. What am I missing?
Open strFileName For Input As #FileNum
Do While Not (EOF(FileNum))
Line Input #FileNum, InputString
* New line
InputString = Replace(InputString, vbLf, vbCrLf)
strArray = Split(InputString, ",")

[June7]

I don't routinely do imports so not sure. I just know it worked with a string in the immediate window.

What does "doesn't seem to" mean - error message, wrong results, nothing happens?

[jhrBanker]

Only getting one line of data.

[June7]

That would be the case because you are altering the string after it is pulled from the text file. It starts as one string and it stays as one string. Would have to modify the text file

Another alternative is to break the single string twice using 2 arrays. The first parses on the solo LF character and populates array. Then looping code reads each element of that array and populates another array to parse on the comma delimiter and populate record in table.

So would be different processes depending on whether the CR character is found.

[jhrBanker]

I like that idea. I'll give it a try.