CODEWITHSUNDEEP
c#ssis-2012
ITHelpGuy
ITHelpGuy

Reputation: 1047

C# code to import data from flat files

ssdt for sql server 2014 when importing flat files from a 3rd party application gives "column delimiter not found error".

C# code:

string[] source = Directory.GetFiles(@"\\share\data\", "*.txt");


using(SqlConnection conn = new SqlConnection("Data Source=sql_server;Initial Catalog=test;Integrated Security=SSPI"))
{
   string query = 
      "INSERT INTO dbo.srcText(uid, code, description) VALUES(@uid, @code, @description)";

   using(SqlCommand cmd = new SqlCommand(query, conn))
   {     
      cmd.Parameters.Add("@uid", SqlDbType.Int);
      cmd.Parameters.Add("@code", SqlDbType.VarChar, 10);
      cmd.Parameters.Add("@description", SqlDbType.VarChar, 500);


      foreach (string loadData in source)
      {

         string[] allrecords = File.ReadAllLines(loadData);

         conn.Open();


         for (int index = 1; index < allrecords.Length; index++)
         {
            // how would I include multiple text qualifiers
            string[] items = allrecords[index].Split(new char[] { '|' });

            cmd.Parameters["@uid"].Value = items[0];
            cmd.Parameters["@code"].Value = items[1];
            cmd.Parameters["@description"].Value = items[2];

            cmd.ExecuteNonQuery();
         }

         conn.Close();
      }
   }
}

How to make this more robust by handling multiple text qualifiers?

Upvotes: 0

Views: 119

Answers (1)

Shannon Holsinger
Shannon Holsinger

Reputation: 2361

You could create an array of splits:

           char[] splits = new char[] { ',', '|', ';' };//add whatever delimiters you want here, surrounded by single quotes and separated by commas
        string[] parts;
        bool splitFound = false;//you could just test for parts==null but I'm adding this anyway. It allows handling a situation where no delimiters are found
        foreach(char splitter in splits)
        {
            parts = allrecords[index].Split(splitter);//this assumes that the text will never have any of the delimeters in it unless they are delimiting. If so, you need to handle first
            if (parts.Length > 0)
            {
                splitFound=true;
                break;
            }
        }
        if(splitFound){
        //process parts
             cmd.Parameters["@uid"].Value = parts[0];
            cmd.Parameters["@code"].Value = parts[1];
            cmd.Parameters["@description"].Value = parts[2];

            cmd.ExecuteNonQuery();

        }else{
            //handle no splits found
        }

Upvotes: 1

Related Questions