By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

Verify CSV Field Names and Number of, Before Importing

P: 2
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"
Expand|Select|Wrap|Line Numbers
  1. Function testCoRecordsCSV()
  2. On Error GoTo testCoRecordsCSV_Err
  3. Dim intFile As Integer
  4. Dim strBuffer As String
  5. Dim strFile As String
  6. Dim varFields As Variant
  7.  
  8. strFile = "c:\company\corecords.csv"
  9.     If Len(Dir(strFile)) > 0 Then
  10.  
  11.     intFile = FreeFile()
  12.     Open strFile For Input As #intFile
  13.     Line Input #intFile, strBuffer
  14.     Close #intFile
  15.     varFields = Split(strBuffer, Chr(9))
  16.     If UBound(varFields) <> 9 Then
  17.  
  18.         MsgBox "The file does not have 10 fields in it"
  19.  
  20.     Else
  21.  
  22.          If varFields(0) <> "Test1" Or _
  23.             varFields(2) <> "Test2" Or _
  24.             varFields(3) <> "Test3" Or _
  25.             varFields(4) <> "Test4" Or _
  26.             varFields(5) <> "Test5" Or _
  27.             varFields(6) <> "Test6" Or _
  28.             varFields(7) <> "Test7" Or _
  29.             varFields(8) <> "Test8" Or _
  30.             varFields(9) <> "Test9" Or _
  31.             varFields(10) <> "Test10" Then
  32.  
  33.             MsgBox "The ten field names do not match"
  34.    Else
  35.        CurrentDb.Execute "APPEND_A_1_corecords", dbFailOnError
  36.        MsgBox "File Appended"
  37.     End If
  38.   End If
  39. End If
  40.  
  41. testCoRecordsCSV_Exit:
  42.     Exit Function
  43. testCoRecordsCSV_Err:
  44.     MsgBox Error$
  45.     Resume testCoRecordsCSV_Exit
  46.     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
Attached Files
File Type: zip importcheck.zip (19.7 KB, 68 views)
Jan 26 '10 #1
Share this Question
Share on Google+
1 Reply


P: 2
I solved this problem. Thank you.
Jan 26 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.