Hello everyone,
i have to export my database record to a CSV file but the data in some column fields contains comma (which is my delimiter during import process) and i want add quotes to a string which contains comma so how to do this. i cannot manualy write string because it comes in import export process, plz give me any link or sample code.
here is my old record,PTNAME,REGNO/ID,BLOOD GRP,WARD NAME,DOC NAME,XRAY,PATHO,MEDICATION,BLOOD GIVEN
Mr. GHULAVE VASANTRAO PANDURANG,SH1503/00847,,RECOVERY,SHELKE SAMEER,"X RAY PBH RT IT FEMUR FRACTURE POST OP XRAY -ACCEPTABLE WITH IMPLANT IN SITU 2D ECHO MILD CONC LVH , GOOD LV SYSTOLIC FUN, ALTERED LV DIASTOLIC FUN.", HB-11.9gm% TLC-8700 PLT COUNT-195000 BSL-173 UREA -23 CREATININE -1.2 SR.ELECTROLYTES-WNR BLD GROUP-B + HIV-NEGATIVE HBsAG-NEGATIVE PT INR -15/15/1.0. ECG SINUS TACHYCARDIA ,IV TAXIMAX 1.5 GM 1-0-1 IV TRAMADOL DRIP 1-0-1 TAB NUSAID SP 1-0-1 TAB ARCOPAN D 1-0-1 CAP BONE C PLUS 1 -0-1 TAB ANXIT 0.5 MG 0-0-1 ANKLE TRACTION 3 KG RT LL ,NOT GIVEN
here is file saved in database and i need to export it with quotes arround whatever string contains comma i.e here the string in bold format.
Mr. GHULAVE VASANTRAO PANDURANG,SH1503/00847,,RECOVERY,SHELKE SAMEER
,X RAY PBH RT IT FEMUR FRACTURE POST OP XRAY -ACCEPTABLE WITH IMPLANT IN SITU 2D ECHO MILD CONC LVH GOOD LV SYSTOLIC FUN , ALTERED LV DIASTOLIC FUN., HB-11.9gm% TLC-8700 PLT COUNT-195000 BSL-173 UREA -23 CREATININE -1.2 SR.ELECTROLYTES-WNR BLD GROUP-B + HIV-NEGATIVE HBsAG-NEGATIVE PT INR -15/15/1.0. ECG SINUS TACHYCARDIA ,IV TAXIMAX 1.5 GM 1-0-1 IV TRAMADOL DRIP 1-0-1 TAB NUSAID SP 1-0-1 TAB ARCOPAN D 1-0-1 CAP BONE C PLUS 1 -0-1 TAB ANXIT 0.5 MG 0-0-1 ANKLE TRACTION 3 KG RT LL ,NOT GIVEN
here is my import method, public void import()
{
try
{
con.Open();
DataTable dt = new DataTable();
string sourcedir = @"C:\Users\Priya\Desktop\B";
var icsv = Directory.EnumerateFiles(sourcedir, "*.csv");
foreach (string currentfile in icsv)
{
using (TextFieldParser parser = new TextFieldParser(currentfile))
{
parser.TextFieldType = FieldType.Delimited;
parser.Delimiters = new string[] { "," };
parser.HasFieldsEnclosedInQuotes = true;
string[] columns = parser.ReadFields();
for (int i = 0; i < columns.Length; i++)
{
dt.Columns.Add(columns[i], typeof(string));
}
while (!parser.EndOfData)
{
string[] fields = parser.ReadFields();
DataRow newRow = dt.NewRow();
for (int i = 0; i < fields.Length; i++)
{
newRow[i] = fields[i];
}
dt.Rows.Add(newRow);
}
}
SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = "EightField";
bc.BatchSize = dt.Rows.Count;
bc.WriteToServer(dt);
//SqlDataAdapter da = new SqlDataAdapter("select * from EightField", con);
//da.Fill(dt);
//dataGridView1.DataSource = dt;
//con.Close();
}
}
catch
{ }
}
here is my export method public void DataExport(string SelectQuery, string fileName)
{
try
{
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(SelectQuery, con);
da.Fill(dt);
//Sets file path and print Headers
// string filepath = txtreceive.Text + "\\" + fileName;
string filepath = @"C:\Users\Priya\Desktop\R\z.csv";
StreamWriter sw = new StreamWriter(filepath);
int iColCount = dt.Columns.Count;
// First we will write the headers if IsFirstRowColumnNames is true: //
for (int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if (i < iColCount - 1)
{
sw.Write(',');
}
}
sw.Write(sw.NewLine);
foreach (DataRow dr in dt.Rows) // Now write all the rows.
{
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
sw.Write(dr[i].ToString());
}
if (i < iColCount - 1)
{
sw.Write(',');
}
}
sw.Write(sw.NewLine);
}
sw.Close();
}
catch { }
}