如果列名和顺序不同,如何从csv将数据插入到db [英] How to insert data from csv to access db if the column names and order is different
问题描述
它使用c#编程。
我选择csv文件并将其插入到访问db。我说,在csv文件中的6列,如ID,FName,LName,Address,Zipcode,单元格号以相同的顺序。在访问中,我的列是FirstName,LastName,S_NO,zip_code(以相同的顺序)。如何导入此csv文件到访问db,如果列名称不同,以及顺序不同。如何实现这个?建议。
我尝试下面的代码:
在这里输入代码
pre>
DataSet da = new DataSet();
try
{da = this.ConnectCSV(strCSVFile);
string connstring =Provider = Microsoft.ACE.OLEDB.12.0;数据源= 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,指定
+)值(+(i + 1)+,'
+ da.Tables [Ss]。Rows [i] .ItemArray.GetValue )+',
+ 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;
}
在此处输入代码
解决方案使用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]
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
解决方案With MS Access, you can run straight from CSV to a table:
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]
这篇关于如果列名和顺序不同,如何从csv将数据插入到db的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!