Importing from Exel

[Geoffk]

Hi All

I import data from exel 2000 into access 2000 how can I remove duplicate and unwanted data (ie Blank rows)

I recieve my daily work via an e-mailed exel spreadsheat which is then transposed before being exported to access.

I have allowed for upto 12 customers per day in my transpose macro.

The problems
1. If I get routed 5 customers I am left with 7 blank rows when I import into access.
2. If a customer is for example is not home and I am routed the same customer at a latter day, when I call up their info in my WIP form I normally get the first date the customer was routed this records the WIP at the wrong date therefore upsetting searches by date.

How can I remove the blank rows.
How can I sort and remove earlier routed customers leaving the last date.

Thanks
Geoffk

 

[llkhoutx]

Use a query to delete the blank rows.

 

[The_Doc_Man]

Import your spreadsheet into a temporary table. Build a query to delete the rows you don't want to keep. It is OK if you need more than one to get the job done. When done, you should have a temporary table that contains only what you wanted to see. Now one more query - either Append or Insert or Make Table as appropriate to your situation. Whichever one it is, use it to put your desired data - and nothing else - into your "live" table.

 

[Bat17]

tweak your Transpose macro in xl to delete the blank rows before import

Peter

 

[Geoffk]

Thanks for your replies

I am fairly new to MS-Access and have not worked with queries as yet.

I can get the query to delete the blank lines using delete query then import into my live table with an append query which seems to be working fine.

How can I delete rows from my temporary table to avoid duplicating data, or am I missing a step.

Is the method suggested by Bat17 a better solution, if so how, I know less about exel automation than I do about access.

Thanks for your help
Geoffk

 

[llkhoutx]

Use a query to delete the blank rows in any table.

 

[Geoffk]

Thanks All for all your help I seem to have this working

Geoffk