如何插入从CSV数据访问数据库，如果列名和顺序是不同的(How to insert data fro

It is using c# programming. I am selecting the csv file and inserting it in to the access db. I have say, 6 columns in the csv file like ID, FName, LName, Address, Zipcode, cell number in the same order. In access, my columns are FirstName, LastName, S_NO, zip_code (in the same order). How i can import this csv file in to access db, if the column names are different as well the order is different. How can i implement this?? Pls suggest. I tried the following code:

enter code here

DataSet da = new DataSet(); try {

            da = this.ConnectCSV(strCSVFile);
            string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\EmpApp\\EmpData.accdb;";
            OleDbConnection conn = new OleDbConnection(connstring);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.Text;
            conn.Open();
            for (int i = 0; i <= da.Tables["Ss"].Rows.Count - 1; i++)
            {

                for (int j = 1; j <= da.Tables["Ss"].Columns.Count - 1; j++)
                {
                    cmd.CommandText = "Insert  into EMP_DOWNLOAD ( ID,Company_name,month_billed,year,start_date,end_date,Designation"
                      + ")  values(" + (i + 1) + ",'" 
                      + da.Tables["Ss"].Rows[i].ItemArray.GetValue(0) + "',"
                      + da.Tables["Ss"].Rows[i].ItemArray.GetValue(8) + 
                      da.Tables["Ss"].Rows[i].ItemArray.GetValue(9) + ")";                        
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                 }
            }
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            btnUpload.Enabled = false;
        }
enter code here

Answer 1:

随着MS Access，您可以从CSV直线延伸到表：

INSERT INTO EMP_DOWNLOAD (ID, Forename, LastName, Address, Zip_Code ) 
SELECT  ID, FName, LName, Address, Zipcode
FROM [Text;Database=z:\docs\;HDR=yes].[importfilename.csv]

Answer 2:

我会用一个LINQ语句来打散文本并创建参数对象的数组，然后通过阵列添加参数发送到数据库的每个插入语句循环。

像这样

var csvParams = (from line in System.IO.File.ReadLines("fileName.csv")
            from cell in line.Split(',').AsEnumerable()
            select new[]
            {
                new OleDbParameter("@fn", cell[1]),
                new OleDbParameter("@ln", cell[2]),
                new OleDbParameter("@sn", cell[3]),
                new OleDbParameter("@zc", cell[4])
            });

using( OleDbConnection conn = new OleDbConnection("CONNECTION STRING GOES HERE"))
{
    foreach (var person in csvParams)
    {
        using (OleDbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO People(FirstName, LastName, S_NO, zip_code) VALUES (@fn, @ln, @sn, @zc)";
            cmd.Parameters.AddRange( (OleDbParameter[]) person);
            cmd.ExecuteNonQuery();
        }

    }
}