Trying to read a CSV file, but values wont show in SQL database

Question

I am reading a CSV file, but it only makes the SQL table with 2 column (ID and test)`but it wont fill those column with values from the CSV file. Here is the code I got:

    public void GetDataTabletFromCSVFile2(string csv_file_path, string tablenaam)
    {
        string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString;
        using (SqlConnection dbConnection = new SqlConnection(cn))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = dbConnection;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = $@"CREATE TABLE  test (  
                [ID] INT IDENTITY (1, 1) NOT NULL, 
                [testingcolumn] VARCHAR (1023) NULL,
                CONSTRAINT [PK_{test}] PRIMARY KEY CLUSTERED ([ID] ASC) 
                )";
                try
                {
                    dbConnection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    MessageBox.Show(e.Message.ToString(), "Error Message");
                }
                finally
                {
                    dbConnection.Close();
                }
            }
        }


        string line;
        System.Data.DataTable csvData = new System.Data.DataTable();
        // Read the file and display it line by line.
        System.IO.StreamReader file = new System.IO.StreamReader(csv_file_path);
        while ((line = file.ReadLine()) != null)
        {
            DataRow newRow = csvData.NewRow();
            csvData.Rows.Add(newRow);
        }

        file.Close();
        InsertDataIntoSQLServerUsingSQLBulkCopy2(csvData, tablenaam);
    }


    static void InsertDataIntoSQLServerUsingSQLBulkCopy2(System.Data.DataTable csvFileData, string Tablename)
    {
        string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString;
        using (SqlConnection dbConnection = new SqlConnection(cn))
        {
            dbConnection.Open();
            string sqlTrunc = "TRUNCATE TABLE " + Tablename;
            SqlCommand cmd = new SqlCommand(sqlTrunc, dbConnection);
            cmd.ExecuteNonQuery();
            using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
            {
                s.ColumnMappings.Clear();
                s.DestinationTableName = Tablename;
                foreach (var column in csvFileData.Columns)
                    s.ColumnMappings.Add(column.ToString(), column.ToString());
                s.WriteToServer(csvFileData);
                dbConnection.Close();
            }
        }
    }

My SQL table looks like this now:

------------
| ID | test|
------------
|null|null |
------------

While it should look like:

-------------
| ID | test |
-------------
|1   |value1|
-------------
|2   |value2|
-------------
|3   |value3|
-------------

EDIT: this didnt work either:

        string line;
        System.Data.DataTable csvData = new System.Data.DataTable();
        // Read the file and display it line by line.
        System.IO.StreamReader file = new System.IO.StreamReader(csv_file_path);
        int i = 0;

        DataColumn datecolumn = new DataColumn("ID");
        datecolumn.AllowDBNull = true;
        csvData.Columns.Add(datecolumn);

        DataColumn datecolumn2 = new DataColumn("RunTimeGroupCheck");
        datecolumn.AllowDBNull = true;
        csvData.Columns.Add(datecolumn2);

        while ((line = file.ReadLine()) != null)
        {
            var id = (i++); //Code this method
            var test = (line); //Code this method
            csvData.Rows.Add(id, test);
        }

        file.Close();
        InsertDataIntoSQLServerUsingSQLBulkCopy2(csvData, tablenaam);

Answer 1

You need proper data types, and proper insertion.

public void GetDataTabletFromCSVFile2(string csv_file_path, string tablenaam)
{
    string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString;
    using (SqlConnection dbConnection = new SqlConnection(cn))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = dbConnection;
            cmd.CommandType = CommandType.Text;
            // check if You really want test for table name, not tablenaam
            // mind that the column name is same as the DataTable's column name!!!!
            cmd.CommandText = $@"CREATE TABLE  test (
        [ID] INT IDENTITY (1, 1) NOT NULL, 
        [RunTimeGroupCheck] VARCHAR (1023) NULL, 
        CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([ID] ASC) 
        )";
            try
            {
                dbConnection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Error Message");
            }
            finally
            {
                dbConnection.Close();
            }
        }
    }


    string line;
    System.Data.DataTable csvData = new System.Data.DataTable();
    DataColumn firstColumn = new DataColumn("ID", typeof(int));
    firstColumn.AutoIncrement = true; // This is the thing that enables You to leave the ID column. It will autoincrement.
    firstColumn.AutoIncrementSeed = 1;
    firstColumn.AutoIncrementStep = 1;
    csvData.Columns.Add(firstColumn);
    csvData.Columns.Add(new DataColumn("RunTimeGroupCheck", typeof(string)));
    // Read the file and display it line by line.
    System.IO.StreamReader file = new System.IO.StreamReader(csv_file_path);
    while ((line = file.ReadLine()) != null)
    {
        DataRow newRow = csvData.NewRow();
        // missing filling of data. You need the line to be put somewhere.
        // also mind, that the newRow["ID"] is not set to anything.
        newRow["RunTimeGroupCheck"] = line;
        csvData.Rows.Add(newRow);
    }

    file.Close();
    InsertDataIntoSQLServerUsingSQLBulkCopy2(csvData, tablenaam);
}


static void InsertDataIntoSQLServerUsingSQLBulkCopy2(System.Data.DataTable csvFileData, string Tablename)
{
    string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString;
    using (SqlConnection dbConnection = new SqlConnection(cn))
    {
        dbConnection.Open();
        string sqlTrunc = "TRUNCATE TABLE " + Tablename;
        SqlCommand cmd = new SqlCommand(sqlTrunc, dbConnection);
        cmd.ExecuteNonQuery();
        using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
        {
            s.ColumnMappings.Clear();
            s.DestinationTableName = Tablename;
            foreach (var column in csvFileData.Columns)
                s.ColumnMappings.Add(column.ToString(), column.ToString());
            s.WriteToServer(csvFileData);
            dbConnection.Close();
        }
    }
}

Answer 2

You are forgetting to populate the datarow you are adding, you are createing your datarow correctly DataRow newRow = csvData.NewRow();, but you have to populate with the info you have in line.

while ((line = file.ReadLine()) != null)
{
    DataRow newRow = csvData.NewRow();
    //DataRow is empty
    csvData.Rows.Add(newRow);
}

Try this

while ((line = file.ReadLine()) != null)
{
    var id = extractIdFromLine(line); //Code this method
    var test= extractTestFromLine(line); //Code this method
    csvData.Rows.Add(id, test);
}

Check this oficial post

Answer 3

When reading the CSV, you are not doing anything with the data read, so your DataTable is effectively empty:

 // Read the file and display it line by line.
        System.IO.StreamReader file = new System.IO.StreamReader(csv_file_path);
        while ((line = file.ReadLine()) != null)
        {
            DataRow newRow = csvData.NewRow();
            // HERE: YOU ARE MISSING PARSING line INTO newRow
            csvData.Rows.Add(newRow);
        }