Trying to escape a single quote from the lines in a csv file before copying the record to Excel using C# script

In an SSIS package, I’m trying to write out the contents of a .CSV file to a .XLS file because the Excel Connection Manager does not work well but having issues when trying to accomplish this via the C# script below.

Just a point of clarification, I am NOT INSERTING RECORDS into a DB but into an EXCEL file.

Any help/direction would be appreciated. Thanks.

If I do a REPLACE function within the SQL code that creates the .CSV file to remove the single quote and run the C# code again then the issue is resolved but the manager for this excel file wants it there and I'd like to remedy the code in order to provide that.

I am getting this error when I execute the code:

System.Data.OleDb.OleDbException (0x80040E14): Syntax error (missing 
operator) in query expression ''SAM'S/COSTCO - RETAIL/CONSUMAB')'.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at ST_3c46305a87ab4837b1d2cd33ee47f34c.ScriptMain.Main()

In the code below I tried to insert the following before the FIRST line within the WHILE loop below:

line = line.REPLACE(“’”, “\’”);

Here is the C# script code I'm working with:

try
{
    //Declare Variables
    string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();
    string DestinationFolderPath = Dts.Variables["User::DestinationFolderPath"].Value.ToString();
    string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
    string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
    string CreateTableStatement = "";
    string ColumnList = "";
    //Reading file names one by one
    string SourceDirectory = SourceFolderPath;
    string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);
    foreach (string fileName in fileEntries)
    {
        // do something with fileName
        //MessageBox.Show(fileName);
        //Read first line(Header) and prepare Create Statement for Excel Sheet
        System.IO.StreamReader file = new System.IO.StreamReader(fileName);
        string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(FileExtension, "")).Replace("\\", ""));
        CreateTableStatement = (" Create Table [" + filenameonly + "] ([" + file.ReadLine().Replace(FileDelimiter, "] Text,[")) + "] Text)";
        file.Close();
        //MessageBox.Show(CreateTableStatement.ToString());
        //Construct ConnectionString for Excel
        //string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DestinationFolderPath + "\\" + filenameonly
        //    + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
        string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + DestinationFolderPath + "\\" + filenameonly + ";" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\"";
        OleDbConnection Excel_OLE_Con = new OleDbConnection();
        OleDbCommand Excel_OLE_Cmd = new OleDbCommand();
        //drop Excel file if exists
        File.Delete(DestinationFolderPath + "\\" + filenameonly + ".xls");
        Excel_OLE_Con.ConnectionString = connstring;
        Excel_OLE_Con.Open();
        Excel_OLE_Cmd.Connection = Excel_OLE_Con;
        //Use OLE DB Connection and Create Excel Sheet
        Excel_OLE_Cmd.CommandText = CreateTableStatement;
        Excel_OLE_Cmd.ExecuteNonQuery();
        //Writing Data of File to Excel Sheet in Excel File
        int counter = 0;
        string line;
        System.IO.StreamReader SourceFile = new System.IO.StreamReader(fileName);
        while ((line = SourceFile.ReadLine()) != null)
        {
            if (counter == 0)
            {
                ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";
            }
            else
            {
                string query = "Insert into [" + filenameonly + "] (" + ColumnList + ") VALUES('" + line.Replace(FileDelimiter, "','") + "')";
                // MessageBox.Show(query.ToString());
                var command = query;
                Excel_OLE_Cmd.CommandText = command;
                Excel_OLE_Cmd.ExecuteNonQuery();
            }

            counter++;
        }

        Excel_OLE_Con.Close();
        SourceFile.Close();
        Dts.TaskResult = (int)ScriptResults.Success;
    }
}
catch (Exception exception)
{
    // Create Log File for Errors
    using (StreamWriter sw = File.CreateText(Dts.Variables["User::DestinationFolderPath"].Value.ToString() + "\\" + "ErrorLog_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".log"))
    {
        sw.WriteLine(exception.ToString());
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

Dts.TaskResult = (int)ScriptResults.Success;

line = line.REPLACE("'", "''");

That should work for you, unless I'm completely mistaken.