[Translation] Read text files (txt, csv, log, tab, fixed length) (on)

Description

Http://www.codeproject.com/KB/database/ReadTextFile.aspx? Display = print

We occasionally try to read and process data from the local file (.txt,.csv,. Tab. The most common method is to use streamreader (. NET) to read files row by row.

Suppose we can read files as a database and process data using a Table query, we will find many shortcomings in the above method:

1. Connect to the environment. Lock the file until the processing is completed.

2. We need to split each row to obtain the data of the independent column. It is difficult to process rows with commas (,) as part of the data.

Example: "Yes, comma is here", 2, "hello, another comma", 6

3. We cannot use conditions to filter data until we read rows and start processing.

4. to calculate the number of records in a file or calculate the number of records of a specific type, you need to read the entire file.

We can list so many shortcomings. All the advantages of Data Table query can make up for the shortcomings of Row-by-row reading.

Can we read text files as database tables?

We do not need to discuss this topic, if the answer is no. Of course, we can read files as data tables and we will easily overcome all the shortcomings mentioned above.

Read data

It is as simple as connecting to a database and querying data from a data table. In this example, we consider the file as a table and the folder containing the file as a database.

To read data, follow these steps:

1. Open the database connection

Note:: It is very important to connect strings here. Depending on the type of the file to be read, it changes. We will discuss it later in this article.

2. Obtain the result using the basic query language

3. Read fields by repeating the results.

Code

DataSet myData = new DataSet ();
String myXML;
String strFilePath = "C :\\";
String mySelectQuery = "SELECT * FROM SampleFile. CSV ";
OleDbConnection myConnection = new OleDbConnection
("Provider = Microsoft. Jet. OLEDB.4.0; Data Source = D :\\;" +
"Extended Properties = \" text; HDR = YES; FMT = Delimited \"");
OleDbDataAdapter dsCmd = new OleDbDataAdapter (mySelectQuery, myConnection );
// Fill the DataSet object
DsCmd. Fill (myData, "CustomerOwners ");
// Create a XML document with the table data
Mydata. writexml ("D: \ testxml. xml ");
Myconnection. Close ();

We read and upload text files to the database as a database. Let's take a look at the advantages of this method:

1. The process is fast.

2. The first thing we need to pay attention to is how to process data rows and split data. If you have a comma (,) in one row as part of the text (the second vertex is not listed above), the process automatically handles this situation. We do not need to process it separately.

3. You can select the specified row set from the file.

Example: "SELECT * FROM SampleFile1.CSV WHERE Number> = 3"

4. At this time, it is easy to understand that we can also use a very helpful WHERE, Having or GROUP By statement in the query.

5. Once we read all the data in the file, we can filter the data as needed. You can use the RowFilter attribute or DataView. As I said, once we read data to DataSet, we can perform all the operations we can do. This includes filtering and sorting.