Using ADO to parse CSV fields returns null values

[rss0213]

Hi. I'm struggling with a task to parse a CSV into individual fields. I found a topic about this on this forum and others, but implementing the code has not provided the results I expected. Here's my code:

BEGIN CODE
----------------

varAppPath=objFSO.GetParentFolderName(wscript.ScriptFullName) & "\"

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & varAppPath & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

objRecordset.Open "SELECT * FROM Vendors2.csv", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
WScript.Echo objRecordset.Fields("VendorNum").Value
outVenNum = objRecordset.Fields("VendorNum").Value
outVenName = objRecordset.Fields("VendorName").Value
outContact = objRecordset.Fields("Contact").Value
outPhone = objRecordset.Fields("Phone").Value
outAddr2 = objRecordset.Fields("NameAddr2").Value
outAddr3 = objRecordset.Fields("NameAddr3").Value
outAddr4 = objRecordset.Fields("NameAddr4").Value
outAddr5 = objRecordset.Fields("CityST").Value
outPostalCode = objRecordset.Fields("Zip").Value
objRecordset.MoveNext
...
Loop

-------------
END CODE

The problem is that all my out* fields are null, but there is DEFINITELY data in the input CSV file. And the CSV file is in the same dir as the VB script.

Also, it just loops forever. It's like the MoveNext method is not working - it's never reaching the end of the record set.

Please help!


Thanks!
Scott

 

[Foada]

Take a look at this thread222-1587532
I am thinking that you are experiencing a problem with the way Jet formats the data in the CSV file.

If you choose to battle wits with the witless be prepared to lose.

 

[rss0213]

I added the schema file, but the out* fields are still null. The loop finished now though. There are 5 records in the CSV input file, and all five records are processed, but output values are null. This is very strange... Is it the way I'm formatting my assignment statements (outVenNum = objRecordset.Fields("VendorNum").Value, etc.)?

Thanks!
Scott

 

[Foada]

Can you post your schema file?

If you choose to battle wits with the witless be prepared to lose.

 

[rss0213]

[Vendors.csv]
Format=Delimited(,)
ColNameHeader=False
Col1=VendorNum Char
Col2=VendorName Char
Col3=Contact Char
Col4=Phone Char
Col5=NameAddr2 Char
Col6=NameAddr3 Char
Col7=NameAddr4 Char
Col8=CityST Char
Col9=Zip Char
CharacterSet=ANSI


Thanks!
Scott

 

[Foada]

Strange. I ran the code you have given with the schema file you posted with VB6 and replaced the echo with a debug.print and mocked some data in the venders2.csv file and the portion of code you have posted works fine. Could there be something somewhere else in the code further down your script?

If you choose to battle wits with the witless be prepared to lose.

 

[rss0213]

Yes, it is strange... I threw in the towel with the VB script approach. I imported the csv file into an Access table and copied the VB script code into a VBA module, and it works fine (after I changed a few things with the ADO objects). Anyway, I appreciate your help. Fortunately (in this case), there's more than one way to skin a cat!

Thanks!
Scott

 

[tsuji]

Why don't you comment out the "On error resume next" line? Do you know what it means? Do you know how much damage ms team at that period of time did and continue to do damage to the community of less informed with their bad examples, amid huge number of great jobs done?

 

[rss0213]

tsuji,

I think you're trying to insult my intelligence, but your very poor English is preventing you from doing so. Sorry...

Thanks!
Scott

 

[Gruuuu]

actually, I think he was stressing the dangers of completely bypassing errors without properly handling them, while also expressing his displeasure at its widespread use.

 

[rss0213]

Ahh... your translation makes sense. Well, I'm still figuring out this VB stuff. So if I wasted anyone's time, I apologize.

Thanks!
Scott

 

[rss0213]

I found a solution on another forum:

http://www.scriptinganswers.com/forum2/forum_posts.asp?TID=3812&PID=22865#22865

This, too, was not available until after I had already performed the work-around. But maybe someone else will have the same problem in the future and find the answer here...

Thanks!
Scott

 

[Gruuuu]

It's definitely not a waste of time. obviously it's not a question we had an immediate answer to. Learning experience (or at least a refresher) for all! And thank you posting the solution, too.

 

[tsuji]

The original lines are taken without acknowledgement from the cardinal ms scripting team article:

http://msdn.microsoft.com/en-us/library/ms974559.aspx

Before defense your intelligence, defense your intellectual integrity.

 

[tsuji]

Thanks Gruuuu for everything!