UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Transferspreadsheet Import - Missing Records, Access 2016    
 
   
daschrislydon
post Oct 18 2017, 02:46 PM
Post#1



Posts: 78
Joined: 28-August 15



Hello there

I am doing a
CODE
DoCmd.TransferSpreadsheet acImport


There are 143 rows of data (row 1 is header, rows 2-144 are data).
The table I'm importing to in Access has the right number of fields and the field types are correct. The data being imported isn't too big for the fields.

The import brings in 32 of the records. The error says "0 records were deleted, 0 records were lost due to key violations". There doesn't seem to be a table of "import errors" as I might expect...

I thought maybe it was "blank" cells in Excel that aren't really blank (none of the fields in my import table allow zero length strings) so I made sure any blanks cells in Excel are really empty.

Very puzzling!
I'm sure it's something solvable but I just can't fathom it...

Any advice/pointers gratefully received!

Thanks
Chris
Go to the top of the page
 
doctor9
post Oct 18 2017, 02:58 PM
Post#2


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Chris,

Have you looked at rows 32, 33 & 34 of your worksheet? Are the 32 records that get imported from the first 32 rows of data in the worksheet? If not, can you spot any commonalities between the 32 that made it that perhaps the other rows do not share?

Good luck,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
theDBguy
post Oct 18 2017, 02:59 PM
Post#3


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi Chris,

Try doing the import manually in case it might give you some clues if there are any problems with the specific file you're trying to import using code.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
daschrislydon
post Oct 18 2017, 04:11 PM
Post#4



Posts: 78
Joined: 28-August 15



Hi there

Unfortunately, the records it does import seem to be scattered through the spreadsheet...

I have tried looking at the good rows compared to the bad ones to see what's what, but can't see anything...

Thanks
Go to the top of the page
 
daschrislydon
post Oct 18 2017, 04:12 PM
Post#5



Posts: 78
Joined: 28-August 15



Oh and yes I tried a manual import in case Access was going to give me some more details on what's wrong with the other records but no luck......

Frustrating!
Go to the top of the page
 
daschrislydon
post Oct 18 2017, 04:15 PM
Post#6



Posts: 78
Joined: 28-August 15



I'm not deleting one column from the right side of the spreadsheet and trying an import after each deletion... see if I can spot which column(s) is/are the problem!

(Annoyingly, if it's multiple columns in different areas, this method won't reallllllllly solve it. But it might be a start!)
Go to the top of the page
 
daschrislydon
post Oct 18 2017, 04:18 PM
Post#7



Posts: 78
Joined: 28-August 15



And it's definitely something with the DATA.

(I copied the whole spreadsheet to a blank spreadsheet and did Paste Special -> values. So it's not a formatting thing. [Except I had to set the Date field to be a Date number type...])
Go to the top of the page
 
daschrislydon
post Oct 18 2017, 04:21 PM
Post#8



Posts: 78
Joined: 28-August 15



Ah ha!

Deleting the data from columns which store phone numbers (i.e. numbers, but set as Text number type) let ALL the records in.

So something is up with them!

I wonder what it is...

Continues to investigate!
Go to the top of the page
 
daschrislydon
post Oct 18 2017, 05:02 PM
Post#9



Posts: 78
Joined: 28-August 15



OK, so yes - something about "empty" cells in Excel not reallllly being empty.
My code wasn't "emptying" them properly. But now is.
and the import works!
Go to the top of the page
 
doctor9
post Oct 19 2017, 08:19 AM
Post#10


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Chris,

Glad you got it working! theDBguy and I were glad to nudge you in the right direction.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
gemmathehusky
post Oct 20 2017, 10:26 AM
Post#11


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


if your dbs table has a field that requires a value, but the text-numeric conversion can't handle the field then I presume the whole row has to be dropped, rather than just the individual row/field value.
Alternatively, if you had a unique index on the field, then multiple zls's would get rejected, which again might reject the whole row, but multiple nulls would not. So maybe blanking the data in the spreadsheet changes them to nulls.

Do either of this ideas make sense?

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 03:18 AM