Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO & semicolon delimited text file?
Hello, I hope someone can help me with this issue, please.
I need to open a large semi-colon delimited text file into a recordset using ADO. Is this possible? If so, how do I make ADO aware that the file is "semi-colon" delimited? Right now, my code does not recognize this delimeter and imports all the data into the recordset as one column (NOTE: there are too many rows to open into a spreadsheet). Your example code would be most appreciated, thanks in advance. If needed, my code so far is below: Set cnText = New ADODB.Connection cnText.CursorLocation = adUseClient cnText.ConnectionString = "PROVIDER=MSDASQL;DRIVER= {MICROSOFT TEXT DRIVER (*.TXT; *.CSV)};DBQ=" & argFilePath & ";" cnText.Open strSQL = "SELECT * FROM " & argFileName & ";" Set rsText = New ADODB.Recordset rsText.MaxRecords = 0 rsText.Open Source:=strSQL, ActiveConnection:=cnText, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText Your example code would be most helpful, TIA. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO & semicolon delimited text file?
Hi raj,
I think you'll have to use a schema.ini file to specify the format of the text file you're opening. Here's a link to a similar thread that may help to get you started: http://groups.google.com/groups?hl=e...x .com&rnum=4 If you do a search on schema.ini and ado, you should find a lot of helpful information. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] raj wrote: Hello, I hope someone can help me with this issue, please. I need to open a large semi-colon delimited text file into a recordset using ADO. Is this possible? If so, how do I make ADO aware that the file is "semi-colon" delimited? Right now, my code does not recognize this delimeter and imports all the data into the recordset as one column (NOTE: there are too many rows to open into a spreadsheet). Your example code would be most appreciated, thanks in advance. If needed, my code so far is below: Set cnText = New ADODB.Connection cnText.CursorLocation = adUseClient cnText.ConnectionString = "PROVIDER=MSDASQL;DRIVER= {MICROSOFT TEXT DRIVER (*.TXT; *.CSV)};DBQ=" & argFilePath & ";" cnText.Open strSQL = "SELECT * FROM " & argFileName & ";" Set rsText = New ADODB.Recordset rsText.MaxRecords = 0 rsText.Open Source:=strSQL, ActiveConnection:=cnText, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText Your example code would be most helpful, TIA. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO & semicolon delimited text file?
Thanks Jake. As an alternative workaround, could I instead
do a search and replace inside the recordset and replace the ";" with ","? If so, do you know how to do this, and could you by chance supply an example in your post back? TIA -----Original Message----- Hi raj, I think you'll have to use a schema.ini file to specify the format of the text file you're opening. Here's a link to a similar thread that may help to get you started: http://groups.google.com/groups?hl=e...=UTF-8&oe=UTF- 8&threadm=5scsrvopmd07ka6hc413j4eccprb7766hq% 404ax.com&rnum=4 If you do a search on schema.ini and ado, you should find a lot of helpful information. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] raj wrote: Hello, I hope someone can help me with this issue, please. I need to open a large semi-colon delimited text file into a recordset using ADO. Is this possible? If so, how do I make ADO aware that the file is "semi-colon" delimited? Right now, my code does not recognize this delimeter and imports all the data into the recordset as one column (NOTE: there are too many rows to open into a spreadsheet). Your example code would be most appreciated, thanks in advance. If needed, my code so far is below: Set cnText = New ADODB.Connection cnText.CursorLocation = adUseClient cnText.ConnectionString = "PROVIDER=MSDASQL;DRIVER= {MICROSOFT TEXT DRIVER (*.TXT; *.CSV)};DBQ=" & argFilePath & ";" cnText.Open strSQL = "SELECT * FROM " & argFileName & ";" Set rsText = New ADODB.Recordset rsText.MaxRecords = 0 rsText.Open Source:=strSQL, ActiveConnection:=cnText, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText Your example code would be most helpful, TIA. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO & semicolon delimited text file?
Hi raj,
I don't think that would work too well, as your fields would be kind of meaningless. You could open the text file and read it record by record (field by field) into a recordset. Here's an example that should get you started: http://msdn.microsoft.com/library/en...datasource.asp Obviously, you don't have to create a class like this example; the general idea is to open the text file using file I/O and read it in line by line. You could use the Split function to split each line into an array of fields, then assign each of the array's elements to the fields in the record. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] raj wrote: Thanks Jake. As an alternative workaround, could I instead do a search and replace inside the recordset and replace the ";" with ","? If so, do you know how to do this, and could you by chance supply an example in your post back? TIA -----Original Message----- Hi raj, I think you'll have to use a schema.ini file to specify the format of the text file you're opening. Here's a link to a similar thread that may help to get you started: http://groups.google.com/groups?hl=e...=UTF-8&oe=UTF- 8&threadm=5scsrvopmd07ka6hc413j4eccprb7766hq% 404ax.com&rnum=4 If you do a search on schema.ini and ado, you should find a lot of helpful information. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] raj wrote: Hello, I hope someone can help me with this issue, please. I need to open a large semi-colon delimited text file into a recordset using ADO. Is this possible? If so, how do I make ADO aware that the file is "semi-colon" delimited? Right now, my code does not recognize this delimeter and imports all the data into the recordset as one column (NOTE: there are too many rows to open into a spreadsheet). Your example code would be most appreciated, thanks in advance. If needed, my code so far is below: Set cnText = New ADODB.Connection cnText.CursorLocation = adUseClient cnText.ConnectionString = "PROVIDER=MSDASQL;DRIVER= {MICROSOFT TEXT DRIVER (*.TXT; *.CSV)};DBQ=" & argFilePath & ";" cnText.Open strSQL = "SELECT * FROM " & argFileName & ";" Set rsText = New ADODB.Recordset rsText.MaxRecords = 0 rsText.Open Source:=strSQL, ActiveConnection:=cnText, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText Your example code would be most helpful, TIA. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO & semicolon delimited text file?
Raj, Jake
How about reading the file in, replacing the semi colons and creating a new file from that? You said it was large, so you might run into some limitations. Here's an example of what I had in mind. Sub TextAdo() Dim Fname As String Dim stCn As String Dim stsql As String Dim cn As ADODB.Connection Dim rs As ADODB.Recordset 'Identify semi-colon delim file Fname = "C:\Dick\ng\Jan\MySCD.txt" 'Return file name of csv file Fname = ConvertedToCsv(Fname) 'Do ado stuff stCn = "DSN=Text Files;DefaultDir=c:\Dick\ng\Jan;DriverId=27" stCn = stCn & ";MaxBufferSize=2048;PageTimeout=5" stsql = "select * from " & Fname Set cn = New ADODB.Connection cn.Open stCn Set rs = cn.Execute(stsql) rs.MoveFirst Do While Not rs.EOF Debug.Print rs.Fields(0).Value rs.MoveNext Loop rs.Close cn.Close Kill Fname End Sub Function ConvertedToCsv(Fname As String) As String Dim fso As Object Dim ts As Object Dim NewFile As String Dim FleTxt As String 'Make a new filename for the csv NewFile = Left(Fname, InStr(1, Fname, ".")) NewFile = NewFile & "csv" 'create the scripting object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(Fname).OpenAsTextStream(1, -2) 'Read in the semi-colon delim text FleTxt = ts.readall ts.Close 'Change ; to , FleTxt = Replace(FleTxt, ";", ",") 'Create a new file and write to it Set ts = fso.CreateTextFile(NewFile, True) ts.Write FleTxt 'Return the new file name ConvertedToCsv = NewFile End Function -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jake Marx" wrote in message ... Hi raj, I don't think that would work too well, as your fields would be kind of meaningless. You could open the text file and read it record by record (field by field) into a recordset. Here's an example that should get you started: http://msdn.microsoft.com/library/en...datasource.asp Obviously, you don't have to create a class like this example; the general idea is to open the text file using file I/O and read it in line by line. You could use the Split function to split each line into an array of fields, then assign each of the array's elements to the fields in the record. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] raj wrote: Thanks Jake. As an alternative workaround, could I instead do a search and replace inside the recordset and replace the ";" with ","? If so, do you know how to do this, and could you by chance supply an example in your post back? TIA -----Original Message----- Hi raj, I think you'll have to use a schema.ini file to specify the format of the text file you're opening. Here's a link to a similar thread that may help to get you started: http://groups.google.com/groups?hl=e...=UTF-8&oe=UTF- 8&threadm=5scsrvopmd07ka6hc413j4eccprb7766hq% 404ax.com&rnum=4 If you do a search on schema.ini and ado, you should find a lot of helpful information. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] raj wrote: Hello, I hope someone can help me with this issue, please. I need to open a large semi-colon delimited text file into a recordset using ADO. Is this possible? If so, how do I make ADO aware that the file is "semi-colon" delimited? Right now, my code does not recognize this delimeter and imports all the data into the recordset as one column (NOTE: there are too many rows to open into a spreadsheet). Your example code would be most appreciated, thanks in advance. If needed, my code so far is below: Set cnText = New ADODB.Connection cnText.CursorLocation = adUseClient cnText.ConnectionString = "PROVIDER=MSDASQL;DRIVER= {MICROSOFT TEXT DRIVER (*.TXT; *.CSV)};DBQ=" & argFilePath & ";" cnText.Open strSQL = "SELECT * FROM " & argFileName & ";" Set rsText = New ADODB.Recordset rsText.MaxRecords = 0 rsText.Open Source:=strSQL, ActiveConnection:=cnText, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText Your example code would be most helpful, TIA. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert column data to semicolon delimited text string | Excel Worksheet Functions | |||
Saving Excel sheet as a semicolon delimited file (.csv) | Excel Discussion (Misc queries) | |||
Converting Excel data into semicolon delimited text file | Excel Discussion (Misc queries) | |||
Export excel file to semicolon delimited text file | Excel Discussion (Misc queries) | |||
Open delimited text file to excel without changing data in that file | Excel Programming |