How to read a file using ADO

[kstv]

How do I read a *.csv file using ADO so I can import it into a table.

Thanks,

Steve

[john_murphy]

You woudn't read the file using ado here is an example of what you want (I run a stored procedure to load it but you could just run the sql

lFileNum = FreeFile()
Open txtFileName.Text For Input As #lFileNum

Do Until EOF(lFileNum)
Line Input #lFileNum, sRecord
lRow = lRow + 1

sValues = Split(sRecord, "|")
sKitPartNo = sValues(0)
sStockroomTransmitAs = sValues(1)
sDescription = sValues(2)

.Clear
.AddParameter "@vkit_part_no", adParamInput, adVarChar, 20, sKitPartNo
.AddParameter "@vdescription", adParamInput, adVarChar, 100, Trim(sDescription)
.AddParameter "@vstockroom_transmit_as", adParamInput, adVarChar, 3, Trim(sStockroomTransmitAs)

If Not (.ExecuteSP("spm_import_kit", adoImport)) Then
Screen.MousePointer = vbDefault
MsgBox "An error occurred importing the Kits." & vbCrLf & gobjSQL.LastError, vbCritical
GoTo end_cmdImport_Click
End If
Loop

Close #lFileNum

[brucevde]

To open a CSV file using ADO you can use the Jet oledb provider. You need to set some extended properties. Here is a sample.

Dim objDB As ADODB.Connection
Dim objRS As ADODB.Recordset
    
Set objDB = New ADODB.Connection
    
objDB.Open "provider=microsoft.jet.oledb.4.0;data source=C:\Projects;" & _ 
"Extended Properties='text;HDR=NO;FMT=Delimited'"
    
Set objRS = New ADODB.Recordset
 
objRS.Open "Select * From Testing.txt", objDB, adOpenStatic, adReadOnly, adCmdText
 
Do
   '... Processing
   objRS.MoveNext
Loop Until objRS.Eof
 
 
objRS.Close
objDB.Close
Set objDB = Nothing

HOWTO: Open Delimited Text Files Using the Jet Provider's Text IIsam

If you are interested - to open fixed length files you need to indicate the fields via a schema ini file. How to Create a Schema.ini File Programmatically

[roglopes]

How I use WHERE when I read a file using ADO?
My fields are separeted with ;
Thanks and sorry about my bad engish.