将具有大量列标题的Excel文件导入到mysql中使用c＃ [英] Importing Excel files with a large number of columns header into mysql with c#

问题描述

我只是想知道，我如何使用c＃将大型excel文件导入mysql？我的编码经验不是很好，我希望如果有人在那里谁可以给我一些粗略的想法开始。到目前为止，我能够加载excel文件到datagridview与以下代码：

  string PathConn =Provider = Microsoft.JET .OLEDB.4.0;数据源=+ pathTextBox.Text +;扩展属性= \Excel 8.0; HDR =是; \;; 
 OleDbConnection conn = new OleDbConnection（PathConn）; 
 conn.Open（）; 
 OleDbDataAdapter myDataAdapter = new OleDbDataAdapter（Select * from [+ loadTextBox.Text +$]，conn）; 
 table = new DataTable（）; 
 
 myDataAdapter.Fill（table）; 
  

但我不知道如何提取信息并将其保存到mysql数据库。假设我有一个之前创建的空方案，我如何工作上传excel文件到mysql？谢谢。

解决方案

在您的中间，您已从Excel电子表格中获取信息，并将其存储在 DataTable <强>。

在将大量数据导入SQL之前，您需要做的第一件事是验证您从电子表格中读取的内容。

您有几个选项，其中一个选项与您在数据中阅读的方式非常相似，并且使用 SQLAdapter 来执行 INSERT 添加到SQL数据库中。在这种情况下，你真正需要做的是创建一个新的连接并写入 INSERT 命令。

有很多例子

/rel =nofollow> http://linqtocsv.codeplex.com/ ）。

使用此功能，您可以将所有数据加载到类对象，以便在将 INSERT 添加到 SQL 之前更容易验证每个对象。

如果您的经验有限，请使用SQLAdapter连接到您的数据库。

>

i just was just wondering, how do i import large excel files into mysql with c#? My coding experience isn't great and i was hoping if there's anyone out there who could give me some rough idea to start on it. So far, i was able to load excel files into datagridview with the following codes:

string PathConn = " Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + pathTextBox.Text + ";Extended Properties =\"Excel 8.0;HDR=Yes;\";";
OleDbConnection conn = new OleDbConnection(PathConn);
conn.Open();
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" + loadTextBox.Text + "$]", conn);
table = new DataTable();             

myDataAdapter.Fill(table);    

but after that, i don't know how i could extract the information and save it into mysql database. Assuming i have a empty scheme created before, how do i work on uploading excel files into mysql? thanks.

解决方案

Your halfway there, You have obtained the information from the Excel spreadsheet and have it stored in a DataTable.

The first thing you need to do before you look to import a significant amount of data into SQL is validate what you have read in from the spreadsheets.

You have a few options, one of which is do something very similar to how you read in your data and that is use a SQLAdapter to perform am INSERT into the SQL Database. All your really needing to do in this case is create a new connection and write the INSERT command.

There are many example of doing this on here.

Another option which i would use, is LINQ to CSV (http://linqtocsv.codeplex.com/).

With this you can load all of your data into class objects which makes it easier to validate each object before you perform your INSERT into SQL.

If you have limited experience then use the SQLAdapter to connect to you DB.

Good Luck

这篇关于将具有大量列标题的Excel文件导入到mysql中使用c＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！