Tons of blank records from excel import

[Jedi420]

Hello,

In my database, I sometimes import excel data to use as records. However, whenever I use the DoCmd.TransferSpreadsheet function to import my data, I get tons (about 136) of blank records in the destination table. I check the spreadsheet and nothing seems out of the ordinary. Sometimes, only 3 rows are filled with data in the spreadsheet, but whenever I import that spreadsheet, I get hundreds of records, all but the 2 rows of data (1 row was used for field names) are blank! Does anyone have any ideas as to why this may be happening. I could not do a search through the threads since this site seems to be loading when it wants to this week. Any ideas or suggestions would be greatly appreciated. Thnx!

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[ian0001]

This happens when the transfer grabs all columns from your worksheet, blank ones too, all the way to az or what ever.

Best learn how to code it - you've so much more control.
There's a bit of code in "Copying Data from Excel to Access"
that will head you in the right direction.

 

[Jedi420]

See, that's the thing though. I'm not getting extra columns ... Im getting extra rows. I've read Microsoft's article about the columns (

http://support.microsoft.com/default.aspx?scid=kb;en-us;208512)

but I do not believe this applies to extra rows of data. Perhaps I am wrong. I've read the thread you referred me too ... I've actually spoken with Jane about this (she hasnt solved her range issue as of yet). When I import, I recieve no extra columns. In all the other posts I've read so far, people have only had the problem of extra columns. Still stumped ... any help or advice would be greatly appreciated.

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[MichaelRed]

I have seen -but never even attempted to resolve it. It 'looks loke' to me, that the spreadsheet had some records (rows) deleted in some previous instantation / use but when it gets saved, Excel only knows about the CELLS which were deleted, not the actual rows. I observed this in doing a VERY short term assignmnet where there was a "template" spreadsheet generated specifically to facillitate the 'collection' of hte info for import into the DB, (Field names in the header, specific TYPEs assigned to the rows, ...). It further appeard (to me) that the "data entry' users (with the spreadsheet) did not "copy th template for each use, but used it "as is",deleted the previous rows / records, and re-entered new data for each occassion of a session. then e-mailed the spreadsheet for accumulation in the db. Somewhat unlike your experience, each 'import' procedure returned a different (but ever increasing) number of the "Blank" records.

While I made no attempt to correct the root problem, I did generate a procedure which did a V&V on the records imported (via a temp table) and discarded ones with NO fileds having values as a preliminary step in the procedure.

When I inquired re 'fixing' the source problem, the 'answer' was " ... ain't broke ... don't fix ... " - which was only a small part of the 'dialog'. As far as I could tell from hte narrow persprctive, the organization did not particularly even want to 'fix' anything.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Jedi420]

Yeah, that's exactly what I do now to circumvent the problem: Just dump the imported SS into a table and pick through it to get the records I want. The only reason I'm concerned about this is that I have a theory that this blank record problem may be the source of another problem I have (thread701-616433). Anyways, thnx for the info ... perhaps this thing will get solved soon.

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[ALEKSJAY]

I have had this problem. If you go to Excel and delete the blanck rows (Not just selecting the blank cells) I was able to solve this problem.
Hope it helps

 

[Jedi420]

How do I delete blank rows? Just select them and hit 'delete'?

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[ALEKSJAY]

What I do is to select the whole row (by clicking where the row number is) and hit delete.

 

[MichaelRed]

O.K. ... but ... this is not a 'programatic' soloution. It requires ye olde manuale interventionee ... and getting hte users who input the info to do it 'your way' is -at best problemetatic. At least importing to a temp table and dleeting hte blanks doen't require starting an additional app, and it could actually be done programatically in Ms. A. - just write a delete query for all records with soe(required?) field not having a value.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Jedi420]

Well, the strange this is that it's Access that's doing the exporting. You export to a spreadsheet so people can do stuff at home ... they come back and import the spreadsheet (pity on me for all the error checking I have to do ). I've analyzed the spreadsheet that Access makes and there doesnt appear to be anything wrong with it. I mean, how can you tell a blank row from a row that just has no data in it? And actually, the other problem I had has been solved so this isnt a critical issue, but it still annoys me! Thnx for responding. (^_^)


-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[ALEKSJAY]

It's when the users do stuff at home that you are having those blank rows. Sometimes, just scrolling in excel creates those rows.