Ytukyg

I'm using SSIS and am importing multiple (30) txt files. I create the table on the fly using the file name of the txt file and creating the columns based on the first row from the txt file - all this works. My code stops working when an apostrophe ' is in one of the fields.

The files are delimitated using a |

My code:

SqlConnection myADONETConnection = new SqlConnection();

        myADONETConnection = (SqlConnection)(Dts.Connections["xxxxxxxxxxxxxx"].AcquireConnection(Dts.Transaction) as SqlConnection);





        string line1 = "";

        //Reading file names one by one

        string SourceDirectory = @"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

          string fileEntries = Directory.GetFiles(SourceDirectory);

        foreach (string fileName in fileEntries)

        {

            // do something with fileName

            string columname = "";



            //Reading first line of each file and assign to variable

            System.IO.StreamReader file2 =

            new System.IO.StreamReader(fileName);



            string filenameonly = ((((fileName.Replace(SourceDirectory, "")).Replace(".txt", "")).Replace("\", "")).Replace("-", "_"));

            line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]." + filenameonly + "') AND type in (N'U'))DROP TABLE [dbo]." + filenameonly + " Create Table dbo." + filenameonly + "([" + file2.ReadLine().Replace("|", "] NVARCHAR(500),[") + "] NVARCHAR(500))").Replace(".txt", "");



            file2.Close();



            SqlCommand myCommand = new SqlCommand(line1, myADONETConnection);

            myCommand.ExecuteNonQuery();      



            MessageBox.Show("TABLE IS CREATED");



            //Writing Data of File Into Table

            int counter = 0;

            string line;



            System.IO.StreamReader SourceFile =

            new System.IO.StreamReader(fileName);

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

            {



                if (counter == 0)

                {

                    columname = line.ToString();

                    columname = "[" + columname.Replace("|", "],[") + "]";

                }



                else

                {

                    string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("|", "','") + "')";

                    SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);

                    myCommand1.ExecuteNonQuery();

                }



                counter++;

            }



            SourceFile.Close();

The offending line is:

string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("|", "','") + "')";

I tried amending to the below to replace the apostrophe to no avail:

string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("|", "','") + line.Replace("'''", "") + "')";



string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("'", "") + "')";

Also nesting the replace does not work:

string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace(line.Replace("'''", ""),"|" ) + "')";

The below does not import anything:

string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("|", "','").Replace("'", "") + "')";

Changing to the below imports all but then fails over at the line with the apostrophe:

string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("|", "','").Replace("'''", "") + "')";

What am I missing?

Instead of concatenating the two REPLACE() functions like your first attempt, you should nest them.

Replace(

  Replace({arguments to remove apostrophe character}), 

  {arguments to remove pipe character}

)

If you want to keep the C# usage of string.Replace(), you would nest like this:

line.Replace({arguments to replace apostrophe).Replace({arguments to replace pipe})

Or you could do it in two separate statements:

line = line.Replace({arguments to replace apostrophe});

line = line.Replace({arguments to replace pipe});