Import.csv file to the access table using VBA

[isladogs]

I was referring both to post #5 and the link in post #3 which I described as a fairly complicated example

Open CSV file from MS Access

Hi all, I am trying to open a blank excel workbook from MS Access and then bring in the data from another excel file. I need to do this because of the file i need to open is a csv file and if I open it normally i lose leading zeros. I have tried the below code but i cant get it to work. Any...

www.access-programmers.co.uk

 

[Pat Hartman]

and there will be files that contain more than 1 CSV.

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.

 

[arnelgp]

i added Saved (Yes/No) field to table1.
i added another button on Form1 to add to Table2 (the code will create the table for you).
those records in Table1 where Saved field is equal to False.

i also Disabled "delete records" so, Table1 will have the "history" of files you already
inserted in Table2.
if you delete the records in this table, chances are you will duplicate the records
in Table2.

also i added Unique Index to FPath field, so the file will not saved to Table1 again
if it already exists.

i haven't tested it yet, so i give you the first hand to test it.

 

[ebs17]

#17 ... looks like pseudo code and so probably isn't tested.

It is real and real working code, simple, direct, fast. Of course, you have to be able to replace placeholder identifiers for table name, field lists and import specification with those from your own real situation, and you should set the right directory for the files.

Depending on your own organization, you will then delete files in the loop after import or move them to an archive directory to prevent multiple imports of the same files.

 

[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.

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

You're welcome. Let us know what method you understood well enough to implement. And post your code. It always helps people who find the thread later especially when there are a multitude of opinions.