Importing CSV files to Access

[Shakil]

Hello,

I am trying to upload 100+ csv files into MS Access, I am a amateur in VBA, could i possibly have some help writing the code?

The file location is, D:\Users\UKVIV00003\Desktop\Clients, the file format i need to import is csv, and i require all fields to be imported.
Also, what is the maximum amount of rows allowed in access,
Any help would be much appreciated.

 

[Norie]

Have you tried creating a macro for importing one of the CSV files?

If you get that working you could convert it to VBA and add further code to import multiple CSV files.

What that code will be is hard to tell without knowing more about your setup, eg are all the CSV files in the same folder, have they all to be imported to one table etc.

As for a maximum no of rows (records), there isn't really a set amount for that but there is a limit for the amount of memory a table can take up.

Each record (row) consists of multiple fields, each of those fields will take up a certain amount of memory so the maximum no of rows kind of depends on the no of fields, the date types etc.

 

[Shakil]

Hi Norie,

I am trying to import all the fields from multiple csv files in the following folder "D:\Users\UKVIV00003\Desktop\Clients"

I have set up the import template, however am struggling with finding the import feature on the macros.

I would ideally like all of the data imported into one table if possible.

Thanks for all your help!

 

[xenou]

Something like this. I usually import to a staging table so I follow that convention here too using a table called "tbl_TEMP". It should be structured like your destination table (though sometimes in my staging tables I use only Text and Date datatypes). This gives you a chance to make sure the data is correct before it goes to the final destination, and to do any cleanups or validations that you might like to do. Of course you can skip that and import right to the destination table if you prefer. Also I recommend you put all the field names in explicitly rather than using a wildcard - so you will know if something goes wrong - crash!

You must properly name and identify your import spec and the table names here.

[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] f [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="SeaGreen"]'----------[/COLOR]


    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] f [COLOR="Navy"]In[/COLOR] CreateObject("Scripting.FileSystemObject").GetFolder("D:\Users\UKVIV00003\Desktop\Clients").Files

        [COLOR="SeaGreen"]'//Truncate Temp Table[/COLOR]
        CurrentDb.Execute "DELETE * FROM tbl_TEMP;"
        
        
        [COLOR="SeaGreen"]'//Import File To Temp table[/COLOR]
        DoCmd.TransferText acImportDelim, "My Import Specification Name", "tbl_TEMP"
    
        
        [COLOR="SeaGreen"]'//Push To Destination Table from Temp Table[/COLOR]
        s = "INSERT INTO tbl_MyTable"
        s = s & " ("
        s = s & " Field1, Field2, Field3"
        s = s & " Field4, Field5, Field6"
        s = s & " )"
        s = s & " SELECT Field1, Field2, Field3,"
        s = s & " Field4, Field5, Field6"
        s = s & " FROM tbl_TEMP"
        CurrentDb.Execute s
        
        [COLOR="SeaGreen"]'//If you want to silently discard duplicate records in an indexed table use instead:[/COLOR]
        [COLOR="SeaGreen"]'DoCmd.SetWarnings False[/COLOR]
        [COLOR="SeaGreen"]'DoCmd.RunSQL s[/COLOR]
        [COLOR="SeaGreen"]'DoCmd.SetWarnings True[/COLOR]
        
    [COLOR="Navy"]Next[/COLOR] f
    

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

One suspects it may be a good idea to record the names of the files you've imported in some way, in case something goes wrong. Or at least to have a "DateLoaded" field with a default of Now(), so that if something goes wrong you can delete the records and start over from scratch.

 

[Shakil]

I have tried to apply the code however, I receive the error, 'the action or method requires a file name argument'. this happens on the line ;

DoCmd.TransferText acImportDelim, "My Import Specification Name", "tbl_TEMP"

I have renamed my import specification and table to match the code.

 

[xenou]

It does seem like we would need some kind of file name argument - otherwise how would Access know what to import? So I agree with the error message! Did you try adding a file name argument?
ξ