Import Large Excel file into Access

[jojo]

I have an Access database with related tables and lookups.
The data for the tables is in a large Excel file with about 100 columns and 30,000+ records, which will be imported into one flat Access table.
This table has to be imported into smaller related tables.
Each record contains all the data on one person. A record maybe 2,000 rows down may be on the same person, but is a different record with different dates and figures. The "same person" part of the record wil be in one table and the rest of it will be in 3 or 4 related tables.
How will the related records match up if there is no matching key field in the original table?
Is this doable and if so, how? Access 2010 Windows 7
Thank you in advance, and for all the useful info from this great forum!
Jojo

 

[CJ_London]

The easiest thing to do is to have several passes through the spreadsheet, ensuring each pass is has columns identifiable as a uniqueID

For example:

• 1st pass - use a group by (or select distinct) on those columns relating to employee record (unique column - employee id)
• 2nd pass - use a group by on those columns relating to employee role (unique columns - EmployeeID and RoleID)
• 3rd pass - use a group by on those columns relating to performance (unique columns - EmployeeID and KPIID)

etc

Alternatively you can pivot the spreadsheet to create a number of different views as above.

If you have problems with (for example) employee name changing although their ID remains the same thereby creating duplicate records, you have a number of choices depending on what you want to do.

• Allow the duplicates and have an archive or history file
• Use first or last on the name

 

[jojo]

Wow!! thank you so much cj_london for your quick reply. When you say "passes through the spreadsheet" do you mean the XL spreadsheet or the Acc table into wihich the spreadsheet will be imported?
I see this well take some time to implement and fine tune.
Thank you again.'
jojo

 

[CJ_London]

either, but given the volume of data probaby better to do it on the table rather than a linked spreadsheet.

Another tip, put indexes on each of the columns in your table which are 'unique' - will work a lot faster

 

[jojo]

Thank you for that also, that was my next question!
I have other parts of this project that need to be done immediately, and this import has to be done later on, but was just thinking about how to go about it when I do get to it, so it might be a couple weeks but I will let you know!
Thanks
jojo

 

[jojo]

The easiest thing to do is to have several passes through the spreadsheet, ensuring each pass is has columns identifiable as a uniqueID


For example:

• 1st pass - use a group by (or select distinct) on those columns relating to employee record (unique column - employee id)
• 2nd pass - use a group by on those columns relating to employee role (unique columns - EmployeeID and RoleID)
• 3rd pass - use a group by on those columns relating to performance (unique columns - EmployeeID and KPIID)

etc

I am not sure what you mean by 1st pass, 2nd pass. Do you mean add columns to the newly created Acc table such as EmployeeId where EmployeeID matches the name in the main (parent) Employee table? If i add this column, what will it be populated with so that the right record will match the correct ID? i hope I sound clesr.

 

[CJ_London]

1st pass would be a query based on your imported table to get employye details
2nd pass would be another query based on your imported table to get employee role details

etc

You need to use a top down approach to get the data you need, so certainly, once you have extracted the employee details to the employee table you can then use this table to reference the imported table but there should be no need to add extra columns to the import table