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

Question

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;

For most questions even losely *SQL value with special character xyz* related, there is only one answer: use [Parameters](https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter(v=vs.110).aspx)! [See second answer](https://stackoverflow.com/a/11912438/5265292) — grek40, Nov 16 '17 at 14:07
Possible duplicate of [single quotes escape during string insertion into a database](https://stackoverflow.com/questions/11912412/single-quotes-escape-during-string-insertion-into-a-database) — grek40, Nov 16 '17 at 14:10
Regarding the edit: it doesn't matter that your end point is an excel file instead of a database. What matters is your usage of the OleDb as intermediate technology and it supports parameters for all I know. — grek40, Nov 16 '17 at 22:52

score 1 · Accepted Answer · answered Nov 16 '17 at 13:31

1

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

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

answered Nov 16 '17 at 13:31

Raithlin

1,764
10
19

Thanks for the quick response. Unfortunately, I'm getting the same error. – Melinda Nov 16 '17 at 13:35
When creating a CSV file for importing into Excel I usually surround text with double quotes. That resolves most issues like this. Would that work for you? – Raithlin Nov 16 '17 at 14:31
I tried that as well but it still failed with the same error. – Melinda Nov 16 '17 at 14:58
1

Raithlin, I had a syntax error when I tried your suggestion and after I fixed it that did the trick. I appreciate your help and time! – Melinda Nov 16 '17 at 16:38
Please edit my answer with the correct syntax for future reference. :) – Raithlin Nov 20 '17 at 07:14
1

Hi Raithlin, you didn't have the syntax error I did on my side when I ran the code. Thanks. – Melinda Nov 20 '17 at 20:30

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

1 Answers1