locked
Import to indexed table from CSV file RRS feed

  • Question

  • Hi,

     

    I'm trying to import data from a CSV file into an empty table with an incremental integer index.

     

    I have used the following code successfully before to import data to a NON-indexed table:

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim i As Integer = 0

    Using parser As New Microsoft.VisualBasic.FileIO.TextFieldParser("c:\temp\PPCName.csv")

    parser.SetDelimiters(",")

    While Not parser.EndOfData

    i = i + 1

    Me.PPCDataSet.Names.Rows.Add(parser.ReadFields())

    End While

    End Using

    MessageBox.Show(i.ToString())

    End Sub

     

    However, it doesn't work with the above indexed table (the index field is the first one) and I get an unhandled exception with a broken link to the appropriate help page. 

     

    I've tried starting the CSV records with null fields, tab characters &c but without success.  Clearly, if I were inputting the data manually, I'd start by tabbing from the auto-index field to the next field and then entering data for the remaining fields.  But I'd don't know how to automate this. 

     

    I'm something of a newbie with respect to VB.NEt so please bear with me. 

     

    Thanks.

    Monday, August 18, 2008 2:14 PM

Answers

  • What is the message of the exception and on what line of code does the exception occur? You probably want to use a Try...Catch block to handle any exceptions that occur during the import process.
    Monday, August 18, 2008 2:57 PM
  • JH,

     

    I think you can use a streamreader and the split method to do what you need.  If i understand correctly you have an auto increment primary key identity field.  Here is an example to read the lines of the csv file into an array in a loop and use only the values you need and place them into your datatable.

     

    Dim sr As IO.StreamReader = New IO.StreamReader(Application.StartupPath & "\SampleCSV.csv")

            sr.ReadLine() 'read the column header line to skip it and move to data to add to table

            While Not sr.EndOfStream

                Dim values() As String = Split(sr.ReadLine, ",")

                Dim rw As DataRow = dt.NewRow

                For i As Integer = 1 To values.Length - 1    'start at second column if table has a auto increment primary key

                    rw(i) = values(i).ToString

                Next

                dt.Rows.Add(rw)

            End While

            Me.DataGridView1.DataSource = dt

     

    This pariticular example will read a file named samplecsv from your applications startuppath which is the bin\debug directory in your project.  The installed applicaiton will know this as the correct location where your application is installed also.  Otherwise you can replace the application.startuppath and the file name with your exact path in quotes such as "c:\myfile.csv".  or whatever you need.  dt in this example is a datatable.  you can replace that with your dataset.table name.  Example  yourdatasetname.yourtablename.rows.add(rw).  you will also have to declare the row correctly in the same manner.  example dim rw as datarow = yourdatasetname.yourtablename.newrow.

     

     

    The split method will split the line at the commas into an array named "values" in this case.  The for loop is reading the items in the array by index.  You can also do this manually if you need to get specific values from different columns for some reason.  For example  values(0)   will get the first item in the array.   values(1) will get the second value in the array.  The indexes start at 0 so that is the reason for that.  In the comments you can see that i started the loop at 1 which is the second column value so it skips the id field.  You table will automatically add the correct id for the auto increment number for you so all you need to worry about are the other columns.  The first read line as commented is to read the very first line of the csv file which is usually the column headers.  Your table will already have the headers so you can skip this line by just calling and empty readline.  It still reads the line but we are not doing anything with it.

     

    I hope all this makes sense.

    Jeff

    Monday, August 18, 2008 3:29 PM
  • Jeff,

     

    Thanks a million - that was tremendously helpful.  I have now read in the data successfully and the auto-indexing &c is working fine.  The only small thing I had to do - and maybe I missed something in your post here - was to start each new line of the CSV file with a dummy field in place of the auto-incremented index.

     

     

    Tuesday, August 19, 2008 9:17 AM

All replies

  • What is the message of the exception and on what line of code does the exception occur? You probably want to use a Try...Catch block to handle any exceptions that occur during the import process.
    Monday, August 18, 2008 2:57 PM
  • JH,

     

    I think you can use a streamreader and the split method to do what you need.  If i understand correctly you have an auto increment primary key identity field.  Here is an example to read the lines of the csv file into an array in a loop and use only the values you need and place them into your datatable.

     

    Dim sr As IO.StreamReader = New IO.StreamReader(Application.StartupPath & "\SampleCSV.csv")

            sr.ReadLine() 'read the column header line to skip it and move to data to add to table

            While Not sr.EndOfStream

                Dim values() As String = Split(sr.ReadLine, ",")

                Dim rw As DataRow = dt.NewRow

                For i As Integer = 1 To values.Length - 1    'start at second column if table has a auto increment primary key

                    rw(i) = values(i).ToString

                Next

                dt.Rows.Add(rw)

            End While

            Me.DataGridView1.DataSource = dt

     

    This pariticular example will read a file named samplecsv from your applications startuppath which is the bin\debug directory in your project.  The installed applicaiton will know this as the correct location where your application is installed also.  Otherwise you can replace the application.startuppath and the file name with your exact path in quotes such as "c:\myfile.csv".  or whatever you need.  dt in this example is a datatable.  you can replace that with your dataset.table name.  Example  yourdatasetname.yourtablename.rows.add(rw).  you will also have to declare the row correctly in the same manner.  example dim rw as datarow = yourdatasetname.yourtablename.newrow.

     

     

    The split method will split the line at the commas into an array named "values" in this case.  The for loop is reading the items in the array by index.  You can also do this manually if you need to get specific values from different columns for some reason.  For example  values(0)   will get the first item in the array.   values(1) will get the second value in the array.  The indexes start at 0 so that is the reason for that.  In the comments you can see that i started the loop at 1 which is the second column value so it skips the id field.  You table will automatically add the correct id for the auto increment number for you so all you need to worry about are the other columns.  The first read line as commented is to read the very first line of the csv file which is usually the column headers.  Your table will already have the headers so you can skip this line by just calling and empty readline.  It still reads the line but we are not doing anything with it.

     

    I hope all this makes sense.

    Jeff

    Monday, August 18, 2008 3:29 PM
  • Jeff,

     

    Thanks a million - that was tremendously helpful.  I have now read in the data successfully and the auto-indexing &c is working fine.  The only small thing I had to do - and maybe I missed something in your post here - was to start each new line of the CSV file with a dummy field in place of the auto-incremented index.

     

     

    Tuesday, August 19, 2008 9:17 AM