Create A Macro To Import Multiple CSV Files To Microsoft Access

In this tutorial, I am going to share a VBA script to auto import CSV/TXT/Excel files to Microsoft Access Database.
So this is a script I use almost everyday when I download my KZbin analytics reports and I need to import the files to my Access database. The advantage using a script to import your data files is when you have multiple files you need to import, a single action will finish the job.
► Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------
☕ Paypal: www.paypal.me/...
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: join.robinhood...
► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
🌳 Becoming a Patreon supporter: / jiejenn
🛒 By shopping on Amazon → amzn.to/2JkGeMD
🗓 Get updated on new Python videos → / madeinpython
📘 More tutorial videos on my website → LearnDataAnaly...
📺 Also check out my 2nd channel Excel channel focus on sharing Excel tips: bit.ly/3B1DjSA
✉ Business Inquiring: [e-mail]
#MSAccess #ImportFiles

[@kathylu7320]

Thanks a lot for sharing this! It’s truly a time saver!

[@jffranca2009]

Congratulations ! It was very helpfull to me !

[@personalaccount5823]

Hi, this is a great video. Can you please help with two points below: 1. The data would be in different sheets. I believe here only path has to be modified right? And file_name would be now sheet_name 2. While doing normal import I get options to select primary_key(I want none but by default it keeps the first column as key so i change that option every time) and select the data type which I have to set as date for some defined columns in each sheet. Is there any way to automate these? Thanks anyway, this video was helpful to me

[@Try2swipe]

Anyway to do this with xlsx? I changed "csv" to "xlsx" but it keeps showing me error 31519 saying "You cannot import this file"

[@soothing_music11]

Did you find the solution

[@benc7856]

I need to use a specific Spec when importing files. How can you utilize spec usage with this method? Great vid, thanks for this.

[@noname9432]

hello Jie, thanks so much , please advise if we have way to add all imported tables into 1 table with macro instead of importing them into individual table with sheet name then import them 1 by 1 I have more than 30 CSV file , it will be really appreciated and sorry for any inconvenience

[@jiejenn]

Let me look into it.

[@noname9432]

@@jiejenn appreciated dear ❤️

[@osamanasher2550]

Great thank you very much jie

[@jiejenn]

👍

[@osamanasher2550]

Kindly jie are you make vidio regarding sharing access in lan or hosting in web ? Also sending sms ????

[@joaovictorbarrosdasilva3020]

Many thanks! It's helps a lot!! Great work!

[@jiejenn]

Glad the video helped!

[@manjuviswam1762]

Thanks for this, is it possible to import files which are Pipe separated?

[@jiejenn]

I just checked the documentation and I don't see the delimiter parameter. I will have to look into it a bit more.

[@manjuviswam1762]

@@jiejenn Thanks for checking, I found out from another video that we can you saved specifications.

[@jasminlewis9845]

Can you help with a work around for the error message to store local object for importing run time error 3011 the Microsoft access database engine could not find the object…

[@jiejenn]

Can you be more specific? Probably need a little more details.

[@keshoweatherz8499]

Hello great video, very helpful. Unfortunately its failing to import csv with names exceeding a certain number say if the csv is saved with more than 26 characters. Any help on such ?

[@jiejenn]

That's the limitation imposed by the OS, you might have to Google the solution.

[@Bobo-wl6bs]

Thanks but I tried this for a table with a date time column and unfortunately it wouldn't work. It would keep giving me an error.

[@jiejenn]

Can you be more specific?

[@KarinS-tk3qt]

can you explain how can i get this tabels from access to sql by macro?

[@jiejenn]

Can you be more specific?

[@KarinS-tk3qt]

@@jiejenn This macro insert multiple csv into access. Thats great , i nedd also to insert excel file. I think I found the answer. What do you think? Public Function import_data_excel() Dim report_path As String, file_name As String With Application.FileDialog(msoFileDialogFolderPicker) .Show report_path = .SelectedItems(1) End With report_path = report_path & "\" file_name = Dir(report_path & "*.xlsx*", vbDirectory) 'MsgBox file_name Do While file_name vbNullString DoCmd.TransferSpreadsheet acImportDelim, , Trim(Replace(file_name, ".xlsx", "")), report_path & file_name, True file_name = Dir Loop MsgBox "Smile-Data Imported", vbInformation End Function

[@omarabbas910]

Hi Jie the macro works amazing but i found that if i rerun the macro to import new csv files the previously loaded data gets duplicated. Is there away to avoid that

[@jiejenn]

When you import a data file, you are basically performing a Union All operation. One approach to avoid duplicate records is to import the records to a temp table first, then insert the ones are not already available in the master table.

[@omarabbas910]

@@jiejenn can that be done within the vba code? Or would I have to manually do that?

[@giogobronidze7788]

Hello Jie, You got a new subscribe. I have a little problem, I do get the msgbox "Data loaded" but it is not loading any files i have in directory. I only see Macros and Modules but no Tables

[@jiejenn]

It's hard to troubleshoot without looking at your database file physically.

[@giogobronidze7788]

@@jiejenn Thanks for replying, I was missing a backslash on my path

[@soothing_music11]

What if we have .xlsx

[@theboyholty]

Oops. "can't execute code in break mode"

[@henrysen-opoku590]

Please, zoom the video. I didn't see a thing.