Strange: DAO could not pickup some columns in Tab delimited file 
Author Message
 Strange: DAO could not pickup some columns in Tab delimited file

Hello all:

I got this strange problem (a bug in DAO) that VB/DAO cannot pick up value
in a
tab-delimited text file.  I open the tab-delimited (TABTRANS.TXT) file using
DAO with the schema.ini defined below.  The recordset picks up most of
fields, but column 6
and 7, it shows NULL values, where it should pickup the "Date Of Service"
and the "RefProvID"

Any suggestion to work around this bug???

I am using VB6 with SP 2

Thank you in advance.

Donald

---VB code-----------------------------

    Dim wetsuit As DAO.Workspace
    Dim dbText As DAO.Database
    Dim rsText As DAO.Recordset

    ' Open text file as database
    Set wsText = DBEngine.Workspaces(0)
    Set dbText = wsText.OpenDatabase("C:\Temp", False, False, "TEXT;")

    ' Get Records
    Set rsText = dbText.OpenRecordset("TABTRANS.TXT")
    While Not rsText.EOF
        Debug.Print rsText.Fields(0).Value & "," & _
        rsText.Fields(1).Value & "," & _
        rsText.Fields(2).Value & "," & _
        rsText.Fields(3).Value & "," & _
        rsText.Fields(4).Value & "," & _
        rsText.Fields(5).Value & "," & _
        rsText.Fields(6).Value & "," & _
        rsText.Fields(7).Value & "," & _
        rsText.Fields(8).Value & "," & _
        rsText.Fields(9).Value & "," & _
        rsText.Fields(10).Value & "," & _
        rsText.Fields(11).Value & "," & _
        rsText.Fields(12).Value & "," & _
        rsText.Fields(13).Value & "," & _
        rsText.Fields(14).Value & "," & _
        rsText.Fields(15).Value
        rsText.MoveNext
    Wend

    rsText.Close
    dbText.Close
    wsText.Close

---Output looks like------------
TEST,CHARGE,ONLYLOCATION,ONLYCLASS,99111,10,50,,,ONLYCARRIER,NOIDC9_1,NULL,N
ULL,NULL,ONLYPATIENT,5/7/99
TEST,CHARGE,ONLYLOCATION,ONLYCLASS,99222,1,69.99,,,ONLYCARRIER,NOIDC9_1,NULL
,NULL,NULL,ONLYPATIENT,5/7/99

---Schema.ini----------------
[TABTRANS.TXT]
Format=TABDelimited
ColNameHeader=TRUE
Col1=ProviderID CHAR
Col2=LocationID CHAR
Col3=ClassID CHAR
Col4=TypeID CHAR
Col5=RefProvID CHAR
Col6=DateOfService DATE
Col7=CPT CHAR
Col8=Units  INTEGER
Col9=Amount FLOAT
Col10=Carrier CHAR
Col11=ICD9_1 CHAR
Col12=ICD9_2 CHAR
Col13=ICD9_3 CHAR
Col14=ICD9_4 CHAR
Col15=PatientID CHAR
Col16=PostingDate DATE
CharacterSet=ANSI

---Data File:tabTrans.txt-------------------
Provider Transaction Type Location Class CPT Units Amount Date Of Service
Referring Provider Insurance Carrier ICD9 1 ICD9 2 ICD9 3 ICD9 4 Patient
Posting Date
TEST CHARGE ONLYLOCATION ONLYCLASS 99111 10 50 5/7/99 REFPROVIDER
ONLYCARRIER NOIDC9_1 NULL NULL NULL ONLYPATIENT 5/7/99
TEST CHARGE ONLYLOCATION ONLYCLASS 99222 1 69.99 5/7/99 REFPROVIDER
ONLYCARRIER NOIDC9_1 NULL NULL NULL ONLYPATIENT 5/7/99



Sat, 27 Oct 2001 03:00:00 GMT
 Strange: DAO could not pickup some columns in Tab delimited file

Nerver Mind this one, I think I solve it.

Quote:

>Hello all:

>I got this strange problem (a bug in DAO) that VB/DAO cannot pick up value
>in a
>tab-delimited text file.  I open the tab-delimited (TABTRANS.TXT) file
using
>DAO with the schema.ini defined below.  The recordset picks up most of
>fields, but column 6
>and 7, it shows NULL values, where it should pickup the "Date Of Service"
>and the "RefProvID"

>Any suggestion to work around this bug???

>I am using VB6 with SP 2

>Thank you in advance.

>Donald

>---VB code-----------------------------

>    Dim wetsuit As DAO.Workspace
>    Dim dbText As DAO.Database
>    Dim rsText As DAO.Recordset

>    ' Open text file as database
>    Set wsText = DBEngine.Workspaces(0)
>    Set dbText = wsText.OpenDatabase("C:\Temp", False, False, "TEXT;")

>    ' Get Records
>    Set rsText = dbText.OpenRecordset("TABTRANS.TXT")
>    While Not rsText.EOF
>        Debug.Print rsText.Fields(0).Value & "," & _
>        rsText.Fields(1).Value & "," & _
>        rsText.Fields(2).Value & "," & _
>        rsText.Fields(3).Value & "," & _
>        rsText.Fields(4).Value & "," & _
>        rsText.Fields(5).Value & "," & _
>        rsText.Fields(6).Value & "," & _
>        rsText.Fields(7).Value & "," & _
>        rsText.Fields(8).Value & "," & _
>        rsText.Fields(9).Value & "," & _
>        rsText.Fields(10).Value & "," & _
>        rsText.Fields(11).Value & "," & _
>        rsText.Fields(12).Value & "," & _
>        rsText.Fields(13).Value & "," & _
>        rsText.Fields(14).Value & "," & _
>        rsText.Fields(15).Value
>        rsText.MoveNext
>    Wend

>    rsText.Close
>    dbText.Close
>    wsText.Close

>---Output looks like------------
>TEST,CHARGE,ONLYLOCATION,ONLYCLASS,99111,10,50,,,ONLYCARRIER,NOIDC9_1,NULL,
N
>ULL,NULL,ONLYPATIENT,5/7/99
>TEST,CHARGE,ONLYLOCATION,ONLYCLASS,99222,1,69.99,,,ONLYCARRIER,NOIDC9_1,NUL
L
>,NULL,NULL,ONLYPATIENT,5/7/99

>---Schema.ini----------------
>[TABTRANS.TXT]
>Format=TABDelimited
>ColNameHeader=TRUE
>Col1=ProviderID CHAR
>Col2=LocationID CHAR
>Col3=ClassID CHAR
>Col4=TypeID CHAR
>Col5=RefProvID CHAR
>Col6=DateOfService DATE
>Col7=CPT CHAR
>Col8=Units  INTEGER
>Col9=Amount FLOAT
>Col10=Carrier CHAR
>Col11=ICD9_1 CHAR
>Col12=ICD9_2 CHAR
>Col13=ICD9_3 CHAR
>Col14=ICD9_4 CHAR
>Col15=PatientID CHAR
>Col16=PostingDate DATE
>CharacterSet=ANSI

>---Data File:tabTrans.txt-------------------
>Provider Transaction Type Location Class CPT Units Amount Date Of Service
>Referring Provider Insurance Carrier ICD9 1 ICD9 2 ICD9 3 ICD9 4 Patient
>Posting Date
>TEST CHARGE ONLYLOCATION ONLYCLASS 99111 10 50 5/7/99 REFPROVIDER
>ONLYCARRIER NOIDC9_1 NULL NULL NULL ONLYPATIENT 5/7/99
>TEST CHARGE ONLYLOCATION ONLYCLASS 99222 1 69.99 5/7/99 REFPROVIDER
>ONLYCARRIER NOIDC9_1 NULL NULL NULL ONLYPATIENT 5/7/99



Sun, 28 Oct 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Changing Tab-delimited file to Comma-delimited

2. open mdb file and create several tab delimited ascii files

3. Converting comma-delimited data to tab-delimited

4. bar-delimit to tab-delimit

5. Help File - Opens on Find tab not Contents tab

6. bcp errors on Tab delimited files

7. DTS Import Tab Delimited Data From Txt File

8. DTS Import of tab-delimited file

9. Query to tab delimited files

10. scheduled export to a tab-delimited file

11. create a tab delimited file using isql.exe (SQL Server 6.5)

12. Reading Tab-delimited data files into SQL


 
Powered by phpBB® Forum Software