Script task in SSIS - insert records from csv with comma seperated and ignore comma in double quotes

[chintudwh]

HI All,

Your help is greatly appreciated.

I am stuck here, I have created c# script in ssis script task to insert records from csv file that is comma separated but I realized that the file also have few values in double quotes and comma in double quotes, I need to ignore commas in double quotes but not sure how to add in my script. please find the code below and please tell me where and what code I need to add to this code to ignore commas in double quotes.






publicvoidMain()


{




// TODO: Add your code here






stringdatetime = DateTime.Now.ToString("yyyyMMddHHmmss");




try

{


//Declare Variables



stringSourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString();




stringFileExtension = Dts.Variables["User::FileExtension"].Value.ToString();




stringFileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();




stringTableName = Dts.Variables["User:estinationTable"].Value.ToString();




stringArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();




stringColumnName = Dts.Variables["User::ColumnName"].Value.ToString();




//string ColumnList = "";



SqlConnection myADONETConnection =




newSqlConnection();


myADONETConnection = (SqlConnection)

(Dts.Connections[




"DB_Conn_MPC_PDM_Automation"].AcquireConnection(Dts.Transaction) asSqlConnection);




//Reading file names one by one




stringSourceDirectory = SourceFolderPath;




string[] fileEntries = Directory.GetFiles(SourceDirectory, "*"+ FileExtension);




foreach(stringfileName infileEntries)


{




//Writing Data of File Into Table




intcounter = 0;




stringline;




stringColumnList = "";




//MessageBox.Show(fileName);


System.IO.StreamReader SourceFile =




newSystem.IO.StreamReader(fileName);




while((line = SourceFile.ReadLine()) != null)

{




if(counter == 0)

{

ColumnList = "["+ line.Replace(FileDelimiter, "],[") + "]";




}




else


{






stringquery = "Insert into "+ TableName + " ("+ ColumnList + ", "+ ColumnName + ") ";


query +=




"VALUES('"+ line.Replace(FileDelimiter, "','") + "','"+ ((fileName.Replace(SourceFolderPath, "")).Replace("\\", "").Replace(FileExtension, "")) + "')";

SqlCommand myCommand1 = newSqlCommand(query, myADONETConnection);


myCommand1.ExecuteNonQuery();





}


counter++;


Thanks a lot


chintudwh

Continue reading...