Import Append

[sdc1234]

Greetings, I am trying to append records that do not exist already in my database: ex: I have 2013 files and 2014 files, rather than purge and append both files I would like to just append the records that does not exist.. any help would be appreciated..




v/r
novice

[June7]

Are these files Access databases? Does each have tables that are related by autonumber primary key? Merging tables with autonumber related records is not a simple exercise.

Build a find unmatched query to discover records that are in one table but not other.

[sdc1234]

unfortunately they are excel files that I am importing each week... instead of putting the records in one file which in my mind would be a whole lot easier, they decided to go with 2 files and I already have the 2014 uploaded and I need to upload 2013 files now instead of purging both files they want me to just append the new records that do not exist in the 2014 load, without massaging the raw data.

[ranman256]

I usu save my excel file (to import) to the same filename every time (import.xls)
This xls is linked to the db as tbl: xlImport
The macro to import appends the data from the xlImport tbl to the data table via the action: TRANSFERSPREADSHEET
This way the macro imports the same file everytime, only the data in the XL file changes. Save excel file, run macro, done!

[June7]

The 2014 file could have records that were also in the 2013 file but not necessarily all of them? If there is no overlap of records, just import the 2013 file.

Can you set link to the 2013 file? Then do find unmatched query.

[sdc1234]

The 2014 file could have records that were also in the 2013 file but not necessarily all of them? If there is no overlap of records, just import the 2013 file.

Can you set link to the 2013 file? Then do find unmatched query.

hey guys thanks for the input, June, i have a question, the 2013 version is different than the 2014 version does this mean I am going to have to make 2 seperate tables, it really bugs me that they change the version every other week, they do not understand data integrity and consistancy....

[June7]

You mean 'they' keep changing the column headers in Excel? Who are 'they'?

Names don't have to be the same but it means you will have to reconstruct the queries every time you need to run this process.

[sdc1234]

yes they keep on changing and adding headers to excel file: example: 2013 has 9 headers, last week 2014 had 12 headers now today it has 14, these are people in from the other coast where I am getting my input from...

[sdc1234]

Is there a way to set my headers up when linking to an excel spreadsheet, when I link the file the headers come up as F1, F2, etc with the actual headers below it...

v/r

[June7]

I think there is option in the import/link wizard to specify the Excel does not have a header row. Try it - what happens?