Import.csv file to the access table using VBA

A

aman

Registered User.
Local time
Today, 02:43
Joined
Oct 16, 2008
Messages
1,251
Hi Guys

Can anyone please provide some guidance on how to import data from a csv file to the access table using vba?

Do we need to create a table first those number of fields and what if any csv file has different fields or some different file format?

How the validations can be placed before importing it to access table?

Can you guys please share some ideas on this?
Any help will be much appreciated .

Thanks
 
Ridders, thanks for this. But this code doesn't show how to import csv file into access database using vba.

Can anyone please help me in this?

Thanks
 
Use TransferText method of DoCmd.
If the table is not yet created,
it will create one.
if already created, it will append new
records, eg:

DoCmd.TransferText acImportDelim, , "TABLE_NAME", "D:\BOOK1.CSV", True

"TABLE_NAME" refers to the new/or existing table
where you want the record appended.

google this method for more info.
 
You can indeed do what arnelgp said ...

...or you can just link to the csv file as a linked table.
I prefer to do the latter as then its 'live' i.e. the latest copy of the csv file.

VBA code is available to do that programmatically but its easy enough to just do it manually

Either way, the new linked or imported csv file will have all fields set as text.
So unless that's what you need anyway, you then need to manipulate the fields as required to import into your 'final table'

That was why I referred you to the other post.
In my approach, the important steps are
a) the linked CSV file is first imported into a buffer table (OMIT if importing as described by arnelgp)
b) the data is then modified to ensure correct datatypes & imported into the final table
c) the changes are logged to another table (OPTIONAL)
 
arnelgp said:
Use TransferText method of DoCmd.
If the table is not yet created,
it will create one.
if already created, it will append new
records, eg:

DoCmd.TransferText acImportDelim, , "TABLE_NAME", "D:\BOOK1.CSV", True

"TABLE_NAME" refers to the new/or existing table
where you want the record appended.

google this method for more info.
Click to expand...
What if i have a file that has dozens of csv files and I want to load them into one table in ACCESS. To do that i need to be able create a loop that each time through points at the next csv file. How do I interrogate the folder with the CSV files one at a time.?
 
Your question is morphing. Please try to tell us what you need to do and we'll help with how. So far it sounds like you have a directory and for each .csv in the directory, you want to append it a single table in the database. In this scenario, the .csv files are all identical in format. Is that the case? Then, what happens to the files that got appended? Do you want them moved/archived to a different folder? Do you want to prevent importing the same file multiple times?
 
Pat Hartman said:
Your question is morphing. Please try to tell us what you need to do and we'll help with how. So far it sounds like you have a directory and for each .csv in the directory, you want to append it a single table in the database. In this scenario, the .csv files are all identical in format. Is that the case? Then, what happens to the files that got appended? Do you want them moved/archived to a different folder? Do you want to prevent importing the same file multiple times?
Click to expand...
Yes, they are all the same. I found a way to get all the CSV files into a table, using a tool that I got from Moke or Isadog. Then I think I open an ado recordset using that table, and append the csv to a different table one at a time in a loop. I am assuming that is a ham-handed approach. The problem might be getting the data types synced between the CSV files and the table, which shouldn't be hard to fix. This is not truly morphing, I just found some data that I didn't know I had. But, if you have a better way of doing it I am wide open to suggestions.

Thanks
 
I think Pat assumed you were the OP when this thread was opened in 2017
It would have been better to start your own thread
No idea what tool you're referring to in post #10
Anyway, I suggest you try the approach I mentioned in post #5
 
Now I'm really confused. You have some magic tool that got the files into a single table but now you think you need an ADO loop to get the data into a second table? Why would you use a VBA loop at all and why would you want ADO? What is wrong with a simple append query at this point?
 
isladogs said:
I think Pat assumed you were the OP when this thread was opened in 2017
It would have been better to start your own thread
No idea what tool you're referring to in post #10
Anyway, I suggest you try the approach I mentioned in post #5
Click to expand...
Thanks
 
isladogs said:
You can indeed do what arnelgp said ...

...or you can just link to the csv file as a linked table.
I prefer to do the latter as then its 'live' i.e. the latest copy of the csv file.

VBA code is available to do that programmatically but its easy enough to just do it manually

Either way, the new linked or imported csv file will have all fields set as text.
So unless that's what you need anyway, you then need to manipulate the fields as required to import into your 'final table'

That was why I referred you to the other post.
In my approach, the important steps are
a) the linked CSV file is first imported into a buffer table (OMIT if importing as described by arnelgp)
b) the data is then modified to ensure correct datatypes & imported into the final table
c) the changes are logged to another table (OPTIONAL)
Click to expand...
The problem is that there are a lot more than 1 CSV file, there are about 200 of them, they all have the same format and data types, but need to be in one csv file. I have all the CSV's captured into one folder. and I guess that the only way to combine then is vi VBA, which i have a pretty good idea of how to create that using the (DoCmd.TransferText acImportDelim, , "TABLE_NAME", "D:\BOOK1.CSV", True) suggested by Arnelgp. Just a loop, read a CSV, append and repeat to EOFolder
arnelgp said:
Use TransferText method of DoCmd.
If the table is not yet created,
it will create one.
if already created, it will append new
records, eg:

DoCmd.TransferText acImportDelim, , "TABLE_NAME", "D:\BOOK1.CSV", True

"TABLE_NAME" refers to the new/or existing table
where you want the record appended.

google this method for more info.
Click to expand...
What if there are lot of CSVs each with a Header row, do I need to set something to ignore the header row after the first CSV file is added to the table?

.
 
I used to update a huge database for schools every night with external data from 30 CSV files.
The method works as described in my earlier post.
Although the purpose in my case wasn't exactly the same as you seem to want, I see absolutely no reason to do this with ADO recordsets or loops.
Use append queries - much faster to run and much simpler to code.

It might also help if you explained what this tool is that you got either from @moke123 or possibly myself.
 
The problem is that there are a lot more than 1 CSV file, there are about 200 of them
Click to expand...
What you can do once, you can do again.

Code: 
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
 
Last edited:
isladogs said:
I used to update a huge database for schools every night with external data from 30 CSV files.
The method works as described in my earlier post.
Although the purpose in my case wasn't exactly the same as you seem to want, I see absolutely no reason to do this with ADO recordsets or loops.
Use append queries - much faster to run and much simpler to code.

It might also help if you explained what this tool is that you got either from @moke123 or possibly myself.
Click to expand...
It was called ListFiles
 

Attachments

Pat Hartman said:
Your question is morphing. Please try to tell us what you need to do and we'll help with how. So far it sounds like you have a directory and for each .csv in the directory, you want to append it a single table in the database. In this scenario, the .csv files are all identical in format. Is that the case? Then, what happens to the files that got appended? Do you want them moved/archived to a different folder? Do you want to prevent importing the same file multiple times?
Click to expand...
Sorry for the confusion. I have a Folder that contains about 480 files. Each file in that folder contains data regarding phone calls, in CSV format, and there will be files that contain more than 1 CSV. In order for me to use that information, all the call(CSV) data, needs to end up in 1 table in ACCESS. I am in completely unfamiliar territory here, and probably asking too many question too soon before I have it clear myself. Just trying to cobble it together as best I can.

Sorry for the confusion
isladogs said:
I used to update a huge database for schools every night with external data from 30 CSV files.
The method works as described in my earlier post.
Although the purpose in my case wasn't exactly the same as you seem to want, I see absolutely no reason to do this with ADO recordsets or loops.
Use append queries - much faster to run and much simpler to code.

It might also help if you explained what this tool is that you got either from @moke123 or possibly myself.
Click to expand...
I can't seem to find your earlier post. Is it the one with the "complicated" example?
 
You must log in or register to reply here.

Similar threads

D
Setting up automated Microsoft Access database update using VBA?
Replies
14
Views
1,001
bastanu
bastanu
L
Data Change in CSV file after TransferText data export.
2 3
Replies
53
Views
2,063
DickyP
DickyP
S
No of Files Count
Replies
14
Views
653
The_Doc_Man
The_Doc_Man
P
Import text file
Replies
5
Views
361
ebs17
E
N
Using MS Access with WAR File
Replies
2
Views
222
sonic8
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom