CSV file reading

This is because many different databases, such as Oracle, access, MSSQL, MySQL, and DB2, are involved in the work.

Therefore, I have always wanted to create a common data query tool that supports all databases. The operation interface is a uniform interface of MSSQL 2005, so that many client tools can be installed less, you do not need to switch between different database client tools.

Put an initial one. Like the MSSQL operation interface, the table and column information display area is on the left, the SQL statement input area is on the top right, and the result display area is on the bottom right.

Supports automatic table and field prompts and CSV and Excel Import and Export

PS: The connected database in the figure is IBM DB2 9.5

 

 

When importing and exporting data, the most basic CSV file format must be supported. At that time, I thought CSV is not simple. It is nothing more than comma-separated. Should I press enter to wrap the text file, I wrote it soon.

Initial CSV reading Public   Static   String [] [] Read_csv ( String Text)
{
VaR text_array =   New List < String [] > ();
String [] Lines = Text. Split ( ' \ R ' , ' \ N ' );
Foreach (VAR line In Lines)
{
If ( ! String . Isnullorempty (line ))
{
String [] Fields = Line. Split ( ' , ' );
Text_array.add (fields );
}
}
Return Text_array.toarray ();
}

Public     Text)
{
  Lines)
. Isnullorempty (line ))
);
Text_array.add (words );
}
Text_array.toarray ();

At first, everything went well and there was no problem. Later I used more, and the problem began to appear. I found that some CSV fields contain commas, if you encounter a comma, it will be treated as another field. Of course, an error will be reported. In the past, the CSV file was so despised. In fact, CSV files also have some specifications that need attention.

1. If the CSV file field contains special characters, the entire field should be enclosed in double quotation marks.

There are three special characters: comma [,], carriage return, and line feed [\ r \ n], and double quotation marks at the beginning of the field ["]

For example, fields a, B, c (B, c contain commas ),

It should be a, "B, c", d

The same is true if there is a carriage return or line feed.

2. If the CSV field contains special characters and the field contains double quotation marks, the double quotation marks in the field should be written twice.

Example: field a, B, c "AA, d

It should be a, "B, c" "AA", d

 

With these two specifications, it is not complicated to modify the read method again.

CSV read after modification
Public   Static   String [] [] Read_csv ( String Text)
{
If (Text =   Null )
Return   Null ;
VaR text_array =   New List < String [] > ();
VaR line =   New List < String > ();
VaR Field =   New Stringbuilder ();
// Whether it is in double quotation marks
Bool In_quata =   False ;
// Start field?
Bool Field_start =   True ;
For ( Int I =   0 ; I < Text. length; I ++ )
{
Char Ch = Text [I];
If (In_quata)
{
// If it is already within the double quotation mark range
If (CH =   ' \" ' )
{
// If there are two quotation marks, it is treated as a normal quotation mark.
If (I < Text. Length -   1   && Text [I +   1 ] =   ' \" ' )
{
Field. append ( ' \" ' );
I ++ ;
}
Else
// Otherwise, exit the quotation mark range.
In_quata =   False ;
}
Else   // Any character (except double quotation marks) within the double quotation marks is treated as a common character.
{
Field. append (CH );
}
}
Else
{
Switch (CH)
{
Case   ' , ' : // New field start
Line. Add (field. tostring ());
Field. Remove ( 0 , Field. Length );
Field_start =   True ;
Break ;
Case   ' \" ' : // Quotation Mark Processing
If (Field_start)
In_quata =   True ;
Else
Field. append (CH );
Break ;
Case   ' \ R ' : // New record row start
If (Field. Length >   0   | Field_start)
{
Line. Add (field. tostring ());
Field. Remove ( 0 , Field. Length );
}
Text_array.add (line. toarray ());
Line. Clear ();
Field_start =   True ;
// In the window environment, \ r \ n usually appears in pairs, So skip
If (I < Text. Length -   1   && Text [I +   1 ] =   ' \ N ' )
I ++ ;
Break ;
Default :
Field_start =   False ;
Field. append (CH );
Break ;
}
}
}
// End of File
If (Field. Length >   0   | Field_start)
Line. Add (field. tostring ());
If (Line. Count >   0 )
Text_array.add (line. toarray ());
Return Text_array.toarray ();
}

After modification, there was basically no problem.