Insert into table Sqlite C# from CSV File Filters

Question

I created a database with two tables in sqlite. I have a pretty specific CSV file which I have to do an insert in the database. The table 'hyddnev' I need to do insert in column 'Station'(Number of station is first element on each line from file) and 'Dat' (Year is second element of each line from file and day of mounth is third element of each line from file - on the first line is 1st day, second line is 2nd day to 31th day). After days I have 12 columns with values for mounths from January to December. And I need to insert the № of station in Station column in Hydnev table, Date in column Dat in the same table, and Values from January do December in the 12 columns of each line. For now I try to insert № of station and Date, but I have exception:

Constraint failed
NOT NULL constraint failed: hyddnev.Dat

Constraint failed
NOT NULL constraint failed: hyddnev.Station

CSV file contains years from 1976 to 2015 and each years have 31 lines..

18050,1976,1,0.390,0.660,0.290,0.740,9.160,1.400,0.670,3.120,0.460,0.420,0.360,0.400,
18050,1976,2,0.390,0.520,0.290,0.740,7.540,1.270,0.670,2.660,0.460,0.420,0.360,0.380,
18050,1976,3,0.390,0.450,0.240,0.660,5.260,1.270,0.670,2.510,0.460,0.420,0.410,0.400,
18050,1976,4,0.390,0.450,0.240,0.660,4.400,1.180,0.620,2.360,0.460,0.410,0.400,0.440,
18050,1976,5,0.390,0.450,0.290,0.660,4.220,1.080,0.620,2.360,0.460,0.410,0.400,4.750,
18050,1976,6,0.520,0.390,0.240,0.580,4.040,1.270,0.620,4.200,0.460,0.410,0.380,2.810,
18050,1976,7,0.390,0.390,0.240,0.520,3.680,37.800,0.620,5.870,0.460,0.400,0.360,1.620,
18050,1976,8,0.390,0.390,0.200,0.580,3.330,22.900,0.580,4.570,0.460,0.380,0.360,0.980,
18050,1976,9,0.390,0.390,0.200,0.660,2.830,11.200,0.580,4.020,0.460,0.360,0.360,0.740,
18050,1976,10,0.390,0.340,0.200,1.380,2.650,8.120,0.580,3.660,0.440,0.360,0.360,0.520,
18050,1976,11,0.340,0.390,0.200,2.260,2.350,5.870,0.580,3.270,0.440,0.360,0.360,0.460,
18050,1976,12,0.340,0.450,0.200,1.700,2.350,4.750,0.580,4.570,0.440,0.360,0.360,0.460,
18050,1976,13,0.340,0.390,0.200,1.590,2.350,3.840,0.540,4.020,0.440,0.340,0.360,0.440,
18050,1976,14,0.340,0.390,0.290,2.120,2.200,3.120,0.540,3.660,0.420,0.340,0.340,0.520,
18050,1976,15,0.290,0.390,0.290,2.400,2.050,2.970,0.540,3.270,0.420,0.400,0.340,0.520,
18050,1976,16,0.290,0.390,0.240,1.590,1.770,2.810,0.540,2.970,0.420,0.360,0.340,0.440,
18050,1976,17,0.290,0.340,0.290,1.170,1.520,2.660,0.540,2.660,0.410,0.360,0.330,0.420,
18050,1976,18,0.290,0.340,0.290,1.170,1.270,2.360,0.540,2.210,0.410,0.410,0.340,0.420,
18050,1976,19,0.240,0.340,0.390,1.170,1.080,2.210,0.540,2.060,0.410,0.410,0.400,0.410,
18050,1976,20,0.290,0.340,0.390,1.010,1.080,2.060,0.520,1.760,0.400,0.400,1.340,0.400,
18050,1976,21,0.290,0.290,0.390,0.920,1.270,1.760,0.520,1.200,0.740,0.400,2.660,0.400,
18050,1976,22,0.340,0.290,0.450,0.820,2.860,1.480,0.520,1.080,0.580,0.380,1.760,0.400,
18050,1976,23,0.340,0.290,0.520,0.740,3.050,1.200,0.520,0.980,0.580,0.380,0.980,0.400,
18050,1976,24,0.340,0.290,0.520,0.660,4.000,0.980,0.540,0.810,0.540,0.380,0.520,0.380,
18050,1976,25,0.340,0.290,0.920,0.740,2.680,0.890,2.810,0.670,0.520,0.360,0.460,0.380,
18050,1976,26,0.390,0.290,1.380,1.380,2.060,0.810,2.510,0.580,0.520,0.360,0.440,0.380,
18050,1976,27,0.740,0.290,1.490,2.570,1.770,0.810,2.510,0.580,0.490,0.360,0.420,0.380,
18050,1976,28,1.280,0.290,1.380,2.730,1.770,0.740,4.750,0.520,0.460,0.360,0.410,0.360,
18050,1976,29,1.010,0.290,1.090,3.610,1.650,0.740,5.480,0.520,0.420,0.360,0.410,0.360,
18050,1976,30,0.820,,0.820,4.000,1.520,0.670,4.210,0.490,0.420,0.360,0.400,0.360,
18050,1976,31,0.660,,0.740,,1.520,,3.660,0.460,,0.360,,0.440,

I dont know how to insert into the table, I need to insert after I will use it to draw a graphics with select query.

My Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        private SQLiteConnection Connection;

        public Form1()
        {
            InitializeComponent();

            label3.Hide();
            label4.Hide();

            SQLiteConnection.CreateFile("hydrodb.sqlite");
            SQLiteConnection Connection = new SQLiteConnection("Data Source=hydrodb.sqlite;Version=3;");
            Connection.Open();

            string createTable = ("CREATE TABLE hyddnev (Station UNSIGNED INT(5) NOT NULL, Dat datetime NOT NULL, Stoej int(5) DEFAULT NULL, Vkol UNSIGNED FLOAT(7,3) DEFAULT NULL, PRIMARY KEY (Station, Dat))");
            SQLiteCommand createHydDnev = new SQLiteCommand(createTable, Connection);
            createHydDnev.ExecuteNonQuery();

            string createTable2 = ("CREATE TABLE hydmes (Station UNSIGNED INT(5) NOT NULL, Dat datetime NOT NULL, StoejMin smallint(5) DEFAULT NULL, VkolMin UNSIGNED FLOAT(7,3) DEFAULT NULL, StoejSre smallint(5) DEFAULT NULL, VkolSre UNSIGNED FLOAT(7,3) DEFAULT NULL, StoejMax smallint(5) DEFAULT NULL, VkolMax UNSIGNED FLOAT(7,3) DEFAULT NULL, PRIMARY KEY (Station, Dat))");
            SQLiteCommand createHydMes = new SQLiteCommand(createTable2, Connection);
            createHydMes.ExecuteNonQuery();

            string deleteTable = ("DELETE FROM hyddnev");
            SQLiteCommand deleteHydDnev = new SQLiteCommand(deleteTable, Connection);
            deleteHydDnev.ExecuteNonQuery();

            string deleteTable2 = ("DELETE FROM hydmes");
            SQLiteCommand deleteHydMes = new SQLiteCommand(deleteTable2, Connection);
            deleteHydMes.ExecuteNonQuery();

            this.Connection = Connection;
        }

        string pathFolder;
        string pathFolder2;

        string resultStation;
        string resultStation2;

        List<string> resultYears = new List<string>();
        List<string> resultYears2 = new List<string>();

        private void button1_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog dialog = new OpenFileDialog())
            {
                if (dialog.ShowDialog(this) == DialogResult.OK)
                {
                    string sFileName = dialog.FileName;
                    pathFolder = sFileName;

                    label3.Text = pathFolder;
                    label3.Show();                 

                    string[] lines = System.IO.File.ReadAllLines(dialog.FileName);

                    int i = 0;

                    foreach (var line in lines)
                    {

                        var splittedValues = line.Split(',');

                        var firstWord = splittedValues[0];
                        var firstYear = splittedValues[1];

                        if (!resultYears.Contains(firstYear))
                        {
                            resultYears.Add(firstYear);
                        }

                        if (i == 0)
                        {
                            resultStation = firstWord;
                        }
                        else
                        {
                            if (resultStation != firstWord)
                            {
                                MessageBox.Show("Файла с дневни данни трябва да съдържа само една станция!");
                                return;
                            }
                        }
                        i++;

                        string addDat = ("INSERT INTO hyddnev (Dat) values ('" + resultYears + "')");
                        SQLiteCommand insertDat = new SQLiteCommand(addDat, Connection);

                        try
                        {
                            insertDat.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {

                            MessageBox.Show(ex.Message);
                        }

                        string addStation = ("INSERT INTO hyddnev (Station) values(" + firstWord + ")");
                        SQLiteCommand insertStation = new SQLiteCommand(addStation, Connection);

                        try
                        {
                            insertStation.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {

                            MessageBox.Show(ex.Message);
                        }

                    }
                    resultYears.Sort();
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog dialog = new OpenFileDialog())
            {
                if (dialog.ShowDialog(this) == DialogResult.OK)
                {
                    string sFileName = dialog.FileName;
                    pathFolder2 = sFileName;

                    label4.Text = pathFolder2;
                    label4.Show();

                    string[] lines = System.IO.File.ReadAllLines(dialog.FileName);

                    int i = 0;

                    foreach (var line in lines)
                    {
                        var splittedValues = line.Split(',');

                        var firstWord = splittedValues[0];
                        var firstYear2 = splittedValues[1];

                        if (!resultYears2.Contains(firstYear2))
                        {
                            resultYears2.Add(firstYear2);
                        }

                        if (i == 0)
                        {
                            resultStation2 = firstWord;
                        }
                        else
                        {
                            if (resultStation2 != firstWord)
                            {
                                MessageBox.Show("Файла с месечни данни трябва съдържа само една станция!");
                                return;
                            }
                        }

                        i++;
                    }

                    resultYears2.Sort();
                }
            }
        }

        public void label3_Click(object sender, EventArgs e)
        {

        }

        public void label4_Click(object sender, EventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (resultStation != resultStation2)
            {
                MessageBox.Show("Номера на станцията в единия файл не отговаря на номера на станцията в другият файл!" + Environment.NewLine + Environment.NewLine +
                    "ЗАБЕЛЕЖКА!" + Environment.NewLine + Environment.NewLine + "В двата файла, номера на станцията трябва да бъде един и същ!");
            }

            comboBox1.Items.Add(resultStation);

            if (string.Join(", ", resultYears) == string.Join(", ", resultYears2))
            //if (resultYears.Equals(resultYears2))
            {
                for (int i = 0; i < this.resultYears.Count; i++)
                {
                    comboBox2.Items.Add(resultYears[i]);
                }
            }
            else
            {
                MessageBox.Show("Годините от двата файла не съвпадат.");
            }

        }
    }
}

score 0 · Answer 1 · edited May 23 '17 at 11:46

Does your CSV file has header rows?

What are the columns in your CSV file?

If you have columns headers in CSV file, you can directly load CSV file to the data table and then you can simply query that table. See this: Loading CSV file in data table (then query/apply filters on data table to extract required data)

NOTE: Be careful about the "HDR = Yes/NO" and "Data Source=Path to CSV file" in connection string.

// using System.Data;
// using System.Data.OleDb;
// using System.Globalization;
// using System.IO;

static DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader)
{
    string header = isFirstRowHeader ? "Yes" : "No";
    string pathOnly = Path.GetDirectoryName(path);
    string fileName = Path.GetFileName(path);
    string sql = @"SELECT * FROM [" + fileName + "]";
    using(OleDbConnection connection = new OleDbConnection(
          @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + 
          ";Extended Properties=\"Text;HDR=" + header + "\""))
    using(OleDbCommand command = new OleDbCommand(sql, connection))
    using(OleDbDataAdapter adapter = new OleDbDataAdapter(command))
   {
     DataTable dataTable = new DataTable();
     dataTable.Locale = CultureInfo.CurrentCulture;
     adapter.Fill(dataTable);
     return dataTable;
   }
}

File not have header row, and the columns are separated with , — Валерия Благовест, Apr 13 '17 at 08:34
Okay, But you must be assuming the fixed columns and sequence data of the data separated with ",". You should chose HDR=NO (as you don't have first row as column header), Also you can assign names to the columns while populating CSV into data table. Let me know if you need further explanation — Ketan, Apr 13 '17 at 08:42

Insert into table Sqlite C# from CSV File Filters

1 Answers1