如何管理我使用BULK INSERT插入到表中的数据 [英] how to manage the data I insert into a table using BULK INSERT

问题描述

我有一个 C＃应用，可将 csv 文件中的数据捕获到 SQL表

文字档案如下所示

students.csv

 标题201501 
 id代码金额
 1 ab11 5000 
 2 ab11 6000 
 3 ab11 8000 
 3 wx34 2500 
 3 df21 1000 
 4 ab11 7000 
 4 zx54 3500 
  

在它自己的单元格在csv文件。 我只是把这个，所以你可以看到什么是在文件。

基本上我必须插入这个数据到两个表，但你可以看到在 id列是具有相同数字的元素， ID是主键。

表格应如下所示：

tblStudents

 金额
 1 ab11 5000 
 2 ab11 6000 
 3 ab11 8000 
 4 ab11 7000 
  

，另一个表格应该只包含所有带有代码（不是ab11）的数据，并且应该包含 。

 标题201501 
 id代码金额月
 3 wx34 2500 201501 
 3 df21 1000 201501 
 4 zx54 3500 201501 
  

这是我目前的情况，但我似乎无法获得输出

  private void btnSelect_Click（object sender，EventArgs e）
 {
 
 
 btnNext.Enabled = true; 
 
 openFileDialog1.Filter =.csv file | * .csv *; 
 openFileDialog1.FilterIndex = 1; 
 
 DialogResult result = openFileDialog1.ShowDialog（）; 
 
 if（result == DialogResult.Yes）
 {
 String file = openFileDialog1.FileName; 
 btnNext.Enabled = true; 
 
 try 
 {
 string connect =Data Source = BRIAN-PC\\SQLEXPRESS; Initial Catalog = PSAHoldings; user id = sa; Password = kagiso; 
 
 string table =IF NOT EXISTS（SELECT * FROM sysobjects WHERE name ='t_original'AND xtype ='U'）+ 
CREATE TABLE t_original（+ 
 empId varChar（10）NOT NULL PRIMARY KEY，+ 
paycode varChar（10）NOT NULL，+ 
amount int NOT NULL，+ 
） 
 
 SqlConnection con = new SqlConnection（connect）; 
 con.Open（）; 
 
 SqlCommand createTable = new SqlCommand（table，con）; 
 createTable.ExecuteNonQuery（）; 
 
 
 String BulkInsert =BULK INSERT t_original FROM_+ 
 file +_WITH（--FIRSTROW = 3，+ 
FIELDTERMINATOR ='|'， + 
ROWTERMINATOR ='\\\\
'）; 
 
 SqlCommand bulkCmd = new SqlCommand（BulkInsert，con）; 
 bulkCmd.ExecuteNonQuery（）; 
 
} 
 catch（SqlException ex）
 {
 MessageBox.Show（ex.ToString（），Exception Error，MessageBoxButtons.OK，MessageBoxIcon.Error） ; 
} 
} 
} 
  

t表中任何东西，我的表是空的

t_original 是我必须插入学生.csv 文件，我必须跳过第一行。

任何帮助和建议都将不胜感激。

谢谢

解决方案

正如他所说，他的评论中的下划线代表空间。添加的下划线的效果是它现在是：
BULK INSERT t_original FROM_ {file} （注意缺少 / code>和文件）。对于 WITH 前面的下划线也是如此。





此外，双破折号> - ）表示注释，因此它之后的所有内容都不会被执行。

如果你调试，并获得 BulkInsert 的实际值，并执行该语句，我会很惊讶if它执行，它可能会使错误更容易找到。

I have a C# application which captures data from a csv file to a SQL table

the text file looks like this

students.csv

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

each item is in it own cell in the csv file. "I just spaced this so that you can see what is in the file".

basically I have to insert this data to two table but as you can see in the id column the are elements with the same number and id is the primary key.

the tables should look as follows:

tblStudents

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

and the other table should have all data with data only with the code that isn't "ab11" and should have the month which appears in csv file next to Header. And look as follows

tblPaid

Header 201501
id      code    amount month
3       wx34    2500   201501
3       df21    1000   201501
4       zx54    3500   201501

This is what I have at the moment but I can't seem to get a output

private void btnSelect_Click(object sender, EventArgs e)
{


    btnNext.Enabled = true;

    openFileDialog1.Filter = ".csv file|*.csv*";
    openFileDialog1.FilterIndex = 1;

    DialogResult result = openFileDialog1.ShowDialog();

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

        try
        {
            string connect = "Data Source=BRIAN-PC\\SQLEXPRESS; Initial Catalog=PSAHoldings; user id =sa; Password=kagiso";

            string table = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_original' AND xtype='U')" +
                "CREATE TABLE t_original (" +
                "empId varChar(10) NOT NULL PRIMARY KEY," +
                "paycode varChar(10) NOT NULL," +
                "amount int NOT NULL," +
                ")";

            SqlConnection con = new SqlConnection(connect);
            con.Open();

            SqlCommand createTable = new SqlCommand(table, con);
            createTable.ExecuteNonQuery();


            String BulkInsert = "BULK INSERT t_original FROM_" +
                file + "_WITH (--FIRSTROW = 3," +
                "FIELDTERMINATOR = '|'," +
                "ROWTERMINATOR = '\\n')";

            SqlCommand bulkCmd = new SqlCommand(BulkInsert, con);
            bulkCmd.ExecuteNonQuery();

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

I can't get anything into the table, My table is empty

t_original is the table I have to insert the students.csv file into and I have to skip the first to lines

Any help and advise will be appreciated

Thank you

解决方案

You took the comment of @StuartLC too literal; as he said, the underscores in his comment represent spaces. The effect of the added underscores is that it now reads: BULK INSERT t_original FROM_{file} (notice lack of space between from and file). The same goes for the underscore in front of WITH.

Furthermore, the double dash (--) represent a comment, so everything after it is not executed.

If you debug, and get the actual value of BulkInsert, and execute that statement I would be very surprised if it executes, it will probably make the errors a whole lot easier to find.

这篇关于如何管理我使用BULK INSERT插入到表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！