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
> ADODB Recordset data problem?    
 
   
LuckyJim
post Jun 20 2007, 02:01 PM
Post#1



Posts: 11
Joined: 16-January 07



Hi there:
I'm transfering an Excel spreadsheet into an Access table. I thought I'd copy the spreadsheet to a recordset and then transfer this into the table.
The first few pieces of data in one of the fields I'm transfering are: 1, 1, 2, 2, 3, 3. These show up in the Access table no problem. The next couple are: 1B, 1C. These also show up. For the remaining 300 + records, if the data is made up of letters alone or letters and numbers (eg 1B), it transfers fine. If it is a number alone, I get a blank.
I think I've established that the problems is in the recordset and where these numbers should be I just have nulls. All the other fields transfer fine. Anyone have any ideas about what's going on? Any workarounds? Data set's not particularly big and never will be - perhaps I shouldn't be using a recordset?
Thanks!
James
Go to the top of the page
 
fkegley
post Jun 20 2007, 02:29 PM
Post#2


UtterAccess VIP
Posts: 23,746
Joined: 13-January 05
From: Mississippi


That is peculiar, isn't it? Perhaps there are some hidden characters in the cells that are not transferring correctly?
You might try this though, put all the cells with number-letter combinations at the TOP of the list, then try importing again.
Go to the top of the page
 
datAdrenaline
post Jun 20 2007, 03:01 PM
Post#3


UtterAccess Editor
Posts: 17,941
Joined: 4-December 03
From: Northern Virginia, USA


Can we see your code you use to open the recordset, then write to the table? That would be most helpful. Also, your destination table configuration would be good to know as well.
Go to the top of the page
 
LuckyJim
post Jun 20 2007, 03:09 PM
Post#4



Posts: 11
Joined: 16-January 07



Hi Brent, Hi Frank,
Thanks for your interest. Unfortunately I'm in the UK and home from work (where this problem raised its ugly head). I'll probably spend a little more time on this again tomorrow, but I'll post the code tomorrow night if I still haven't sorted it out.
Cheers!
James
Go to the top of the page
 
ByteMyzer
post Jun 20 2007, 04:59 PM
Post#5



Posts: 193
Joined: 26-March 04
From: California


The issue may not necessarily be with your code. The issue may well be with the way that the data is stored in the Excel file.
If the data is a letter-only or a letter-number combination, Excel stores the data in the cell as Text by default.
If it is a number-only, however, Excel stores the data in the cell as a numeric value (NOT Text) by default.
This can cause problems in enumerating an ADODB recordset of an Excel file, if the recordset field definition is set for a text entry. Your best bet is to:
* copy the trouble-column cells into Notepad
* clear the column cells from Excel
* reformat the cells as text
* copy the text data from Notepad
* paste the text data into the Excel column cells
This should repopulate the cells in the column with ALL values as Text data.
See if this solution works for you.
Go to the top of the page
 
LuckyJim
post Jun 21 2007, 01:10 PM
Post#6



Posts: 11
Joined: 16-January 07



Hi all:
solved this problem today - the solution was to change the connection string to something like this:
CODE
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

My previous connection string did not include the IMEX=1 extended properties. On the MSDN forum where I found the solution, it said that setting the IMEX=1 has the effect of telling "the driver to always read "intermixed" data columns as text". That's the effect it had for me; it's actually a little more complicated than that, as you can read here:
PRB: Excel Values Returned as NULL Using DAO OpenRecordset
Thanks for all your suggestions - UA rules!
James
Go to the top of the page
 
fkegley
post Jun 21 2007, 05:41 PM
Post#7


UtterAccess VIP
Posts: 23,746
Joined: 13-January 05
From: Mississippi


James, I had never heard of IMEX before. Thanks for posting the URL, I have bookmarked it for future reference.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 04:54 AM