• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

Removes Rows in a CSV FIle

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.
0
aneilg
Asked:
aneilg
  • 3
  • 2
  • 2
1 Solution
 
Barry CunneyCommented:
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
            try
            {
                // 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
                                linecounter++;

                                // 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 
                                        writer1.WriteLine(line);
                                    
                                }
                                else
                                {
                                    // if execution gets to here then hit a blank line so stop writing out 
                                    break;
                                }

                            }

                            // Do final write and close of newly parsed IN file
                            writer1.Flush();
                            writer1.Close();

                          
                        }

                        // close the original IN file
                        reader.Close();

                    }                                              // 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.Delete(LoadFullFile);
                    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

0
 
aneilgAuthor Commented:
thanks i'll give it a go.
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
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

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
aneilgAuthor Commented:
Thanks Barry.

Just a little change works perfect.
0
 
aneilgAuthor Commented:
I've requested that this question be closed as follows:

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

for the following reason:

Perfect.
0
 
Barry CunneyCommented:
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
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now