Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Removes Rows in a CSV FIle

Posted on 2015-01-21
Medium Priority
Last Modified: 2015-01-24
I am using SSIS Script Task Editor C#

I have a csv file and I need to delete extra rows that are not needed.

Example:-       9 columns.
Number – Date – ect.

I can have any number of rows.
Then an empty row.

Then unwanted data.

So basically I want to delete all rows after the first empty row.
Question by:aneilg
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 17

Expert Comment

by:Barry Cunney
ID: 40561511
Hi aneilig,
One approach may be to use StreamReader and StreamWriter objects in C# in the SSIS Script Task.

With the StreamReader object read the file line by line writing each line out to a temporary file with the StreamWriter object.
Skip(do not write out lines that should be excluded)
In your case, if a given line just read is an empty string, then you can exit the loop

Then copy the clean temp file back over the original file

The following is sample code which you can adapt - the file is passed in as an SSIS variable
 public void Main()

            string line = null;
            string FileFullPath = null;
            string IntendedAction = null; 
            string ReturnMessage = null;
            int linecounter = 0;
            bool DTSLogFireAgain = true;

            // Re-write the specified file leaving out the unwanted lines
                // store the path of the current file being processed 
                FileFullPath = Dts.Variables["FileFullPath"].Value.ToString();

                IntendedAction = "Parsing file: " + FileFullPath;

                if (System.IO.File.Exists(FileFullPath))
                    using (StreamReader reader = new StreamReader(FileFullPath))
                        using (
                                StreamWriter writer1 = new StreamWriter(FileFullPath + ".temp")  // Clean file 
                            // Read the file in file line by line
                            while ((line = reader.ReadLine()) != null)

                                // Keep a record of line number

                                // first check that line is not a blank line 
                                if (line.Length > 0)
                                        // if execution gets to here then, not a blank line so write out to temp file 
                                    // if execution gets to here then hit a blank line so stop writing out 


                            // Do final write and close of newly parsed IN file


                        // close the original IN file

                    }                                              // End Using file stream reader
                }                                                   // End If file exists
                else   // else load file does not exist
                    throw new System.IO.FileNotFoundException("Parsing: Unable to find file [" + LoadFullFile + "]");

                // Overwrite original file with newly parsed temp file
                if (File.Exists(LoadFullFile + ".temp"))
                    File.Move(LoadFullFile + ".temp", LoadFullFile);

                    ReturnMessage = "Success: " + IntendedAction;

                // Set the Pass/Fail flag to TRUE for job success
                Dts.Variables["LoadFileParsedFlag"].Value = true;
            catch (Exception Ex)                // Catch any overall exceptions
                // Set the error message
                ReturnMessage = "Failure: " + IntendedAction + " " + Ex.Message;
                Dts.Variables["ErrorMessage"].Value = ReturnMessage;

                // Set the Pass/Fail flag to FALSE for job failure
                Dts.Variables["LoadFileParsedFlag"].Value = false;

             // Log details of this task to the DTS Log
            Dts.Events.FireInformation(0, "File parsing", ReturnMessage, "", 0, ref DTSLogFireAgain);

            Dts.TaskResult = (int)ScriptResults.Success;

Open in new window


Author Comment

ID: 40562034
thanks i'll give it a go.
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 40562238
If the file is small, then you can load the whole thing into memory, remove the unwanted entries, then overwrite the original without using a temporary file:
            string FileName = @"C:\Users\Mike\Documents\SomeFile.txt";
            List<string> lines = new List<string>(System.IO.File.ReadAllLines(FileName));
            int blankLine = lines.FindIndex(x => x.Trim().Length == 0);
            if (blankLine != -1)
                while(lines.Count > blankLine)
                    lines.RemoveAt(lines.Count - 1);
            System.IO.File.WriteAllLines(FileName, lines.ToArray());

Open in new window

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 40564133
Thanks Barry.

Just a little change works perfect.

Author Comment

ID: 40566472
I've requested that this question be closed as follows:

Accepted answer: 0 points for aneilg's comment #a40562034

for the following reason:

LVL 17

Expert Comment

by:Barry Cunney
ID: 40565929
Hi Aneilg
Please let us know if you are going to accept solutions and award points for this.
I think you should possibly split points between myself and Mike Tomlinson as we both gave you good approaches, each with their own merits.

Thank you
LVL 86

Accepted Solution

Mike Tomlinson earned 0 total points
ID: 40566473
Points should go to Barry if that was the solution used (as implied by later comments).  A split would be fine, too, but doesn't really matter to me.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question