Reading text file using ADO 2

[protechdm]

Hello,

I am using ADO ( Microsoft Text Driver ) to load a text based file into a recordset. The file, when given a TXT extension works fine. But when I change it to, say, CPI it will not work. It looks as if you can only read from a file with either TXT or CSV as the extension. Is this correct?

 

[CCLINT]

One of these should work. Just change the file name and path:

'============================method 1=================================

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & PathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
rs.Open "select * from TextFile.txt", _
cn, adOpenStatic, adLockReadOnly, adCmdText
'============================method 2=================================

'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & PathtoMDB & "Northwind.mdb"
'rs.Open "SELECT * FROM [Text;Database=" & PathtoTextFile & ";" & _
"HDR=YES;FMT=Delimited].[textfile.txt]", _
cn, adOpenStatic, adLockReadOnly, adCmdText
'=====================================================================
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[protechdm]

Thanks for the reply. Unfortunately I've tried this and still get the same error message :-

Run-time error '-2147217911 (80040e09)'
Cannot update. Database or object is read only.

Strange really - if I change the file extension to .txt goes through fine!!

 

[CCLINT]

Hmmm. I will check into it for you..... [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

Ok. This took a while.
I usually use a schema.ini and copy the text file to another name so I do not have to change the schema.ini all the time, and do not have to touch the original file any further.

You will need to either change the file extension programically to txt,csv,tab,asc,htm,html

or, change the registry entry:

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions

by adding the desired extension.

Microsoft does this for security reasons, which doesn't really make any sense, because anyone can make these changes.

I'm trying to find a Jet provider Extended Property setting for this, but I have a feeling there isn't one.....
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

Well, I haven't found one yet.


Anyone else? [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[protechdm]

Thanks for the help. Much appreciated.

 

[458sDad]

I have another question on this subject. I am reading a TEXT file and have a schema.ini file. When I execute my code:
Public Function Read_Text_File()
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim objConnection, objRecordset, strPathtoTextFile, Wscript
'*
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
'*
strPathtoTextFile = "C:\1\"
'*
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=No;FMT=FixedLength"""
'*
objRecordset.Open "SELECT * FROM test.txt", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Dim intL As Integer
objRecordset.MoveLast
intL = objRecordset.RecordCount
objRecordset.MoveFirst
'*
Do Until objRecordset.EOF
Debug.Print ; "Name:" & objRecordset.Fields.Item("Name"); " Serial:" & objRecordset.Fields.Item("Serial"); " ID:" & objRecordset.Fields.Item("ID")
objRecordset.MoveNext
Loop
'*
End Function
....
It always skips the first record. It's like the conenction thinks the first row is a header row even though I have specified that a header row doesn't exist. My Schema.ini file looks like:
[Test.txt]
Format=FixedLength

Col1=Data1 Text Width 3
Col2=Serial Text Width 6
Col3=Data2 Text Width 37
Col4=TestType Text Width 1
Col5=Data3 Text Width 1
Col6=Name Text Width 5
Col7=Junk Text Width 1
Col8=ID Text Width 9
Col9=Data4 Text Width 8
Col10=Board Text Width 2
Col11=Data5 Text Width 1
Col12=Disc Text Width 1
Col13=Data6 Text Width 148

Any help would be appreciated.

Thanks.