Advanced CSV File Import into Access

[@scottaxton2513]

Thank you! Just what I was looking for. I get a file from a mail house that supplies address corrections if people have moved and I've been searching how to automate the update of the records rather than typing in one at a time. This helps very much! Liked and subscribed, Look forward to additional videos.

[@njmusama]

Thank you for this great lecture, I hope you explain how to append the same file into an access table or append the file directly from the search box to an access table, in order not to increase the database size by duplicating data.

[@codekabinettcomen]

That's a good point. - I'm thinking about a follow up video, where I will cover this.

[@jaironaranjo3654]

Thank you so much for such agreat video great job

[@davegoodo3603]

Great video Phillip! Interesting approach, lots to learn and use. Thanks.

[@virterra]

Really good video. Thank you for taking the time to make it!

[@smiljanicn]

Great video! How to handle issue when your data does not start after first line? Also what if you dont have Field names in CSV?

[@codekabinettcomen]

If you run the Import Text Wizard, there is a setting "First Row Contains Field Names". If you open the hidden system table "MSysIMEXSpecs", which stores the configuration of the import specs, there is a "StartRow" column. For reasons unknown, this is not shown in the Wizard UI, but AFAIK it still works if you edit it in the table.

[@rabeezley]

Thank you for the great videos. How would you handle an import into a related table that had a foreign key to the primary key of the another table? For example you import in a list of customers who may or may not already exist and then you need to import in orders for those customers and relate them to the customers primary key.

[@codekabinettcomen]

Good question. Your files will probably have an external customer id linking customers and orders. You can use this id to link the orders to the right customer (and their PK) in your database and insert it into the FK of the orders table. The real problem are the potential customer duplicates. From experience I know there is no technical solution to solve 100% of this. You need to provide and user interface to the user to manually link/eliminate duplicates on those cases that cannot be solved technically.

[@peterfrigge5803]

This video showed clearly how to import csv-files into access. very good! I have a question: don't you follow the Microsoft rules when naming variables? For example: strName for strings, lngId for long integer, intVarName for integer an so on. It is very helpful when reading a foreign programcode for I can identify the types of the variables by their names.

[@bokkakesballa]

Can I import also xls to access trought a batch script?thanks

[@codekabinettcomen]

Yes, you can by using DoCmd.TransferSpreadsheet. However, import specifications are not supported, which makes the process simpler but also less flexible.

[@AMARHADI]

What if I have tabel that dont have primary key like sales or transaction data which can be repeated, and i want update based on monthly transaction but want just update it if it already imported before. thank you for your video, it help me so much.

[@codekabinettcomen]

No primary key is not ideal in any table. You can compare multiple fields if required. If all fields in multiple records are identical, I would aggregate the data until the duplicates are removed. There's no value in keeping individual records then. That applies to the data in the database as well as in the import.

[@dalygeek]

Can you share the ACCESS file with us please? Thanks in advance

[@codekabinettcomen]

I've added a download link to the video description.

[@pelegst]

Why not just link to the CSV instead of actually importing it into a temporary file? This way, you don't have to worry about deleting it and compacting your database. There may be a performance hit, but I don't think, as a practical matter, it will be anything to worry about.

[@codekabinettcomen]

Paul, very good point! This was actually in my first draft of the script for this video. I changed it because it will work only for the append query but not for the update query. - I will explain in more detail in another video.