Import CSV into datagrid in VB.Net

Posted on 2012-08-17
Last Modified: 2012-08-21
Using the attached code to bring a csv into a datagrid in works, the only problem is that it only brings in numbers and dates but will not bring in strings.
Question by:jeremyj54
    LVL 35

    Expert Comment

    by:Robert Schutt
    It seems that the text driver is scanning the csv file and when it finds numbers or dates, then any strings (in the same column) are no longer accepted.

    If the csv files are generated and it's ok to treat all data as text, you could enclose the values in quotes. Otherwise, it may be possible to override this in a schema.ini file.

    Can you give an example of (a few rows) of one of your csv files?

    Author Comment

    I have attached one of the files.
    LVL 35

    Expert Comment

    by:Robert Schutt
    Yup, so the problem is indeed that your fields are being recognised as numbers and dates (starting from line 5). I think what you would need to do is create a file schema.ini (either manually or, if the file names and contents can vary, in your code) containing the column definitions as all Char/Text columns, or use MaxScanRows=1 (pity you can't use that in the connection string) so that it just looks at the first line which contains mixed data so it won't be recognised as numbers/dates (if this is a typical file).

    Another option altogether (especially if you just need all the text from the file and no filtering is done) is of course to read the file into the grid with a File/TextStream object.

    Author Comment

    That is all I need, how would I use that?
    LVL 35

    Accepted Solution

    a schema.ini file would look like this:
    1=Column1 Char
    2=Column2 Char
    3=Column3 Char
    4=Column4 Char
    5=Column5 Char
    6=Column6 Char
    7=Column7 Char
    8=Column8 Char
    9=Column9 Char
    10=Column10 Char
    11=Column11 Char
    12=Column12 Char
    13=Column13 Char
    14=Column14 Char
    15=Column15 Char
    16=Column16 Char
    17=Column17 Char
    18=Column18 Char
    19=Column19 Char
    20=Column20 Char
    21=Column21 Char
    22=Column22 Char
    23=Column23 Char
    24=Column24 Char
    25=Column25 Char
    26=Column26 Char
    27=Column27 Char
    28=Column28 Char
    29=Column29 Char
    30=Column30 Char

    Open in new window

    Problem of course would be that you have to write this out every time if there is a possibility of variation in file names or number of columns (and in every directory). If the assumption about the first line always being obvious strings, not numbers or dates, then you don't need the column definition because now it's only looking at the first line (MaxScanRows=1).

    Here's another way to do it, without an oledb connection, note that this breaks when values are quoted, which they never seem to be in your case.
    You could add the data to the grid directly but I kept the DataTable because it makes handling the data in the rest of the program easier.
                        Dim DeltaData As New DataTable()
                        Using sr As StreamReader = File.OpenText()
                            While Not sr.EndOfStream
                                Dim vals As Object() = sr.ReadLine().Split(","c)
                                If DeltaData.Columns.Count = 0 Then
                                    Dim cols(vals.Count - 1) As DataColumn
                                    For c As Integer = 0 To UBound(cols)
                                        cols(c) = New DataColumn("Columns " & (c + 1)) ' vals(c)
                                End If
                            End While
                        End Using
                        DataGridView1.DataSource = DeltaData.DefaultView

    Open in new window

    If you want the column names on line 4 you should really skip the first 3 lines (or show them differently. You could use the commented out "vals(c)" for that but you would have to do it on line 4.

    Author Closing Comment

    Solution Worked great, thanks for the help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
    Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now