Import a CSV file to the database

1. Introduction to CSV files

The full name of CSV is comma separated values. It is a plain text file format used to store data and is usually used for workbooks or database software. In this way, you will find that CSV is actually a plain text file, which can be opened in notepad. However, because it generally represents a table, it is opened in Excel by default in Windows.

The rules are as follows:

It must start with "no blank" and start with "behavior unit.

• The column name can be included or not included, and the column name is the first row of the file. (This should be determined during insertion. If the first row is a column name, you need to discard the first row of data)
• A row of data is not broken, and there are no empty rows.
• Use the [,] symbol as the separator. If the column is empty, its existence must be indicated.
• If the column content contains [,], use double quotation marks [""] to include the field content.
• If the column content contains ["], double quotation marks [" "], this field content is included, and two double quotation marks are used in the content to represent a double quotation mark [" "].
• Quotation marks are used to read and write files.
• The internal code format is not limited. It can be ASCII, Unicode, or other.

2. Insert a database

Inserting a CSV file into a database is a common requirement. The simplest practice is to directly read the CSV file and insert it into the database after one row is read and split. Of course, this method seems simple, but it is difficult to solve the write problem. For example, if the column content has a [,] problem, it is difficult to judge this situation using this method. Therefore, this method is generally not recommended for data insertion.

The method I introduced today is to use Microsoft ActiveX Data Objects:

(1) Introduce Microsoft ActiveX Data Objects

Right-click the "Reference" -- com tab -- Microsoft ActiveX Data Objects. My computer is in version 6.0. In actual use, the difference between Chinese versions is not big, and the code can be used universally.

 

 

(2) code:

/// <Summary> /// import the CSV file to the database /// </Summary> /// <Param name = "strcsvfile"> name of the imported file </Param> /// <Param name = "strpathtotextfile"> file storage path </param> Public void test (string strcsvfile, string strpathtotextfile) {connection adocsvconnection = NULL; recordset adocsvrecordset = NULL; try {adocsvconnection = new connection (); adocsvrecordset = new recordset (); adocsvconnection. open (string. format ("provider = Microsoft. jet. oledb.4.0; Data Source = {0}; extended properties = \ "text; HDR = yes; FMt = delimited \" ", strpathtotextfile); adocsvrecordset. open (string. format ("select * from {0}", strcsvfile), adocsvconnection); While (! Adocsvrecordset. EOF) {for (INT I = 0; I <adocsvrecordset. Fields. Count; I ++) {var strsource = adocsvrecordset. Fields [I]. value;
// You understand it here... ^_^} adocsvrecordset. movenext () ;}} catch (exception ex) {} finally {If (adocsvrecordset! = NULL) {adocsvrecordset. Close () ;}if (adocsvconnection! = NULL) {adocsvconnection. Close ();}}}

This method uses CSV files as a database. You can see that there is a connection string "provider = Microsoft. jet. oledb.4.0; Data Source = {0}; extended properties = \ "text; HDR = yes; FMt = delimited \"".

 

There are still many factors to consider when inserting CSV files into the database. For example, multithreading is required for large volumes of data, and the webform delegate callback function cannot be used in MVC to obtain the UI thread for data change. These questions will be written later.