Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO and text file
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO and text file
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. "Howie" wrote in message ... 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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
How to tell number of spaces between values in saved text file fromthe original xls file | Excel Discussion (Misc queries) | |||
How do I convert excel file into ASCII text file with alignment? | Excel Discussion (Misc queries) | |||
Open delimited text file to excel without changing data in that file | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |