More OleDB Text/Excel Driver Woes...
So I have a method which I can call with just a filename and some other meta data and it will go out, open that file and return back a recordset. Sounds easy, right?
During this import process you actually can use OLEDB, and ADO to do the work for you. New up an Adodb.Connection, passing the properly formatted connection string, and then select * from your datasource.
So what do I do with this connection object once I have selected all of the data out?
Well I would just love to disconnect the recordset, the same old fashion that I have been doing for YEARS in a typical environment (non-text drivers). What you simply need to do is set the "CursorLocation" of the recordset to adUseClient, PRIOR to actually opening/populating it with data. And then once its all loaded up, you can Set the "ActiveConnection" to nothing, which results in our disconnected recordset. From there, make sure to .Close() and set your connection object to nothing (proper housekeeping, of course).
Read more on the different types of Cursors
So, when I change my cursor to adUseClient I see the error:
"Data provider or other service returned an E_FAIL status error"
A quick google on it turns up the real reason (thank god it is a simple solution) for this error. It has to do with the data types of the source and how OLEDB and ADO maps that into your recordset. For example, lets say your selecting from an excel document and you had a phone number field, and the first few values were seen as:"6043359632" the resulting recordset will treat that as a numeric. But what happens when further down the list someone used the format: "604-335-5639", that simply will not map easily into any sort of a numeric field, so the result is the annoying E_FAIL status error when using a client sided cursor.
Specifically what I was doing, was mapping the field names in my source data file to the very specific list of fields in my destination, like this:
select '' as [Notes], [CityName] as [City], [AreaCode] + [Phone] as [Phone]
Notice that i used a "+" to concantenate those two fields. My bad. It would have attempted to add those fields numerically and treat the final field as a numeric. I switched it over to use "&" to do the concantenation and all is well.
Luckily this specific project is coming to a close soon enough. I have had enough.
1 Comment
Comments have been disabled for this content.
Don Demsak said
I know that it is a little late for you, but have you seen my (old) post on Converting Excel Worksheets and CSV Files to Datasets using ADO.Net. It is similar to your method, just a little more generic.