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