M
Monty
Hi All,
I am having a problem with leading zeros being stripped from fields in a CSV
file when I bring them in using Jet/OleDB. In VB.Net/VS 2008, I am accessing
a CSV file like so:
sSQL = "SELECT * FROM [" & sFileName & "]"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Microsoft.VisualBasic.FileIO.FileSystem.GetParentPath(msFile) &
_
"\;Extended Properties=""text;HDR=No;FMT=Delimited"";"
CreateSchemaIniFile(sFileName, sPath) 'creates my Schema.ini file
oConn = New System.Data.OleDb.OleDbConnection(sConnStr)
oConn.Open()
oDA = New System.Data.OleDb.OleDbDataAdapter(sSQL, oConn)
oDA.Fill(oDS)
With FileSystem.OpenTextFieldParser(msFile)
msFileHeader = .ReadLine()
.Close()
End With
To set each column to a text format so I can retain any leading zeros, I
dynamically create a Schemal.ini file that looks something like this (first
few rows):
[ImportMe_TestLeadingZero.csv]
Format=CSVDelimited
MaxScanRows=1
ColNameHeader=False
CharacterSet=OEM
Col1="PersonID" Char
Col2="First Name" Char
Col3="Middle Name" Char
Col4="Last Name" Char
In the Schema.ini file, I have tried both "Char" and "Text" as the format
descriptor for each column.
My simplified CSV file looks like this:
"PersonID","First Name","Middle Name","Last Name"
00334,Jimbo,J,Jones
With this setup, if I reference the first field, the PersonID comes in as
334. However, if I change my CSV by putting double quotes arround the
PersonID value (like "00334"), it then comes in correctly with the leading
zeros. Unfortunately I have no control over the source file so I cannot add
the double-quote field qualifiers. Can anyone tell me if there is a way to
bring the values correctly with the leading zeros? For what it's worth, I've
also set the registry key like this (below), but to no avail:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes=Text
TIA for your help.
I am having a problem with leading zeros being stripped from fields in a CSV
file when I bring them in using Jet/OleDB. In VB.Net/VS 2008, I am accessing
a CSV file like so:
sSQL = "SELECT * FROM [" & sFileName & "]"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Microsoft.VisualBasic.FileIO.FileSystem.GetParentPath(msFile) &
_
"\;Extended Properties=""text;HDR=No;FMT=Delimited"";"
CreateSchemaIniFile(sFileName, sPath) 'creates my Schema.ini file
oConn = New System.Data.OleDb.OleDbConnection(sConnStr)
oConn.Open()
oDA = New System.Data.OleDb.OleDbDataAdapter(sSQL, oConn)
oDA.Fill(oDS)
With FileSystem.OpenTextFieldParser(msFile)
msFileHeader = .ReadLine()
.Close()
End With
To set each column to a text format so I can retain any leading zeros, I
dynamically create a Schemal.ini file that looks something like this (first
few rows):
[ImportMe_TestLeadingZero.csv]
Format=CSVDelimited
MaxScanRows=1
ColNameHeader=False
CharacterSet=OEM
Col1="PersonID" Char
Col2="First Name" Char
Col3="Middle Name" Char
Col4="Last Name" Char
In the Schema.ini file, I have tried both "Char" and "Text" as the format
descriptor for each column.
My simplified CSV file looks like this:
"PersonID","First Name","Middle Name","Last Name"
00334,Jimbo,J,Jones
With this setup, if I reference the first field, the PersonID comes in as
334. However, if I change my CSV by putting double quotes arround the
PersonID value (like "00334"), it then comes in correctly with the leading
zeros. Unfortunately I have no control over the source file so I cannot add
the double-quote field qualifiers. Can anyone tell me if there is a way to
bring the values correctly with the leading zeros? For what it's worth, I've
also set the registry key like this (below), but to no avail:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes=Text
TIA for your help.