Datagrid leaves empty cells

[spyderman4g63]

I am importing a .csv fiel 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 was wondering if I had to set the field type to a string (if that has anything to do with it) and how I would do it.

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

 

[cjard]

As evidenced by the
PEPSI 24 PAK,04

becoming
PEPSI 24 PAK,4

I think it's fair to say that some sort of cooking is happening to the data.. the fields you outline look like numbers until one happens upon the letter P in the middle of them


ps; http://www.vbdotnetforums.com/showthread.php?t=14111&highlight=pepsi looks awfully familiar... nice to see the example project I created went to good use!

 

[spyderman4g63]

The question has been answered. When the data is imported the column was being set as numerical. When the mixed type data was seen it was discarded because it didn't fit the data type. If I was using excell I could have added IMEX=1 to the extended properties in the connection string.

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

However, this will not work with my text (.csv) file. The solution is to use a schema.

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 header 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/d...ma_ini_file.asp