Why am I getting blank records in my Access Table...??

I am importing data from an Excel spreadsheet into an Access table daily.  Before I import the records each day, I first run a delete query in Access to rid the table of yesterday's records.  When I import the new Excel data into the table it is creating several hundered rows of blank records, but it doesn't seem to do it everytime.  This makes me think it has something to do with the Excel data I am creating every morning to update the table.

In Excel, I working with a spreadsheet that identifies if a task has been "done" or is "pending".
I only want to take the "pending" records into the Access table.   In general, every day the number of "done" tasks goes up, and the number fo "pending" tasks goes down.  (Anything to do with the blank records?)  I filter the spreadsheet on a column for those identified as "pending" and then I copy those filtered records and paste special into another spreadsheet tab.  Then I go to Access and import the "paste special" tab that has the "pending' records on it.  But when I check the import I find that even though there were only 12000 records that should have been imported, I am getting 12484.  

All this is happening in Office 2000.  Any thoughts?  - Tom
LVL 7
tomfarrarAsked:
Who is Participating?
 
Jillyn_DConnect With a Mentor Commented:
tomfarrar,

It would be much simpler to run a delete query in Access to delete any blank recorrds once the information has been imported.  You could add it to the import routine, then it would be automated and wouldn't add another step for the new person.

You could also write a macro on the Excel side to take care of it.

~Jillyn
0
 
ClothahumpConnect With a Mentor Commented:
One of the things that has helped me when importing from Excel -

After creating the spreadsheet and loading the data, move to the first blank line at the end.  Click on the row selector number and drag down until you have a couple of thousand blank rows highlighted, then delete them.

That will usually prevent blank records from being imported.
0
 
Jillyn_DCommented:
Hi tomfarrar,

Another way to do this is to hit <Ctrl>+<End>.  This will take you to the last row Excel thinks exists.  Then use Clothahump's method to highlight all rows from there to the last valid row in your worksheet, and delete them!

Good luck!
~Jillyn
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
tomfarrarAuthor Commented:
Clothahump - Well, that certainly could be the solution.  It appears you have had the same problem.  Do you know why it might be happening?  That is what I would like to first know, and then what can be done about it (one of which is your suggestion)....
0
 
Jillyn_DCommented:
tomfarrar,

The most common cause for this that I've come accross is when a user selects columns and formats them.  However, it also happens when information is deleted from rows, but the rows themselves are not deleted.  Excel recognizes these formated or newly empty rows as valid members of the worksheet.

~Jillyn
0
 
tomfarrarAuthor Commented:
Well we seem to be getting somewhere on this.  I just noticed that the number of blank rows that are created in the Access table appears to be the difference between the number of records yesterday and the number of records today.  For instance, yesterday I had 12800 valid records in Access (that I used a delete query to get rid of) and I imported 12200 records today from Excel.  The number of blank rows was 600.  I use the same spreadsheet tab in Excel to bring each days records in after first deleting the old and then pasting in the new.  So you are perhaps right about what you are saying, but could the problem be in Access's delete query.  If it is an Excel problem, is there anything I can do on the front end, to eliminate going through the steps you've suggested?  I am going to turn this over to another user and I am trying to keep the number of steps to a minimum.

Thanks to both of you for helping me on this.  - Tom
0
 
tomfarrarAuthor Commented:
Good answer, Jillyn.  Clothahump gets an assist.  I appreciate both of your responses to my issue.  Thanks again.  - Tom
0
 
Jillyn_DCommented:
Glad to be of assistance :)

Thanks for the points, Tom!

~Jillyn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.