将csv文件导入C＃windows应用程序中的数据库SQL服务器，不带标头 [英] Import csv file to database SQL server in C# windows application without header

问题描述

您好我尝试将csv文件导入到数据库sql server而没有标题这个代码工作正常但是有了标题怎么可以跳过标题删除或忽略的代码将csv导出csv带有标题到c＃windows应用程序中的数据库我的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，



我的尝试：

hi i try import csv file to database sql server without header this code work good but with header how can skip the header what the code that delete or ignore to import csv with out header to database in c# windows application my csv format below . thank you all.

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,

What I have tried:

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

namespace ImportFileTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        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();
                }

            }
        }

       
    }
}

推荐答案

创建DataTable时，由于csv文件没有标题记录，因此需要提供列名称。由于您没有这样做，然后随后按列名称调用DataTable行字段，因此每个字段返回的值为null，因为DataTable不包含该名称的列。



以下是我将如何修复它：

When you create the DataTable, since your csv file does not have a header record, you need to supply the column names. Since you didn't do this, and then subsequently call the DataTable row fields by their column names, the value returned for each field is null since the DataTable doesn't contain a column by that name.

Here is how I would fix it:

private DataTable GetDataFromFile()
{

    DataTable importedData = new DataTable();
    string header = "device_id,employee_id,status,date,time";

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

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

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