Nowadays it is common in applications to have the functionality of reading the CSV data. My current project needed one. Even after searching for long, I could not get one which could satisfy my requirements. But after doing considerable amount of study, I came up with the following tool. CSV files stand for Comma Separated Value files. They are common text files with comma delimited values. Though the default delimiter is comma (,), we can specify other characters as delimiters like the semi-colon (;), colon (:), asterisk (*). But you cannot specify double quotes (") as a delimiter. I have used Microsoft Text Drivers for reading the CSV data. You have to use ODBC connection for accessing the CSV data. You can either create a DSN or use the connection string. If you create a DSN, theschema.ini file gets created automatically in the folder where all your CSV files reside. But if you use connection string, you have to create schema.ini file on your own. We are going to see the latter approach.
When the Text driver is used, the format of the text file is determined by using a schema information file. The schema information file, which is always named schema.ini and always kept in the same directory as the text data source, provides the IISAM with information about the general format of the file, the column name and data type information, and a number of other data characteristics.
For successfully running the application you need Test.csv file and a database with a table having three columns. But all this is provided in the demo application. So you need not worry. Follow these steps to run the demo application:
This is a function writeSchema()
. It creates the schema.ini file dynamically.
/*Schema.ini File (Text File Driver) When the Text driver is used, the format of the text file is determined by using a schema information file. The schema information file, which is always named Schema.ini and always kept in the same directory as the text data source, provides the IISAM with information about the general format of the file, the column name and data type information, and a number of other data characteristics*/private void writeSchema(){ try { FileStream fsOutput = new FileStream (txtCSVFolderPath.Text+"\\schema.ini", FileMode.Create, FileAccess.Write); StreamWriter srOutput = new StreamWriter (fsOutput); string s1, s2, s3,s4,s5; s1="["+strCSVFile+"]"; s2="ColNameHeader="+bolColName.ToString (); s3="Format="+strFormat; s4="MaxScanRows=25"; s5="CharacterSet=OEM"; srOutput.WriteLine(s1.ToString()+'\n'+s2.ToString()+ '\n'+s3.ToString()+'\n'+ s4.ToString()+'\n'+s5.ToString()); srOutput.Close (); fsOutput.Close (); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { }
This function ConnectCSV (string filetable)
takes the .csv file name as argument and returns the dataset containing the imported data.
public DataSet ConnectCSV (string filetable){ DataSet ds = new DataSet (); try { /* You can get connected to driver either by using DSN or connection string. Create a connection string as below, if you want to use DSN less connection. The DBQ attribute sets the path of directory which contains CSV files*/ string strConnString= "Driver={Microsoft Text Driver (*.txt;*.csv)}; Dbq="+txtCSVFolderPath.Text.Trim()+"; Extensions=asc,csv,tab,txt; Persist Security Info=False"; string sql_select; System.Data.Odbc.OdbcConnection conn; //Create connection to CSV file conn = new System.Data.Odbc.OdbcConnection( strConnString.Trim ()); // For creating a connection using DSN, use following line //conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN"); //Open the connection conn.Open (); //Fetch records from CSV sql_select="select * from ["+ filetable +"]"; obj_oledb_da=new System.Data.Odbc.OdbcDataAdapter( sql_select,conn); //Fill dataset with the records from CSV file obj_oledb_da.Fill(ds,"Stocks"); //Set the datagrid properties dGridCSVdata.DataSource=ds; dGridCSVdata.DataMember="Stocks"; //Close Connection to CSV file conn.Close (); } catch (Exception e) //Error { MessageBox.Show (e.Message); } return ds;}
This is a code written in the button's click event btnUpload_Click
. This actually inserts the data in the database.
private void btnUpload_Click(object sender, System.EventArgs e){ try { // Create an SQL Connection // You can use actual connection // string instead of ReadConFile() SqlConnection con1= new SqlConnection(ReadConFile().Trim()); SqlCommand cmd = new SqlCommand(); SqlCommand cmd1 = new SqlCommand(); // Create Dataset DataSet da = new DataSet(); /* To actually fill the dataset, Call the function ImportCSV and assign the returned dataset to new dataset as below */ da=this.ConnectCSV(strCSVFile); /* Now we will collect data from data table and insert it into database one by one. Initially there will be no data in database so we will insert data in first two columns and after that we will update data in same row for remaining columns. The logic is simple. 'i' represents rows while 'j' represents columns*/ cmd.Connection=con1; cmd.CommandType=CommandType.Text; cmd1.Connection=con1; cmd1.CommandType=CommandType.Text; con1.Open(); for(int i=0;i<=da.Tables["Stocks"].Rows.Count-1;i++) { for(int j=1;j<=da.Tables["Stocks"].Columns.Count-1;j++) { cmd.CommandText= "Insert into Test(srno, "+da.Tables["Stocks"].Columns[0].ColumnName.Trim()+") values("+(i+1)+", '"+da.Tables["Stocks"].Rows[i].ItemArray.GetValue(0)+"')"; /* For UPDATE statement, in where clause you need some unique row identifier. We are using ?srno? in WHERE clause. */ cmd1.CommandText= "Update Test set " +da.Tables["Stocks"].Columns[j].ColumnName.Trim()+" = '"+da.Tables["Stocks"].Rows[i].ItemArray.GetValue(j)+ "' where srno ="+(i+1); cmd.ExecuteNonQuery(); cmd1.ExecuteNonQuery(); } } con1.Close(); } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { btnUpload.Enabled=false; }}
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.
A list of licenses authors might use can be found here
联系客服