PDA

View Full Version : Import CSV using ADO - Missing negative values



Atravis
08-13-2010, 08:54 AM
Hi,

I am trying to import the data from closed CSV files in an excel spreadsheet using an ADO connection. If there are positive and negative numbers in the columns being imported the negative values are missing.

I need these numbers to be included, and any help on this would be appreciated. Please find the code below.


'Pulls Data from CSV to Data sheet
Sub Open_Sort_CSV(CSV_Dir, CSV_name)
'set record set variables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
'ser connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
'oopen connection (headers,Delimited style,mixed data taken as text(not sure imex works))
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CSV_Dir & ";" & _
"Extended Properties=""TEXT;HDR=Yes;FMT=Delimited;IMEX=1"""
'get data from csv
objRecordset.Open "SELECT * FROM " & CSV_name, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
'Loop across the fields
With objRecordset
For a& = 0 To .Fields.Count - 1
' Add field names to data sheet
ThisWorkbook.Worksheets("Data").Cells(1, 1).Offset(0, a&).Value = .Fields(a&).Name
Next a&
End With

'this errors for no good reason so stop any errors
On Error Resume Next

'copy data into worksheet under headers
ThisWorkbook.Worksheets("Data").Cells(2, 1).CopyFromRecordset objRecordset

'end connection and recordset
Set objConnection = Nothing
Set objRecordset = Nothing

End Sub

Kenneth Hobs
08-13-2010, 12:13 PM
Welcome to the forum! Please use the VBA code tags when posting code.

Please attach a sample CSV file for us to test. You could zip it and your XLS for us to test.

Atravis
08-13-2010, 04:21 PM
Unfortunately the files with which I am developing the code contain sensitive data.

The files we are trying to import data from are downloaded from an online source.

I intended to create a generic file following the format of those which we use, however I have found that on opening the downloaded CSV file and then saving it (regardless of whether changes are made or not) the issue is resolved.

Could the file have different properties depending on whether it is saved directly from the website or whether it is opened and then saved?