CODEWITHSUNDEEP
vbams-access
Devon
Devon

Reputation: 11

Importing multiple CSV files into 1 table - first row doesn't match

I've used the below code before to import multiple csv files from one folder into 1 table in Access Database. However, this time the first row of each file includes an account number, and the column headers are in row 2. So the first row of each file is different, and this code fails on "DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames"

How do I exclude the first row in each of the files?

I've tried changing the "blnHasFieldNames" to False in hopes that the code would accept any differences in row 1, but this did not work.

Dim strPathFile As String, strFile As String, strPath As String Dim strTable As String, strBrowseMsg As String Dim blnHasFieldNames As Boolean

    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = True

    strBrowseMsg = "Table Name"

    strPath = "FilePath"

    If strPath = "" Then
          MsgBox "No folder was selected.", vbOK, "No Selection"
          Exit Sub
    End If

    ' Replace tablename with the real name of the table into which
    ' the data are to be imported
    strTable = "Table Name"

    strFile = Dir(strPath & "\*.csv")
    Do While Len(strFile) > 0
          strPathFile = strPath & "\" & strFile

    DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames

    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    '       Kill strPathFile

          strFile = Dir()
    Loop

The Error I get is (xxxxxx= I used this rather than the name of the account)

Run-time error '2391': Field 'xxxxxxxxx' doesn't exist in destnation table 'Table Name'

Upvotes: 1

Views: 312

Answers (1)

Gustav
Gustav

Reputation: 55816

You will have to either open the file, remove the first line, save the file, and run the import; or read the file line by line, skipping the first line, and append the data line by line.

In both cases, the FileSystemObject object can be used.

Upvotes: 0

Related Questions