Verify CSV Field Names Before Importing

[johnson]

I'm trying to verify:

1) How many fields are present before importing a csv file
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 it doesn't work

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(strFile, 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

There are no errors, it just doesn't get past the first If statement. UBound always returns 0.

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

 

[gemma-the-husky]

i would import it, then check the field names

basically this will get you started

dim fld as field
for each fld in currentdb.tabledefs("mytable").fields
msgbox(fld.name)
next

 

[johnson]

Thanks, however the file can be enormous and shouldn't be imported unless the number of fields can be verified as well as the correct names for them.

 

[gemma-the-husky]

then assuming the file has a header row, open it read the first row, close it, use split to separate the header into an array of values, and test the values.

how big is an enormous file though? it doesnt take access long to import very big files, and its easier to do a lot of this stuff directyly in access

----------
just looking again, i see that is what you are doing - but you are using chr(9) as a separator - is this a tab - if so, its chr(8)!
thats why chr(9) is not splitting - or more accurately splitting into 1 section.

 

[johnson]

What you describe is EXACTLY what the code i've posted is supposed to do, but it doesn't work.

 

[johnson]

Chr(8) is backspace Chr(9) is tab (ref. asciitable.com)

Ubound is returning 0, so it's not even getting to the split with the first line I think.

 

[SimonB1978]

Hi,

Try splitting strBuffer instead of strFile

BTW, you have two strange characters at the beginning of strBuffer that dont show up when opening the file... insert a Debug.Print strBuffer to see them...

Simon B.

 

[johnson]

Thanks Simon,

I've finally resolved this. I had missed that strBuffer brain fart and a couple other things as well.

The special character issue you saw was nasty.

I ended up trimming the first X amount of characters that will remain constant and then splitting that.

 

[gemma-the-husky]

Johnson

sorry, So much wrong in so short a time

I had it in mind that chr(8) was a tab, and assumed that was the error - you obviously know what you are doing. I'll try harder next time!