从 CSV 文件筛选器插入到 Sqlite C# 表中

Question

我在sqlite中创建了一个包含两个表的数据库。 我有一个非常具体的CSV文件，我必须在数据库中插入。 我需要在"Station"列中插入"hyddnev"表(站数是文件中每行的第一个元素)和"Dat"(年份是文件中每行的第二个元素，mounth 的日期是文件中每行的第三个元素 - 第一行是第一天，第二行是第 2 天到第 31 天)。几天后，我有 12 列，其中包含 1 月至 12 月的 mounths 值。我需要在 Hydnev 表中的"车站"列中插入"电台编号"，在同一表中的 Dat 列中插入日期，并在每行的 12 列中插入 12 月到十二月的值。 现在我尝试插入电台编号和日期，但我有例外：

Constraint failed
NOT NULL constraint failed: hyddnev.Dat
Constraint failed
NOT NULL constraint failed: hyddnev.Station

CSV文件包含从1976年到2015年的年份，每年有31行。

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,

我不知道如何插入到表中，我需要在使用它来绘制带有选择查询的图形之后插入。

我的代码：

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("Годините от двата файла не съвпадат.");
}
}
}
}

Answer 1

您的 CSV 文件是否有标题行？

CSV 文件中有哪些列？

如果 CSV 文件中有列标题，则可以直接将 CSV 文件加载到数据表中，然后只需查询该表即可。 请参阅：在数据表中加载CSV文件(然后在数据表上查询/应用过滤器以提取所需数据)

注意：请注意连接字符串中的"HDR = 是/否"和"数据源 = CSV 文件的路径"。

// 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;
}
}