Import Excel File into Multiple Tables

[dunnma]

Is it possible to have a script read an Excel file 1 row/column at a time. For instance say in Col A you have "artist_id" and in Col B you have "artist_name". The script would look in the "artists" table to see if the "artist_id" exists. If it doesn't, the data would be added and then continue. If it exists, the script would move to Col C & D where there is similar data (maybe "writer_id, writer_name") but would be stored in a separate table (maybe "writers").

 

[JerryKlmns]

dunnma

link your excel file to your database, and run the following

strSQL = "Insert Into FirstTable (artist_id, artist_name) "& _
"Select artist_id, artist_name " & _
"From MyExcelTable As A Left Join FirstTable As B " & _
"On A.artist_id = B.artist_id " & _
"Where B.artist_id Is Null;"

CurrentProject.Connection.Execute strSQL ,,adCmdText + adExcuteNoRecords

to append new records to your table from the excel file.
You could automate more this, for all columns in your excel but this is the general idea to start ..

 

[dunnma]

That looks like what I am looking for. 2 questions though, 1 - How do I "link" my Excel file to the database? 2 - How does the code above know where to get the artist_id and artist_name?

 

[JerryKlmns]

dunnma

File --> Get External Data --> Link Tables ... follow the wizard.

Now that the excel file is linked it is treated as an Access table. The excel rows are records and the columns are fields of the records.

 

[dunnma]

Oh, I am sorry. I want to design this so that the user does not have to go through the import wizard. I would like for me to put a "browse" link for them to just find the downloaded file and then the programming work from there, or better yet, just when the database is opened it automatically looks in the same folder for the file.

I understand what you are saying though about linking. Basically once the file is linked, it would be treated as a table, and then I could use normal SQL queries to "dissect the data" and delete rows.

The only question now is can I have the database "auto link" the file without user intervention (or allow the user to link the file without the import wizard)?

 

[JerryKlmns]

If you store the file at the same location and with the same name there 's nothing else to do...

 

[dunnma]

Thank you for your help. This actually turned out easier than I had thought. I created a command button called import and it will look for a predetermined file that is stored in the same folder as the Access database. It will import it into one table, then spread it accross the other tables and wipe the original.

I only receive a few import warnings (which I am not sure I can do anything about). One of the columns is set to text, but when the fields are imported, it thinks they are numbers because often the first row imported the value is a number, but it can very. Everything still imports however.

I do have one question that you might know the answer to. Can I name the file with any extension I want? It is a standard CSV, but could I name it "import.imp" or something like that (so to a user it looks like it is a proprietary format)?

 

[JerryKlmns]

To fool Access about the field data type, use a text record for your first row


Can I name the file with any extension I want?

You can even change name and extension

Name "C:\asd20050829.csv" As "C:\import.imp"

 

[dunnma]

I added this:

Name CurrentProject.Path & "\import.csv" As CurrentProject.Path & "\import.imp"

Above this:

DoCmd.TransferText acImportDelim, , "tblImport", CurrentProject.Path & "\import.csv", True

And I receive an error that the file was not found. Do I need to set it as a variable?

Thank you for your help.

 

[PHV]

And this ?
DoCmd.TransferText acImportDelim, , "tblImport", CurrentProject.Path & "\import.imp", True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dunnma]

I receive "file not found" and the debugger points to the first row below

Name CurrentProject.Path & "\import.csv" As CurrentProject.Path & "\import.imp"

'Load offline data CSV
DoCmd.TransferText acImportDelim, , "tblImport", CurrentProject.Path & "\import.imp", True

 

[dunnma]

I figured it out. It works like a charm. Basically all it does is rename the file, import it, and then I rename it back to what it was (for appearances sake). I will do research to see if it is possible to delete the file, or erase the data in the file.

 

[dunnma]

Well, lets call this thread "closed".

I used the Kill command to delete the file.

Thanks for all of your help guys!