How to write data from the CSV file to the SQL table?

advertisements

I have a C# windows application which I am writing and I have a can't seem to figure out how to insert data from a CSV file to a Database.

I have a csv file named "data.csv" with records as follows

Header 201501
id      code    amount
1       ab11    5000
2       ab11    6000
3       ab11    8000
3       wx34    2500
3       df21    1000
4       ab11    7000
4       zx54    3500

and I have to get this data to a sql table to look as follows

ab11 represents fees to be paid and any other code represent the amount which the student has paid

first table tblStudents

id      code    amount
1       ab11    5000
2       ab11    6000
3       ab11    8000
4       ab11    7000
5       ab11    9000

second table tblPaid

id     code  Paid  Month
3       wx34    2500  201401
3       df21    1000  201401
4       zx54    3500  201401

basically what I have to do is capture this data from the data.csv text file and sort it to a table to look like tblStudents and tblPaid.

for the tblStudents I have to take everything the code "ab11" and put write to table.

for the tblPaid I only have to write only data which doesn't have the code of "ab11" and put in a field that reads the date next to the header on the data.csv text file.

I was thinking of using a BULK INSERT but the header shouldn't not be in the table.

So for I can only get to create the table and read the data.csv text file as follows

        //Open File Dialog to open csv file
        //Only get csv files
        openFileDialog1.Filter = ".csv file|*.csv*";
        openFileDialog1.FilterIndex = 1;

        DialogResult result = openFileDialog1.ShowDialog();

        if (result == DialogResult.Yes)
        {
            String file = openFileDialog1.FileName;

            //Read csv text file to get the month in the first line
            using (var reader = new StreamReader(file))
            {
                string firstLine = reader.ReadLine();
                string[] splitedLine = firstLine.Split(';');
                string month = splitedLine[1];
            }
            try
            {
                //String with connection information of the database PSAHoldings
                string connect = "Data Source=BRIAN-PC\\SQLEXPRESS; Initial Catalog=PSAHoldings; user id =sa; Password=kagiso";

                //String Query to create t_original table if it does not exist already
                string table = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_original' AND xtype='U')" +
                    "CREATE TABLE t_original (" +
                    "empId varChar(10) NULL," +
                    "paycode varChar(10) NULL," +
                    "amount int NULL," +
                    ")";

                //String Query to create tblStudents table if it does not exist already
                string tblStudents = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_employee' AND xtype='U')" +
                    "CREATE TABLE t_employee (" +
                    "empId varChar(10) NOT NULL PRIMARY KEY," +
                    "paycode varChar(10) NULL," +
                    "amount int NULL," +
                    ")";

                //String Query to create tblPaid table if it does not exist already
                string Paid = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_deduction' AND xtype='U')" +
                    "CREATE TABLE t_deduction (" +
                    "empId varChar(10) NOT NULL PRIMARY KEY," +
                    "amount int NULL," +
                    "balance int NULL," +
                    ")";

                //String Query to create t_institutions table if it does not exist already
                string t_institutions = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_institutions' AND xtype='U')" +
                    "CREATE TABLE t_institutions (" +
                    "empId varChar(10) NOT NULL PRIMARY KEY," +
                    "paycode varChar(10) NOT NULL," +
                    "amount int NULL," +
                    ")";

                //Connecting to the server/database
                SqlConnection con = new SqlConnection(connect);
                con.Open();

                //Create the tables
                SqlCommand createTable = new SqlCommand(table, con);
                SqlCommand createEmployee = new SqlCommand(t_employee, con);
                SqlCommand createDeductions = new SqlCommand(t_deduction, con);
                SqlCommand createInstitution = new SqlCommand(t_institutions, con);

                createTable.ExecuteNonQuery();
                createEmployee.ExecuteNonQuery();
                createDeductions.ExecuteNonQuery();
                createInstitution.ExecuteNonQuery();

                //String Query to insert the text file into the t_original table
                String BulkInsert = "BULK INSERT t_original FROM_" +
                    file + "_WITH (--FIRSTROW = 3," +
                    "FIELDTERMINATOR = ''," +
                    "MAXERRORS = 0," +
                    "ROWTERMINATOR = '\\n')";
                //"UPDATE t_original"+
                //"SET month =" + month +
                //"WHERE month is null";

                //insert the text file into the t_original table
                SqlCommand bulkCmd = new SqlCommand(BulkInsert, con);
                bulkCmd.ExecuteNonQuery();

            }
            //Catch exeption
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString(), "Exception Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

I have writen a BULK_INSERT but can't get anything into my table

Any help will be appreciated as I am a beginner in development


First, you only need to create the tables in the database once. therefor, move the entire code block that creates them out of the loop. Second, for each line you read from the csv you need to find out if it belongs to the students table or to the paid table. something along these lines:

string[] DataArray = line.split(',');
  if(DataArray.Length==3) // this is an actual data line
  {
    if(DataArray[1] == "ab11")
    {
    // student row
    }
    else
    {
     // Paid row
    }
  }