Incorrect Field Types in ADODB Recordsets

[eddiespics]

I've been having this issue for months, and have done extensive research, only to find nothing. I am using VBA to import data from a closed CSV file, and for the most part, it works fine. The one issue I am running into, is there are a couple of columns which can contain both integers and strings. The field object for this column in my recordset will show a type of "adInteger"


Dim RS As ADODB.Recordset
Dim cN As ADODB.Connection
Dim sql
Dim RowCnt, FieldCnt, StartFieldCnt As Integer
Dim folder_path as string, fileName as string


folder_path = [place folder path here]
fileName = [place file name here]

'Sets up connection to CSV file
Set cN = New ADODB.Connection
cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=folder_path;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";" & _
"Persist Security Info=False"

cN.Open

Set RS = New ADODB.Recordset
sql = "SELECT * From " filename
RS.ActiveConnection = cN
RS.Source = sql

RS.Open


This will grab all the data just fine, except any strings in the column containing integers. As an example, the CSV is

OrganizationID,OrganizationName,ProgramID,ProgramName,
31,"Dummy Data, Inc.",245, APR - HPRP Combined
Agency1,TestAgency,Program1,TestProgram


So "Agency1" and "Program1" do not get brought into the recordset.

Any help on this would be greatly appreciated!

[Kenneth Hobs]

There is a registry setting that can be used to change how many lines are read before assigning a field type in a CSV. If you set it to one, that first one will govern. In that case, I would make sure that the first records field of varied type was a string.

There may be other methods that would work better for you.

Post example file(s) if you need more detailed help.

[Tinbendr]

In this article (http://msdn.microsoft.com/en-us/library/ms974559.aspx), the author describes defining your own header row. Maybe you could do that.

It's about half way down, under "I Dozed Off; What Did You Say Header Rows Were?"

David

[eddiespics]

Thank you both, as this was very helpful. It appears the best route for my case is to write the Schema.ini file for this, as the files I need to import have varying amounts of rows, so unfortunately increasing the number of rows to check won't solve every case.

Does anyone know of a way to set up the columns and headers directly in the code, without the Schema.ini file?

[Kenneth Hobs]

This link might give you an idea for setup. http://www.devx.com/tips/Tip/12566

This one shows how to use code to generate it. http://support.microsoft.com/kb/155512

It should not be difficult now that you have examples. IF you post a very simple csv, we can show you how in detail. We would use standard text file read/write methods and ADO.

[eddiespics]

Kenneth, Thank you very much. I think writing a custom Schema.ini file is the best way to go, as the files can have different names depending on the computer it is being used on. That Microsoft article you linked to spells everything out perfectly.