using window application to import csv data to sql server.here the code i tried. Not get any error but the import data cannot save in database.
code:
private void btnselect_Click(object sender, EventArgs e)
{
//open serach file to recognise by the
OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = ".csv";
ofd.Filter = "Comma Separated(*.csv)|*.csv";
ofd.ShowDialog();
textfilename.Text = ofd.FileName;
}
private 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)
{
while (! csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
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;
}
private void btnImport_Click(object sender, EventArgs e)
{
{
Cursor = Cursors.WaitCursor;
DataTable dt = GetDataTabletFromCSVFile(csv_file_path);
if (dt == null) return;
SaveImportDataToDatabase(dt);
MessageBox.Show("Data Import success!");
textfilename.Text = string.Empty;
Cursor = Cursors.Default;
}
}
private void SaveImportDataToDatabase(DataTable SS)
{
using (SqlConnection conn = new SqlConnection())
using (SqlConnection dbConnection = new SqlConnection("Data Source=ytl//bku; Initial Catalog=databse; User Id=***; Password=*****;"))
{
dbConnection.Open();
using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
s.DestinationTableName = "SS";
foreach (var column in SS.Columns)
s.ColumnMappings.Add(column.ToString(), column.ToString());
s.WriteToServer(SS);
}
}
}
Jignesh KumarPosted Dec 29, 2022, 10:06 AM
Hello,
You should validate your data before insert into table and need to check columnmapping for source(.csv) and sql table. If both are not mactching then you need to validate it.
In above case still you want to insert then create "Milliseconds" column in csv file and put 0 as milliseconds as default value. then it should work for you
Jignesh KumarPosted Dec 27, 2022, 12:43 PM
Hello,
Mapp your columns like below
or you can map using below if you have different column name,
https://stackoverflow.com/questions/55517968/sql-bulk-copy-mapping-with-different-column-names