Omitting Rows from Excel Import

[DampFloor]

I am attempting to create a database which will import quote sheets which are received in excel sheets from suppliers. Each one of these excel sheets are categorized i.e. Dairy, Protein etc. The issue is that some of these sheets contain subcategories i.e. Dairy will have the subcategories of yogurt and cheese. The sub categories are separated by headers within the same table. is it possible to omit specific rows so that the quote sheets are imported without the "header rows"?

Thanks,

Damp

 

[pbaldy]

Probably. How are you importing? If you link to the spreadsheet and then append, you may be able to exclude them via criteria. Same if you import into a temp table and then into the real table. Worst case you import line-by-line, but you'd still need a way to identify rows that should/should not be imported.

 

[DampFloor]

My goal is that it all quote sheets would be in a single folder. When a new quote sheet is received from a supplier, it would be saved over the quote sheet which is currently in the folder. So when the database is opened, the database would automatically import all sheets which are in the folder into a table for each sheet. This data would then be used in various ways. I would also need to a append a date to each sheet so there can be a function which prevents importing duplicate data.

So in short, I am not sure about how i should be importing the sheets. It should also be noted that i am very new to access and this is a project for school....

 

[isladogs]

Suggest you do this in 3 stages

1. Import the entire Excel file to an intermediate or 'buffer' table
2. Copy the records (rows) you want to your final table
Suitable code here will prevent duplicates
3. Empty (or delete) your buffer table as you no longer need it.

 

[DampFloor]

Hi Ridders,

Is this a process that can be performed automatically? I am not entirely sure how i "copy" rows into another table. and then how do i omit the rows i do not need? is there code I can enter which will filter rows which contain certain text? or no text at all? not sure how to approach the elimination of duplicates either.

 

[DampFloor]

nice got it to work with all sheets into their own table with only the records i require. I am still a little unsure how i will prevent duplication of data being entered. Should I attach a date to for each time a record is appended?

 

[isladogs]

Look at the unmatched query wizard for how to do this

The attached screenshot shows an example of the idea