Results 1 to 3 of 3
  1. #1
    johnson is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2010
    Posts
    2

    Question Verify CSV Field Names Before Importing

    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

    Code:
    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

    EDIT:

    I've changed
    Code:
         varFields = Split(strFile, ",")
    to
    Code:
         varFields = Split(strBuffer, ",")
    And now receive the error, "Subscript out of range"
    Last edited by johnson; 01-26-2010 at 12:04 PM. Reason: update status of problem

  2. #2
    johnson is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2010
    Posts
    2
    This is all resolved.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Would you like to share your solution and then follow the link in my sig and mark this thread as Solved?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. creating field names in table without typing
    By GHanover in forum Database Design
    Replies: 2
    Last Post: 12-14-2009, 05:13 AM
  2. Changing field names in queries
    By scottsoo9 in forum Queries
    Replies: 1
    Last Post: 12-01-2009, 05:47 PM
  3. Replies: 5
    Last Post: 03-31-2009, 09:16 PM
  4. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 PM
  5. How to extract names from 1 large field
    By Tepin in forum Access
    Replies: 1
    Last Post: 12-04-2006, 01:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums