I'm trying to verify before importing a CSV file:
1) How many fields are present
2) If the number of fields is correct, are the field names correct as well
The CSV file uses tab as the delimiter with no text qualifier. If the CSV is verified as correct, the append query is run, transferring the data from the CSV that is a linked table.
This is the code so far and returns the error: "Subscript out of range"
- Function testCoRecordsCSV()
-
On Error GoTo testCoRecordsCSV_Err
-
Dim intFile As Integer
-
Dim strBuffer As String
-
Dim strFile As String
-
Dim varFields As Variant
-
-
strFile = "c:\company\corecords.csv"
-
If Len(Dir(strFile)) > 0 Then
-
-
intFile = FreeFile()
-
Open strFile For Input As #intFile
-
Line Input #intFile, strBuffer
-
Close #intFile
-
varFields = Split(strBuffer, Chr(9))
-
If UBound(varFields) <> 9 Then
-
-
MsgBox "The file does not have 10 fields in it"
-
-
Else
-
-
If varFields(0) <> "Test1" Or _
-
varFields(2) <> "Test2" Or _
-
varFields(3) <> "Test3" Or _
-
varFields(4) <> "Test4" Or _
-
varFields(5) <> "Test5" Or _
-
varFields(6) <> "Test6" Or _
-
varFields(7) <> "Test7" Or _
-
varFields(8) <> "Test8" Or _
-
varFields(9) <> "Test9" Or _
-
varFields(10) <> "Test10" Then
-
-
MsgBox "The ten field names do not match"
-
Else
-
CurrentDb.Execute "APPEND_A_1_corecords", dbFailOnError
-
MsgBox "File Appended"
-
End If
-
End If
-
End If
-
-
testCoRecordsCSV_Exit:
-
Exit Function
-
testCoRecordsCSV_Err:
-
MsgBox Error$
-
Resume testCoRecordsCSV_Exit
-
End Function
I've attached a zip of the database (Access 2007) and a sample csv file. Could someone take a look and see if they can tell why this isn't working? The database looks for the linked file in C:\company\
Thank you
