Handling Comma while reading a .txt file after converting toresultset

[Avi]

Hi All,

I am reading a text file after converting it to a record set. I have
defined a custom delimiter (|). My txt file looks like

**************
a|b|d
1|4,11|12
3|2,13|14
**************

and I am reading the record set like

******************************************
While oRs.EOF <> "True"
MsgBox oRs(0).Value
MsgBox oRs(1).Value
MsgBox oRs(2).Value
oRs.MoveNext
Wend
******************************************

Problem I am facing is; the second column b is read as date and I am
not getting any clue why. Messagebox oRs(1).Value displays values as
4:11:00 AM and I want to see this as 4,11 only

Please let me know if you have any idea why this is happening and how
can i fix the issue.

Any help on this issue is highly appreciated.

Thanks,
Avinash

 

[Dave Peterson]

You got replies to your other post.

Hi All,

I am reading a text file after converting it to a record set. I have
defined a custom delimiter (|). My txt file looks like

**************
a|b|d
1|4,11|12
3|2,13|14
**************

and I am reading the record set like

******************************************
While oRs.EOF <> "True"
MsgBox oRs(0).Value
MsgBox oRs(1).Value
MsgBox oRs(2).Value
oRs.MoveNext
Wend
******************************************

Problem I am facing is; the second column b is read as date and I am
not getting any clue why. Messagebox oRs(1).Value displays values as
4:11:00 AM and I want to see this as 4,11 only

Please let me know if you have any idea why this is happening and how
can i fix the issue.

Any help on this issue is highly appreciated.

Thanks,
Avinash

 

[Tim Williams]

If you're running a select against the file to populate the recordset then
you could try casting the second column to string rather than have the
driver guess at the data type.

If you post the SQL you're using then someone might have suggestions. I'm
not sure what type of syntax can be used with the text file driver.

Tim

 

[Avi]

Thanks for the reply Tim.

Below is the code that I am using to query the text file. It is a part
of reusable function where I pass the file name and get the result set
back. So, in my case i really don't know what the column names would
be, and how many columns are going to be there. That is the reason I
used "select * from filename". Once I get the result set i get the
element by column name to use this further.

So the solution of type casting the column in the query will not allow
me to re-use the function.

Any other suggestions ?


************************************************************************************************************
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnectionString = sConnectionString + "Data Source=" &
szFileFolder & ";"
sConnectionString = sConnectionString + "Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open "select * from [" & szFileTitle & "]", sConnectionString
************************************************************************************************************

 

[Tim Williams]

I tried this complete sub:

Sub TestText()

Dim szFileTitle As String, sConnectionString As String
Dim rs As ADODB.Recordset
Dim f

szFileTitle = "tester.txt"


sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & _
"Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open "select * from [" & szFileTitle & "]", sConnectionString

For Each f In rs.Fields
Debug.Print f.Name
Next f

Do While Not rs.EOF
For Each f In rs.Fields
Debug.Print f.Value & " - "
Next f
rs.MoveNext
Loop

End Sub

It didn't work for me at all - the driver does not seem to recognize the
pipe character as a delimiter. How did you get that to work ?

In any case - if your code is working all you need to do is do a select
which will return an empty recordset:

"select * from [" & szFileTitle & "] where 1=2"

Then loop through the column names and build another select to cast the
values to string.

Tim

Thanks for the reply Tim.

Below is the code that I am using to query the text file. It is a part
of reusable function where I pass the file name and get the result set
back. So, in my case i really don't know what the column names would
be, and how many columns are going to be there. That is the reason I
used "select * from filename". Once I get the result set i get the
element by column name to use this further.

So the solution of type casting the column in the query will not allow
me to re-use the function.

Any other suggestions ?


************************************************************************************************************
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnectionString = sConnectionString + "Data Source=" &
szFileFolder & ";"
sConnectionString = sConnectionString + "Extended Properties=Text"

Set rs = New ADODB.Recordset
rs.Open "select * from [" & szFileTitle & "]", sConnectionString
************************************************************************************************************

 

[Avi]

Thanks again Tim.. Will give a shot on this.

I tried this complete sub:

Sub TestText()

    Dim szFileTitle As String, sConnectionString As String
    Dim rs As ADODB.Recordset
    Dim f

    szFileTitle = "tester.txt"

    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & ThisWorkbook.Path & ";" & _
                        "Extended Properties=Text"

    Set rs = New ADODB.Recordset
    rs.Open "select * from [" & szFileTitle & "]", sConnectionString

    For Each f In rs.Fields
        Debug.Print f.Name
    Next f

    Do While Not rs.EOF
        For Each f In rs.Fields
            Debug.Print f.Value & "   -   "
        Next f
        rs.MoveNext
    Loop

End Sub

It didn't work for me at all - the driver does not seem to recognize the
pipe character as a delimiter.  How did you get that to work ?

In any case - if your code is working all you need to do is do a select
which will return an empty recordset:

"select * from [" & szFileTitle & "] where 1=2"

Then loop through the column names and build another select to cast the
values to string.

Tim


Thanks for the reply Tim.

Below is the code that I am using to query the text file. It is a part
of reusable function where I pass the file name and get the result set
back. So, in my case i really don't know what the column names would
be, and how many columns are going to be there. That is the reason I
used "select * from filename". Once I get the result set i get the
element by column name to use this further.

So the solution of type casting the column in the query will not allow
me to re-use the function.

Any other suggestions ?

*************************************************************************** *********************************
    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
    sConnectionString = sConnectionString + "Data Source=" &
szFileFolder & ";"
    sConnectionString = sConnectionString + "Extended Properties=Text"

    Set rs = New ADODB.Recordset
    rs.Open "select * from [" & szFileTitle & "]", sConnectionString
*************************************************************************** *********************************

If you're running a select against the file to populate the recordset then
you could try casting the second column to string rather than have the
driver guess at the data type.

If you post the SQL you're using then someone might have suggestions. I'm
not sure what type of syntax can be used with the text file driver.