Data import to multiple tables (1 Viewer)

[flebber]

HI

i was reading allen brownes guides in particular this one http://allenbrowne.com/casu-06.html .

I am setting up my tables now but am wondering if there is a good guide or how-to on pulling a data sources (csv, xml or excel) file into multiple tables.

Most examples I find show how to bring data into one table via import but realistically that isn't going to happen very often. This must be a fairly common question can access 2007 do this standard or do I need to use vba to code a solution?

 

[ions]

I don't think Access can import a csv into multiple tables.

I think you will have to break up the imported table into multiple tables once you import.

Maybe Access can create multiple tables from a multiple worksheet excel workbook in one import.

Check out ImportSpecifications if you want to repeatedly import the same file.

 

[flebber]

So import data into a holding table containing all values of other tables and then set a macro to split the imported holding table up into the relationalized tables.

This would be extremely common wouldn't it? I don't want to re-invent the wheel with something that would have been done many times before.

 

[Galaxiom]

It can be done by a series of Insert queries from the imported flat file into related tables. If it is a one off task you would just run the queries manually.

Although has certainly been done many times, the exact requirements of each database are different so there is no structured facility provided in Access to do it.

 

[smig]

either you import all data into a single table and take the data from there to the all other tables, or you do a many import routines each to a different table.

with Excel files you can go with second method by defining the imported area.
with text files you can go with second method by defining deferent import schema for each table

 

[flebber]

As I will go down the Excel path. It seems the "One table" method would be used. Do I define a "Dummy" table for the imported data to go in or just let it create its own table, perhaps create a macro to always rename it to a fixed name and delete table at end of process.

In the middle of this process I assume I am running insert queries into the database tables and because this will be a common task it will have to be set up as a macro. Is there any easy way to check that I am maintaining data integrity on import that each record that is being inserted into the other tables is relating correctly?

 

[ions]

Flebber to ensure data integrity set up your tables and relationships before you start transferring records from you dummy table to your legit tables.

In tables you can set Primary Key, Index, Compound Index, Required Value, Validation Rules, etc... best way to learn about this is to pick up a book.

Once you are done setting up your tables create relationships between tables using the Relationship Window. Make sure you set up Referential Integrity and Cascading Updates.

Once you are done the relationships set up your Append Queries and then set up a macro that runs all your append queries.

Also as mentioned if you will be repeatedly importing you can set up ImportSpecifications so you don't have to go through the import wizard and put the Import into a macro as well.

 

[flebber]

Ah cool, I already have my tables and relationships set up so I just need to figure out an append query and how to set these to a macro and I should be fine.

 

[lehcarrodan]

Did you figure this out? I am looking to do something similar. Paypal gives us our orders and I want to append these to my orders table but need some of the info to go to order details table and my customers table.

Any more light you can shed on this would be great!

 

[tmguru]

Hi All,

I'm looking to do the same thing - import data from Excel (which seems easiest) into existing Access 2016 tables on a monthly basis. From the info in this thread, it looks like importing Excel into a new table each time using an import specification for repeat tasks then writing a macro to insert each field into the correct tables is the way to go. Has anyone done this and do you have an example of a macro to do this? Thanks!

-tmguru

 

[The_Doc_Man]

To your question:

Yes, this is the way to go. An import specification using pre-defined tables as targets will work, followed by using a few INSERT INTO ... FROM queries to distribute the data would probably give best results.

Yes, I have done it using the "one predefined table" method for the import. However, because I was doing validation that was beyond simple field validation rules, I used some VBA and recordsets to examine the table first and then ran queries to distribute to three different permanent tables.

No, the database in which I did this was from a few years ago and I don't own it. Since I have retired, I have no way to get to it to present anything.

 

[tmguru]

Thanks very much Doc Man! I'm still new-ish to Access and macros. I think I can see how the insert statement would work in a macro for one record. Would I create an if loop using goto record next in the source table to loop through the rows until I get an error when I reach the end? If not or if there is a better way could you provide a suggestion please?

-tmguru

 

[isladogs]

It isn't clear whether you are always importing from the same (but updated)Excel file

I suggest a slightly different approach.
Link to or import your excel file
Then create a procedure including append queries or the SQL equivalents to insert dats to the relevant tables.
If you will also need to update existing data, include update queries or SQL equivalents
The procedure can also include any processing of the data before it is used to append or update. This may be necessary as well as or instead of an import specification

VBA procedures are far more powerful than macros and will make the whole process far easier to manage.

Good luck

 

[tmguru]

The Excel file will always have the same name/location but will be a new file every month. There is (currently) some manual normalization that has to take place in the Excel file before the records can be imported. I'll look to find a way to automate the Excel normalization later. I have created a saved import to a new table from which I expect to insert the records into the correct tables. There will be no updates to existing data only new records monthly. Yes I can see where VBA would be a good solution. Given the above, any changes to your suggestions?

 

[isladogs]

From the sound of it, you just need to run several append SQL statements in the VBA procedure.

However why not try to do the 'manual normalisation in Excel' as part of the Access procedure?

 

[tmguru]

Yes, I'm taking this a step at a time. I do believe I'll be able to normalize within the procedure. It will take some column renaming but I suspect it's very doable.

I created some VBA code to import the columns into my three table and it worked, sort of. I got the data into the tables but the referential integrity is not working. None of my lookups from other tables show the newly inserted data. Here's the VBA code I have:

Private Sub cmdImportAmazonRecords_Click()
Dim SQL As String
SQL = "INSERT INTO Table2 (DatePurchased, Price) SELECT DatePurchased, Price FROM tblAmazon;"
DoCmd.RunSQL strSQL
SQL = "INSERT INTO tblItems (Item) SELECT Item FROM tblAmazon;"
DoCmd.RunSQL SQL
SQL = "INSERT INTO tblSupplier (Supplier) SELECT Supplier FROM tblAmazon;"
DoCmd.RunSQL SQL
End Sub

I'm sure I'm missing something basic but not sure what it is. Any suggestions on how to correct this?

 

[isladogs]

Why do you need to do some renaming of Excel columns?
Append queries don't have to append data to fields with the same name.

Is tblAmazon the table containing your imported Excel data?

Not clear what 'it worked, sort of' means.
Do you have RI between the 3 destination tables? Cascade update/delete?
The order you do the appends may matter for the import to be successful

Change your variable name from SQL to e.g. strSQL as SQL is a reserved word.
Also suggest you
1. Use CurrentDb.Execute with dbFailOnError instead of DoCmd.RunSQL
2. You alter the 3 append statements so the data can't be imported twice.
Basically an unmatched append query
3. Lookups at table level are a bad idea if that's what you meant

 

[The_Doc_Man]

Would I create an if loop using goto record next in the source table to loop through the rows until I get an error when I reach the end?

No. If you were working with recordsets, you would do something similar to...

Dim rsWork as DAO.Recordset
dim lNumRec as Long

...

Set rsWork=CurrentDB.OpenRecordset( "<temp table name goes here>" )
rsWork.MoveFirst
rsWork.MoveLast
lNumRec = rsWork.RecordCount

Note that there are other options that follow the recordset source name depending on what else you might be doing.

Now if you were not using recordsets at all, it could be as simple as

...
CurrentDB.Execute "DELETE * FROM <name of temp table goes here>"

...
{perform your import to your temp table}
...
lRecNum = DCount( "*", "<name of temp table goes here>" )

The above operation cleans out the temp table, loads it via whatever import you are doing, and then counts what is in the table. If you are using SQL, there is an implied test for <end-of-table> so that unless you create a really crazy Cartesian JOIN, it will just do the right number of records based on the records in the source table. You don't need to give it a count. It will have one already.

You could also (if you were using recordsets) just end when rsWork.EOF is true. You can look up many of these ideas on the web or using this forum's Search function.

 

[The_Doc_Man]

tmguru

Note that in the three queries you showed in your post #16, that the last two queries each created a single-field table, which is somewhat questionable.

Some food for thought:

A single-field table is just as legal as any other - but strategically it is of limited value. At most you would consider it as a validation table. But if the data set you captured include any duplicated items or suppliers, your table would contain duplicates as well. Look up keyword DISTINCT as a way to eliminate duplicates using a SELECT query or sub-query. Something similar to:

INSERT INTO tblSupplier( Supplier) SELECT DISTINCT Supplier FROM tblAmazon WHERE Supplier NOT IN ( SELECT Supplier FROM tblSupplier ) ;

The other thing I would watch out for is that Item is a keyword if you happen to be playing around with Excel using Access automation. It is not usually a good idea to have a field or variable that matches a keyword because things tend to go "bang, zap, boom" when that happens.

 

[tmguru]

Of course you're right on the column names not needing to be the same. I remembered that right after posting. The "sort of" referred to my issue with RI. The lookups in the tables were created by the Analyze Table tool when I imported the initial Excel spreadsheet. I used the default table split recommendations and the query the tool created. I'll follow your other suggestions except that there will be normal duplicates in most tables. The Analyze Table tool created some tables that look like the attached Supplier Crosstab view.png (with one 'Parent' row for each unique Supplier and then subsequent 'child' rows for each entry. My data entry form for a record at a time works perfectly. Obviously I'm trying to solve the bulk entry issue. I'm far from opposed to re-doing my database (again) if there's a better way.

I also attached some .png's with the relationships and an example table in design view. I'm wide open to suggestions!