Verify CSV Field Names Before Importing 1

[jjohnson123]

I've posted this question elsewhere, but no one seems to know how to fix the code...

Before importing from a CSV, 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 with field names in the first row. 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.

Can someone take a look and see if they can tell why this isn't working?

Thank you!!!

 

[SkipVought]

Hi,

How about this...

    Line Input #intFile, strBuffer
    Close #intFile
    varFields = Split([b]strBuffer[/b], Chr(9))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjohnson123]

Hi Skip,

Thanks, completely didn't see that. Now it gives an error, "Subscript out of range".

 

[SkipVought]

...on???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjohnson123]

I typed the varFields out of sequence which was causing that error.

Now I've got another can of worms with this. There's potentially 2 CSV files, generated from different sources, that seem to encode the CSV file differently.

They both create a funny looking character first character, kind of like a y with 2 dots over it with a sideways tongue to the right of it?

This causes the first varFields(0) to not match.

And then with one source it seems to read in the whole csv it looks like because Ubound is returning 552 in a msgbox.

So now I guess I'm down to figuring out how to omit that first character from the line input so varFields(0) is read properly, as well as recognizing the end of the row in the second source.

Opening the second source in notepad shows a vertically rectangular box at the end of each row, but it's not being recognized or something when inputing the line.

 

[SkipVought]

BTW, a CSV file is a Comma Separated Values file or a COMMA delimited text file.

You, on the other hand, have TAB delimited text files.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjohnson123]

Hi Skip, yeah that's why I use Chr(9) as the delimiter, it works.

I fixed the funny character problem by trimming the first 2 characters off varField(0).

If (Mid(varFields(0), 3, 5)) <> "Test1" Or _

Solved! Works perfect.

My last problem is the line input recognizing the end of the first row...

The artificial file I have access export works perfect, this whole problem is solved.

But the real csv with the actual data in it, that is supposedly the same (tab delimited, no text qualifier) also has the funky vertically rectangular box at the end of each row that the line input isn't recognizing for some reason.

 

[SkipVought]

You need to determine what that character is.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjohnson123]

Part of the problem is that one of the files has the character the other doesn't. And I'm having a hard time even finding what the character is, there's a bunch that show up as boxes...

Is there a way in the line input to only input the first 76 characters? That's the number of characters in both files until the end of the first row containing the names.

I could then split that?

 

[jjohnson123]

Skip, THANK YOU SIR.

I used an extra variable (prepBuffer) to trim out the first 2 special characters and the exact number of characters to import and then split that.

Works on both CSV files.

Man you MADE my week. Thanks for taking the time to help me work through this.