Appending CSV files into an existing table in Access

[deadeye123]

I have several CSV files in one folder. All files have the same format. I created an empty table in Access that has the same headers as the CSV files.
What I would like to do, is to append those csv files to my table in Access. It's an ongoing process, so those CSV files keep coming in to my folder every day, so I like to append those files and delete them after appending them on a weekly basis. I don't want to copy the headers though since I already have the headers in my Access table. (I could delete my table in Access if I can find code that just appends the CSV files and creates the headers and table in Access).
I tried the code below but the issue is, that everytime I use it, it deletes my headers in Access and doesn't delete the CSV files.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Option Compare Database
Sub Import_multiple_csv_files()


Const strPath As String = "C:\Contacts" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
strFile = Dir(strPath & "*.csv")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"Test", strPath & strFileList(intFile)
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"

End Sub</code>

 

[Micron]

You have a code comment on transferspreadsheet - this method will replace your table each time you run it.

If your main issue is the headers, be specific about the HasFieldNames parameter. You have not specified it, so it defaults to treating the first row as normal data so it's probably why you have a problem with headers. The command to delete a file is KILL, but I can't recall at the moment if that's part of the VB Script library or not - I think so. I wouldn't delete those files until you were sure you had it working for some time. To get around this, you could change the extension as part of your code procedure unless you can easily get new file copies after you've deleted them.

 

[deadeye123]

Thanks for some reason, i can't figure the header issue. i really need the headers to be part of the access table that i am importing data to

 

[xenou]

Since you have CSV files it's actually TransferText which applies, not TransferSpreadsheet.
As noted above, you designate whether or not to treat the first row as field names or not, when you use the TransferText method.

For example:

DoCmd.TransferText acImportDelim, , "MyTable", "C:\myTemp\Book1.csv", True

In general, the first time you import the data, you will create the table if it doesn't exist. After that, you would be importing into an existing table and the field names *must* match. Unless you are deleting the table before every import. But it doesn't hurt to create the table manually yourself, the first time, so that it has the correct field names and data types.
Note the empty second argument since we aren't using a saved specification (though that could be a good thing to do).

 

[Micron]

Don't really understand what the problem is based on your description of it. You're really only saying it doesn't work and haven't responded as to whether or not you tried setting the HasFieldNames parameter to True and whether or not that resulted in getting what you need. Not specifying defaults to False. If neither worked, then what was the outcome versus what you need? Were there any error messages? Was there errors in the resulting records?

 

[deadeye123]

Thanks. never tried the HasFieldNames parameter to True. where would i add it to the code? thanks again

 

[xenou]

See Post# 4:

For example:
Code:

DoCmd.TransferText acImportDelim, , "MyTable", "C:\myTemp\Book1.csv", True

Also google msaccessTransferText - very likely the first result will give you the help file on the TransferText method.