ADO & semicolon delimited text file?

[raj]

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.

 

[Jake Marx]

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/[email protected]&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]

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=en&lr=&ie=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]

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.

.

 

[Jake Marx]

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-us/vbcon98/html/vbconcreatenewdatasource.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]

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=en&lr=&ie=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]

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.

.

 

[Dick Kusleika]

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.

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-us/vbcon98/html/vbconcreatenewdatasource.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]

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=en&lr=&ie=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.

.