CSV to datatable only showing integers - Xtreme Visual Basic Talk

11-12-2007, 08:48 AM

itsover

Newcomer

Join Date: Nov 2007

Posts: 4

CSV to datatable only showing integers

Hey,

My app has a simple function that selects all the data on an csv file, puts the data onto a datatable, and displays that datatable on a datagridview.

The problem is that if a column in my csv file contains mostly numbers (50% or greater), the rest of the values are missing. For example, if my csv file contains:

Code:

Code,Description,Filter
N,None,FALSE
1,area flood or flash flood products,FALSE
2,Minor,FALSE
3,Moderate,FALSE
4,Major,FALSE
U,Unknown,FALSE

then my datatable contains:

Code:

Code,Description,Filter
[DbNull],None,FALSE
1,area flood or flash flood products,FALSE
2,Minor,FALSE
3,Moderate,FALSE
4,Major,FALSE
[DbNull],Unknown,FALSE

If my csv file contains:

Code:

Code,Description,Filter
N,None,FALSE
N,area flood or flash flood products,FALSE
N,Minor,FALSE
3,Moderate,FALSE
4,Major,FALSE
U,Unknown,FALSE

it would work fine.

Here is the function:

Code:

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

        Dim connStr As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Environment.CurrentDirectory & ";" & _
            "Extended Properties=""text;HDR=YES\;FMT=Delimited;"""
        Dim dbConn As OleDbConnection = New OleDbConnection(connStr)
        Dim dbCmd As OleDbCommand = New OleDbCommand("SELECT * FROM " & csvFile, dbConn)
        Dim dbDataAdptr As OleDbDataAdapter = New OleDbDataAdapter()
        Dim csvDt As DataTable = New DataTable()

        ' Open connection to csv database
        dbConn.Open()

        ' Add columns to csv datatable, explicitly set type
        csvDt.Columns.Add("Code", GetType(String))
        csvDt.Columns.Add("Description", GetType(String))
        csvDt.Columns.Add("Filter", GetType(Boolean))

        ' Fill datatable from database
        dbDataAdptr.SelectCommand = dbCmd
        dbDataAdptr.Fill(csvDt)

        ' Display on datagridview
        DataGridView1.DataSource = csvDt

        ' Clean up
        dbConn.Close()
        dbCmd = Nothing
        dbConn = Nothing

    End Sub

At first I realized the datatable first column's value type was automatically being set to int32, so I explicitly add columns (with first column's value type as string). But that did not solve the problem.

Any advice is appreciated. Thank you in advance.

11-12-2007, 11:19 AM

Jayded

Centurion

Join Date: Jul 2003

Posts: 144

I dont mean to sound weird but why bother with the datatable? You would have a lot more control over the displayed elements if you wrote it straight to the datagridview.
The datatable is trying to be to intelligent when all you really need to do is display data on a datagridview. Unless you have strong reasons for using the datatable I would dump it altogether. The speed difference would be negligible for such small data amounts and even for a larger amount it would perform that poorly.

11-13-2007, 08:57 AM

itsover

Newcomer

Join Date: Nov 2007

Posts: 4

I'd like to treat the csv file as a database rather than a text file. In order for me to put the results of a query onto a datagridview, I needed to use a datatable as a medium to the database. If you could suggest to me another way, which does not require the datatable, I'd be happy to try it.

11-13-2007, 10:17 AM

Jayded

Centurion

Join Date: Jul 2003

Posts: 144

It would be a fairly simple process of loading the csv file into a IO stream and then reading it line by line in a loop.
You would then load the datagridview using a string array.
That said on re reading the problem could I ask whether you have checked the data in the datatable? It would be interesting to see what has been loaded and it may also give a clue as to whats going on. As a suggestion run a quick loop through your datatable and either debug.print the results or load them into a msgbox().
If the problem is between the datatable and the datagridview then it would probably be easier to load the datagridview manually from the datatable.
Could you also attach a real sample of the CSV file?

11-13-2007, 01:15 PM

itsover

Newcomer

Join Date: Nov 2007

Posts: 4

It is not the problem of the datatable to datagridview. The datatable is missing the data.

I am strongly trying to avoid using file IO to process the csv file as it should be treated as a database.

Attached Files

hvtec_severity.zip (241 Bytes, 1 views)

11-13-2007, 05:18 PM

IUnknown

Senior Contributor

Forum Leader
* Expert *

Join Date: Oct 2004

Location: Montréal

Posts: 1,135

You can use a schema.ini file to define the datatype of the columns in the csv file to be imported.

BTW, you can also put the connection string into the app.config file and retrieve it using MySettings.

__________________
win7 : vs 2008 : .Net 3.5

Last edited by IUnknown; 11-13-2007 at 05:28 PM.

11-13-2007, 10:07 PM

itsover

Newcomer

Join Date: Nov 2007

Posts: 4

Quote:

This solved my problem! Thank you very much!