Import.csv file to the access table using VBA

[jpl458]

What does that mean? A file can't contain another file. Are you saying the file has multiple rows? #17 probably solves your problem. It looks like pseudo code and so probably isn't tested. Try it and see what happens.

Basically the process is to browse through the folder with the files and for each .csv in the folder run an append query that to append the contents of the file to the specified table. The sample code uses an unusual method that embeds the text file name in the query rather than linking the text file and then running a more common table to table append query.

I was reviewing all the posts from yesterday. I didn't get the nomenclature correct when I was describing the problem. There Is a folder that contains CSV files. Each CSV file can have one or more "rows" of data. I need to get each of the CSV files into one table table.

Example:

A partial picture of the contents of the folder

I am looking at this code provided by ebs17 as a possible solution

Sub Import_CSV()

    Dim db As DAO.Database
    Dim sPath As String
    Dim sFile As String
    Dim sSQL As String
    Set db = CurrentDb
    sPath = "X:\AnyWherde\Importfiles\"
    sFile = Dir(sPath & "*.csv")
    Do While sFile > vbNullString
        'Debug.Print sFile
        sSQL = "INSERT INTO TableX (Field1, Field2, Field3)" & _
            " SELECT T.FieldA, T.FieldB, T.FieldC" & _
            " FROM [Text;DSN=NameSpecification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;" & _
            "DATABASE=" & sPath & "].[" & sFile & "] AS T"
        db.Execute sSQL, dbFailOnError
        sFile = Dir
    Loop
    Set db = Nothing
End Sub

But, I was wondering that since all the CSV files are exactly alike, and the table already has column headers that match the CSV files, couldn't a solution look something like this:

Option Compare Database
Option Explicit
Dim rs As DAO.Database

Private Sub AppenCallCSVFiles_Click()
    rs.MoveFirst
       Do Until rs.EOF
       DoCmd.TransferText acImportDelim, , "LogExpanded", "C:\PathToTheCVSFiles"", False"
       rs.MoveNext
   Loop
rs.Close
Set rs = Nothing

End Sub

My immediate problem is the container the code is in: Dim rs As DAO.Database is wrong, and I think I remember you saying that I shouldn't use a recordset. Been reading and still don't have that part straight. Any advice would be appreciated.

I just realized what you said earlier that in the second bit of code that the record being read would never change.

I am going to step back and let the fog clear.

Hope I didn't stretch your patients too much.

Thanks Pat.

 

[jpl458]

The TransferText method requires the specific, full, filename. No wildcard. You need to make the file name a variable that you fill with each file name as you loop through the files in the directory.

However, the loop is NOT of a recordSet, it uses FSO (File System Object) or the Dir

Here is a sample database that shows three imports. One uses a loop. I've also attached 4 files to use with the sample.

The third sample also logs each file and assigns a sequence number which is appended with the data so for each row in the data table, you can tell by looking in the log file what source file that row came from. The example also raises an error if you try to import the same file more than once as well as complaining about but ignoring any file in the folder without the "known" extension. This example imports both .csv and excel files to the same target table. Of course, they must have the same format for this to work. If you want to import .csv or .txt files, you MUST create an ImportSpec first for your specific file format. This is done by importing the file ONCE manually so you can setup through the wizard and define the column types, etc. Then using the Advanced button, you save the file spec before exiting the wizard. This is the "old" style spec and it is much more flexible than the "new" spec which gives you a "save import" option.

Thanks for the examples, Thanks for everything.

 

[Mark24]

TransferText worked while I had Access open. When I closed and reopened it returned a file not found error message. This thread helped me figure out that the full path was missing from my TransferText. The file name without the path will work, until Access is closed. Pat asked for code. This is simple now, but a lot of hours went into getting this far. Thanks for everyone's advice.

Dim sourceDirectoryName As String
Dim sourceFileName As String
Dim sourceFileNameFullPath As String

    sourceDirectoryName = "\\Readyshare\Primary\Bank Statements\"
    Debug.Print sourceDirectoryName

    sourceFileName = Dir(sourceDirectoryName & "*checking*.csv")
    'Finds the first file with "checking" as part of the file name
    Debug.Print sourceFileName

    sourceFileNameFullPath = sourceDirectoryName & sourceFileName
    Debug.Print sourceFileNameFullPath

    Do Until sourceFileName = vbNullString
        DoCmd.TransferText acImportDelim, "ImportChecking", "Checking", sourceFileNameFullPath
        'ImportChecking created in Import/Export Specification in the Import Text Wizard
        'Checking is the destination table for the import
        sourceFileName = Dir()
        'Finds the next file with "checking" as part of the file name
        Debug.Print sourceFileName
        sourceFileNameFullPath = sourceDirectoryName & sourceFileName
        Debug.Print sourceFileNameFullPath
    Loop

Edited for code readability. Thanks for the advice. I did try to make it readable the first time