how to import csv file into sql server in asp.net c#

Question

Can you tell me how to upload csv file in asp.net and then how to save that file into sql server.

Sachin Singh · Answer

Upload csv to your local folder is same as uploading image or file , the main part is to read data from csv file and create a tabular format (Row column like datatable) and then loop through and save to DB or use SqlBulkCopy. // first fill datatable columns with CSV private static DataTable GetDataTabletFromCSVFile( string csv_file_path) { DataTable csvData = new DataTable(); try { using (TextFieldParser csvReader = new TextFieldParser(csv_file_path)) { csvReader.SetDelimiters( new string [] { "," }); csvReader.HasFieldsEnclosedInQuotes = true ; string [] colFields = csvReader.ReadFields(); foreach ( string column in colFields) { DataColumn datecolumn = new DataColumn(column); datecolumn.AllowDBNull = true ; csvData.Columns.Add(datecolumn); } while (!csvReader.EndOfData) { string [] fieldData = csvReader.ReadFields(); //Making empty value as null for ( int i = 0; i { if (fieldData[i] == "" ) { fieldData[i] = null ; } } csvData.Rows.Add(fieldData); } } } catch (Exception ex) { return null ; } return csvData; } } //Step 2.Save into sql server using SqlBulkCopy static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData) { using (SqlConnection dbConnection = new SqlConnection( "Data Source=SqlServerInstanceName;Initial Catalog=yourDB;Integrated Security=SSPI;" )) { dbConnection.Open(); using (SqlBulkCopy s = new SqlBulkCopy(dbConnection)) { s.DestinationTableName = "Your table name" ; foreach (var column in csvFileData.Columns) s.ColumnMappings.Add(column.ToString(), column.ToString()); s.WriteToServer(csvFileData); } }

Mageshwaran R · Answer

Hi Sonali, please try this link: https://www.aspsnippets.com/Articles/Import-Upload-CSV-file-data-to-SQL-Server-database-in-ASPNet-using-C-and-VBNet.aspx

Jignesh Kumar · Answer

Hi, You means to store data or entire file in database ?

Cynthia Sathuragiri · Answer

Refer the below URLs https://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-csv-file-records-into-sql-server-database-using-as/ https://www.c-sharpcorner.com/UploadFile/8c19e8/uploading-and-importing-csv-file-to-sql-server/