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

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 < fieldData.Length; 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);
}
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
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
+1
Jignesh Kumar6y ago
Hi,

You means to store data or entire file in database ?
+1
Sachin Singh6y ago
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 < fieldData.Length; 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);
         }
     }
+2
Cynthia Sathuragiri6y ago
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/
+1