VBScript ADO with TEXT Files Please help

[sanjdhiman]

Hi there

Problem with the code recognising the second field and returning its values not nulls.

If I set rs.Fields(0) then the first field of the data is selected with its correct value. If i change this to anything other than 0, i.e. 1 to get the second field, it only gives a null as its value. Here is the code

Function Main()

Dim Conn, rs
Dim fso2, tf, var
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=\\cmasvrfs\data\Shared\Datamanagment\Team\Sanj\Jul25;" & _
"Extensions=asc,csv,tab,txt;" & _
"Format=Delimited(|)" & _
"Persist Security Info=False"

Dim sql
sql = "SELECT * FROM [AHD#txt]"
set rs = conn.execute(sql)
stop
While Not rs.EOF

SET fso2= CREATEOBJECT("Scripting.FileSystemObject")
SET tf = fso2.OpenTextFile( "\\cmasvrfs\data\Shared\Datamanagment\Team\Sanj\Jul25\ResultOfADO.txt",8)
IF rs.Fields.Item(2) = Null Then
Msgbox "Null value found"
Else
'var = CStr(rs.Fields(0))
var = rs.Fields.Item(2)
tf.writeline(var)
End IF

set fso2 = nothing
set tf = nothing
rs.MoveNext
Wend
Set rs = nothing
Main = DTSTaskExecResult_Success
End Function


also Here is the raw data

asset_id|amt_out_dt|amt_outsd|currency_cd
0x0003860035e8a1f9|20030130|500|EUR
0x00038600379f361b|20021212|500000|JPY
0x0003860033b4cbc9|19980115|2000|DEM
0x0003860033beaaa2|20021118|0|ITL
0x0003860033beaaa2|19971118|20000000|ITL
0x0003860033c11463|19980120|650000|FRF
0x0003860033c11463|19980119|500000|FRF
0x000386003646fe0b|20030117|12000|USD
0x000386003646fe0b|20030130|12000|USD

Here is the schema.ini file also

[AHD.txt]
CharacterSet=ANSI
ColNameHeader=True
MaxScanRows=0
Col1=assetID Text Width 18
Col2=amt_out_dt Text Width 8
Col3=amt_outsd Text Width 10
Col4=currency_cd Text Width 10


Thanks in advance for your help

 

[Wholsea]

Here is how I got this to work...

Made a new folder:
c:\test\

Put your data file (data.txt) into c:\test\data.txt

Saved a copy of your code to a new file c:\test\test.vbs

Modified the code to look like this:


Dim Conn, rs
Dim fso2, tf, var
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\test;" & _
"Extensions=asc,csv,tab,txt;" & _
"Format=Delimited(|)" & _
"Persist Security Info=False"

Dim sql
sql = "SELECT * FROM [data#txt]"
set rs = conn.execute(sql)
stop
While Not rs.EOF
columns = split(rs.fields(0).value,"|")

SET fso2= CREATEOBJECT("Scripting.FileSystemObject")
SET tf = fso2.OpenTextFile( "data2.txt",8)

for i = 0 to ubound(columns)
wscript.echo columns(i)
next
set fso2 = nothing
set tf = nothing
rs.MoveNext
Wend
Set rs = nothing


Worked like a charm...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[Wholsea]

Okay. I didn't like that solution, so I came up with this...

"test" directory:
c:\test\

Contains these files:
schema.ini
data.txt
test.vbs

---[ Schema.ini ]---
[data.txt]
CharacterSet=ANSI
ColNameHeader=True
MaxScanRows=0
Format=Delimited(|)
Col1=assetID Text Width 18
Col2=amt_out_dt Text Width 8
Col3=amt_outsd text width 10
Col4=currency_cd Text Width 10

---[ Data.txt ]---
asset_id|amt_out_dt|amt_outsd|currency_cd
0x0003860035e8a1f9|20030130|500|EUR
0x00038600379f361b|20021212|500000|JPY
0x0003860033b4cbc9|19980115|2000|DEM
0x0003860033beaaa2|20021118|0|ITL
0x0003860033beaaa2|19971118|20000000|ITL
0x0003860033c11463|19980120|650000|FRF
0x0003860033c11463|19980119|500000|FRF
0x000386003646fe0b|20030117|12000|USD
0x000386003646fe0b|20030130|12000|USD


---[ test.vbs ]---
Dim Conn, rs
Dim fso2, tf, var
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\test;" & _
"Extensions=asc,csv,tab,txt;" & _
"Persist Security Info=False"
set rs = CreateObject("ADODB.Recordset")

Dim sql
sql = "SELECT * FROM [data#txt]"
rs.open sql,conn

While Not rs.EOF
for i = 0 to rs.fields.count - 1
wscript.echo rs.fields(i).value
next

rs.MoveNext
Wend
Set rs = nothing



*THIS* works like you would expect it to...


ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise