'OLEDB connection avoid auto convert on text to int

when run a simple vbs code to read a csv. Example CSV:

AB,CD
XYZABDER,TLK431
..

with following code

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

strPathtoTextFile = "C:\xyz\"

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

objRecordset.Open "SELECT * FROM myfile.csv", objConnection

Do Until objRecordset.EOF
    MsgBox "AB: " & objRecordset.Fields.Item("AB")
    MsgBox "CD: " & objRecordset.Fields.Item("CD")
    objRecordset.MoveNext
Loop

it alsways converts "TLK431" to 431. As soon as i change the "TLK" to "TLB" or whatever its read as string "TLB431" for "TL" or "TLK" its converting to int 431.

Cant understand the reason for this auto conversion.

For now my workaround is a schema file. But i doesnt like it and want to ask for a better solution and the reason for this conversion.

Thx

vbscriptoledbconnection


Solution 1:[1]

This may be an encoding issue. If the CSV file contains Unicode characters, ensure that both the CSV and VBS files are saved as Unicode (UTF-16), otherwise, save both files as ANSI. Do not use UTF-8.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 LesFerch

Related Questions