Datagrid leaves empty cells

[spyderman4g63]

I am importing a .csv file into a datagrid. When I import the data, some of the columns are left blank.

data from .csv file:
ItemNumber,UPC,Wharehouse,Description,Department,Sequence,Type,Empty
000P005,000-11034-42005,,DRY VERMOUTH,05,,DSDS,
000P014,000-11034-42005,,DRY VERMOUTH,05,00,REG,
20309,000-12000-00017,,PEPSI 24 PAK,04,,,
722,000-12000-00017,,PEPSI 24 PAK,04,80,DSDS,

output from datagrid:
,000-11034-42005,,DRY VERMOUTH,5,,DSDS,
,000-11034-42005,,DRY VERMOUTH,5,0,REG,
20309,000-12000-00017,,PEPSI 24 PAK,4,,,
722,000-12000-00017,,PEPSI 24 PAK,4,80,DSDS,


As you can see some of the data in the ItemNumber field has been imported and some of it hasn't. It seems that all of the 000p0*** fields are the only ones left off. I have determined that it will only display numbers in that column, is there a way I can set the type of the column to string?

Here is the code I am using to read the .csv file into datagrid:

Private Sub cmdOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOpen.Click
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Duplicates\;Extended Properties=Text;"
Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()
Dim objCmdSelect As New OleDbCommand("SELECT * FROM test.csv", objConn)
Dim objAdapter1 As New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
Dim objDataset1 As New DataSet
objAdapter1.Fill(objDataset1, "test")
DataGridView1.DataSource = objDataset1.Tables(0).DefaultView
objConn.Close()
End Sub

[jmcilhinney]

I thought I'd answered this question at another forum earlier today but it seems I didn't. The columns with some numbers and some text values are being interpreted as numbers so the values that won't convert to numbers are being discarded. Try adding "IMEX=1" to the Extended Properties of your connection string to force mixed data to be interpreted as text or else build the DataTable schema before retrieving the data.

[spyderman4g63]

I go tthe connection string to work with double quotes:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Duplicates\;Extended Properties=""text;IMEX=1;"""

But the data is still only displaying numbers. Will this setting work with a text db? Every example I found on google used it with an Excel File.

[spyderman4g63]

I couldn't get the connection string to work. I think IMEX only works with Excel files. I did get the schema to work though. I will list it below incase someone else needs help with this topic.

I created a schema.ini file in the same directory as my csv file.:

[Test.csv]
Col1=Vendor Text width 7
Col2=UPC Text Width 15
Col3=Wharehouse Text Width 15
Col4=Desciprtion Text Width 15
Col5=Department Text Width 4
Col6=Sequence Text width 4
Col7=Type Text width 4

The first line is always the text file name in brackets. The other lines define the columns in my .csv file. For example column one heard is Vendor the type of data is text and the column width is set to 7 characters.

More information is available at:
http://msdn.microsoft.com/library/de...a_ini_file.asp

Thanks for the help.

[jmcilhinney]

My hope was that the IMEX property was a function of the Jet OLEDB provider but you may well be right that it is specific to Excel. I thought it was worth a try though.