eddiespics
01-20-2011, 09:28 AM
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!
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!