在没有标题的C＃Windows应用程序中将csv文件导入SQL Server数据库 [英] Import csv file into SQL Server database in C# Windows application without header

问题描述

我正在尝试将 .csv 文件导入到没有标题的SQL Server数据库中。

I am trying to import a .csv file into a SQL Server database without the headers.

这代码工作正常，但有标题 - 如何跳过标题？我需要更改代码以忽略 .csv 文件中的标题？

This code works fine, but with headers - how can skip the headers? What do I need to change in the code to ignore the headers from the .csv file?

谢谢大家。

CSV文件格式：

001,0000002226,01,2011/03/27,07:07,        
001,0000009392,01,2011/03/27,07:12,      
001,0000002220,01,2011/03/27,07:17,        
001,0000002121,01,2011/03/27,07:19, 

C＃代码：

private void btnBrowse_Click(object sender, EventArgs e)
{
    OpenFileDialog ofd = new OpenFileDialog();
    ofd.DefaultExt = ".csv";
    ofd.Filter = "Comma Separated (*.csv)|*.csv" ;
    ofd.ShowDialog();

    txtFileName.Text = ofd.FileName;
}

private void btnClose_Click(object sender, EventArgs e)
{
    this.Close();
}

private void btnimport_Click(object sender, EventArgs e)
{
    Cursor = Cursors.WaitCursor;

    DataTable imported_data = GetDataFromFile();  

    if (imported_data == null) 
       return;           

    SaveImportDataToDatabase(imported_data);    

    MessageBox.Show("load data succ.....!");
    txtFileName.Text = string.Empty;

    Cursor = Cursors.Default;
}

private DataTable GetDataFromFile()
{
    DataTable importedData = new DataTable();

    try
    {
        using (StreamReader sr = new StreamReader(txtFileName.Text))
        {
            string header = sr.ReadLine();

            if (string.IsNullOrEmpty(header))
            {
                MessageBox.Show("no file data");
                return null;
            }

            string[] headerColumns = header.Split(',');

            foreach (string headerColumn in headerColumns)
            {
                importedData.Columns.Add(headerColumn);
            }

            while (!sr.EndOfStream)
            {
                string line = sr.ReadLine();

                if (string.IsNullOrEmpty(line)) 
                   continue;

                string[] fields = line.Split(',');
                DataRow importedRow = importedData.NewRow();

                for(int i = 0; i < fields.Count(); i++)
                {
                    importedRow[i] = fields[i];
                }

                importedData.Rows.Add(importedRow);
            }
        }
    }
    catch (Exception e)
    {
        Console.WriteLine("the file could not be read:");
        Console.WriteLine(e.Message);
    }

    return importedData;
}

private void SaveImportDataToDatabase(DataTable imported_data)   
{
    using (SqlConnection conn = new SqlConnection("Data Source=HA-PC\\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"))
    {
        conn.Open();

        foreach (DataRow importRow in imported_data.Rows)   
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO imported_data (device_id, employee_id, status, date, time) " +
                                            "VALUES (@device_id, @employee_id, @status, @date, @time)", conn);

            cmd.Parameters.AddWithValue("@device_id", importRow["device_id"]);
            cmd.Parameters.AddWithValue("@employee_id", importRow["employee_id"]);
            cmd.Parameters.AddWithValue("@status", importRow["status"]);
            cmd.Parameters.AddWithValue("@date", importRow["date"]);
            cmd.Parameters.AddWithValue("@time", importRow["time"]);

            cmd.ExecuteNonQuery();
        }
    }
}

推荐答案

从文件中读取数据时，只需忽略标题。删除这些行：

Just ignore the headers when you read the data from the file. Delete these lines:

string[] headerColumns = header.Split(',');

foreach (string headerColumn in headerColumns)
{
    importedData.Columns.Add(headerColumn);
}

这篇关于在没有标题的C＃Windows应用程序中将csv文件导入SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！