将csv文件导入C＃windows应用程序中的数据库SQL服务器，不带标头

问题描述

您好我尝试将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();
                }

            }
        }

       
    }
}

推荐答案

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服务器，不带标头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！