ADO and text file

[Howie]

Trying to open a text file using ADO, but don't want it
delimited(comma). When using the following code, it
automatically returns comma delimited recordset. If I
change the FMT=Delimited to FMT=Fixed, then the
RecordSet.Open fails.

I've also tried opening with text driver (see below) and
get same comma-delimited recordset.

Not much ADO experience, so I'm mostly cutting and pasting
these coneection strings.

Thanks.

'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended
Properties=""text;HDR=No;FMT=Delimited"""
'Get the recordset
Set oRS = CreateObject("ADODB.RECORDSET")

'Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM " & strFilename, oConn,
adOpenStatic, adLockReadOnly, adCmdText

TEXT DRIVER OPEN

"Driver={Microsoft Text Driver (*.txt;
*.csv)};" & _
"Dbq=" & strFilePath & ";" & _
"Extensions=txt"

 

[onedaywhen]

I had a play with this and, like you, couldn't get an ADO connection
string that would open a comma delimited text file without applying
the delimiter. It seems to default to delimited whatever I try.

However, I found something interesting I'd never noticed before. I
executed a SQL query that uses joins between text files and it created
a file Schema.ini in the folder. This ini file contains column schema
info for the text files, obviously derived from the data. I tried
editing it to suit but it still opened comma delimited - no luck but
then I didn't spent much time on it.

If you are interested in investigating further I suggest you search
MSDN using the phrase 'Schema.ini'. There seems to be a few articles
that are relevant.