CODEWITHSUNDEEP
c#parsingcsvssis
user3143565
user3143565

Reputation: 63

Delete Rows Start in CSV file In SSIS Controlflow Script Task

I have a .csv file that looks like this:

#Example Company                        
#(999) 999-9999                      
#http://yourwebsite.com                             
#Report Date Range: Dec 26, 2013 - Dec 26, 2013                     
#Exported: Dec 26, 2013                             
#Twitter : Profile Summary                              
#Screen Name,Name,Description,Location,Followers,Following,Listed

SctaSa,statisticalgraph,statistical Screen- The official account for your 
organization,Saudi Arabia,6775,8,75

So, I need to take specific data from the .csv file to be readable to SSIS Transformation, start from column "Screen Name" and remove the garbage data which start with # , to be look like that

Screen Name,Name,Description,Location,Followers,Following,Listed,Exported,Report Date Range
SctaSa,statisticalgraph,statistical Screen- The official account for your organization,Saudi Arabia,6775,8,75,26-Dec-13,26-Dec-13

i tried to use this C# script but it does not wore file (I'm not an expert in C# so I don't know what the problem is) I tried to use the following script to delete any line start with # but the file dose not transfare to the out put path; could you give me any suggestions?!

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Collections.Generic;
#endregion

namespace ST_a7b941606e0b40aa920bfe13fc81dc81
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            var lines = new List<string>();
            string line;
            using (var file = new System.IO.StreamReader("D:\\try.csv")) 
            {
                while ((line = file.ReadLine()) != null)
                {
                    if (line.Length != 0)
                    {
                        if (!line.StartsWith("#")  )
                        {
                            lines.Add(line);
                        }
                    }
                } 
           }
           File.WriteAllLines("D:\\SCTA_ETL\\try.csv", lines);
        }

        /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        public void Main()
        {
            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

Upvotes: 2

Views: 1981

Answers (2)

Yaakov Ellis
Yaakov Ellis

Reputation: 41530

You might want to change your logic in the middle:

var lines = new List<string>();
string outputPath = // your output path here
using (var file = new System.IO.StreamReader("c:\\mycsv.csv")) 
{
  string line;
  while ((line = file.ReadLine()) != null)
  {
    if (!line.StartsWith("#"))
    {
      lines.Add(line);
    }
  } 
}
File.WriteAllLines(outputPath, lines);

You had been removing all lines that had "#" anywhere inside.

Instead, only add in lines that do not start with "#".

Also, be sure to close and dispose your StreamReader when you are done with it, or just put the whole thing in using section.

Upvotes: 1

Dmitriy Finozhenok
Dmitriy Finozhenok

Reputation: 854

Another way:

File.WriteAllLines(outputPath, File.ReadAllLines("c:\\mycsv.csv").Where(x => !x.StartsWith("#")).ToArray());

Upvotes: 3

Related Questions