I'm having trouble importing data from a column from a CSV file. The column causing the problem, CustomerOrderNumber can be numeric or alphanumeric. In the sample file I'm trying to import (supplied by a 3rd party), the first row contains a customer order number of 123456, the second row contains X123456. The first row imports fine, however the second row is read as DBNULL.
I use the following code to open the file:
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("\") & "CRMAdmin\Uploads\" & ";" & _
Dim objConn As New OleDbConnection(sConnectionString)
Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & strFilename, objConn)
I've imported Excel data before with no problem, after changing the registry setting HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows to 1000 (this allows Jet to determine the datatype of the column).
For the CSV text import I've changed "MaxScanRows" to 1000 to no avail.
Is there any other setting I should change to import this CSV text file successfully?
what are the following keys and allowed values?