Importing Excel Data

[uaguy3005]

I have a database I use for tracking quality concerns. Currently data is captured in a corporate database and I export the data to excel and copy into my database. The data has many repeated values and when I import, I'd like to import into separate tables in my database. How would I go about doing that? Example data would be:



ImportData
Fields: Cell, Part Number, Failure Mode, Tag, Value, Shift

tblScrap
Fields: tblParts, tblSource, Failure Mode, Tag, Value, Shift

tblParts
Fields: Part Number, Value, tblSource

tblSource
Fields: Cell, Zone

[ranman256]

if you are using different excel files to import to the various tables then,
create an import macro that has
all the transfer commands (similiar to those below, IMPORTEXPORTSPREADSHEET)
or
a button click and these vb commands:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblScrap", Filename1, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblParts", Filename2, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblSource", Filename3, True

---these will import the excel file to the tables

[uaguy3005]

The import data is all on one excel sheet in the same table. Just not sure what the process is to "explode" that table into the separate access tables.

[ranman256]

Then you need this method:
Save the excel file to a generic name, say ImportFile.xls
Attach (link) this file into access as a linked table, say: tblFile2Import (done just this once)
Build queries needed to append the data from tblFile2Import to your target table, say: qaImportXLScrap

Build a macro to run the monthly import. mImportXL
The macro will have the quereries :
qaImportXLSCRAP
qaImportXLParts
qaImportXLSource

So every month/week,
1. save your data to the generic file: ImportFile.xls (overwriting the previous one)
2. Then run the import macro, mImportXL
Done.