ฝัง
- เผยแพร่เมื่อ 8 ส.ค. 2021
- 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 TH-cam 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/jiejenn/5
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: join.robinhood.com/jiej6
► 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 → LearnDataAnalysis.org
📺 Also check out my 2nd channel Excel channel focus on sharing Excel tips: bit.ly/3B1DjSA
✉ Business Inquiring: TH-cam@LearnDataAnalysis.org
#MSAccess #ImportFiles
Thanks a lot for sharing this! It’s truly a time saver!
Congratulations ! It was very helpfull to me !
Many thanks! It's helps a lot!! Great work!
Glad the video helped!
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
I need to use a specific Spec when importing files.
How can you utilize spec usage with this method?
Great vid, thanks for this.
Great thank you very much jie
👍
Kindly jie are you make vidio regarding sharing access in lan or hosting in web ? Also sending sms ????
Anyway to do this with xlsx? I changed "csv" to "xlsx" but it keeps showing me error 31519 saying "You cannot import this file"
Did you find the solution
What if we have .xlsx
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 ?
That's the limitation imposed by the OS, you might have to Google the solution.
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…
Can you be more specific? Probably need a little more details.
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.
Can you be more specific?
Oops. "can't execute code in break mode"
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
Let me look into it.
@@jiejenn appreciated dear ❤️
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
It's hard to troubleshoot without looking at your database file physically.
@@jiejenn Thanks for replying, I was missing a backslash on my path
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
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.
@@jiejenn can that be done within the vba code? Or would I have to manually do that?
Thanks for this, is it possible to import files which are Pipe separated?
I just checked the documentation and I don't see the delimiter parameter. I will have to look into it a bit more.
@@jiejenn Thanks for checking, I found out from another video that we can you saved specifications.
can you explain how can i get this tabels from access to sql by macro?
Can you be more specific?
@@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
Please, zoom the video. I didn't see a thing.