Wednesday, 20 May 2015

How to remove any extra column from a CSV file using C# .NET ?

Scenario - We need to process in which it is required to upload data from a .csv file on the daily basis using the SSIS package. It was observed from the past few days that the few extra columns are coming to the .csv file with same name which already existing. Because of this situation our flat file data source is throwing an error.

Resolution - In order to remove the above error we need to handle the repeated column from the source input .csv file and to make an automatic process for this task, code into script task has been written and it resolved this problem.

Work around steps

1. Downloading the file and acquiring the HTTP:// location
//Acquire the connection
Object objNative = Dts.Connections["HTTPConnection"].AcquireConnection(null);
//Create an object of httpclientconnection
HttpClientConnection objHttp = new HttpClientConnection(objNative);
//Download the file to the below location
objHttp.DownloadFile("\\xxxxxxxxxxx\\xxxxxxxx\\Sample.csv", true);

2. Use the Below function to validate the .csv check extra columns -
        /// <summary>
        /// Method: To check the extra column named - [Product Type] 
        /// </summary>
        /// <param name="pRenamedFilePath"></param>
        /// <param name="pActualFilePath"></param>
        private void ValidateExtraColumn_ProductType(string pRenamedFilePath, string pActualFilePath)
        {
          
            //String builder for normal rows string handling
            StringBuilder tCSVNormalRows = new StringBuilder();
            int tCountProType = 0;

            using (StreamReader tReader = new StreamReader(pActualFilePath))
            {
                string[] col = tReader.ReadLine().Split(',');


                for (int i = 0; i < col.Length; i++)
                {
                    if (col[i].ToString().Trim().ToUpper().Replace(" ", "") == "PRODUCTTYPE")
                    {
                        tCountProType++;
                    }
                }
                if (tCountProType == 2) //Remove the secord [product type] column
                {


                    //Assign the empty values to the string builder variables           
                    tCSVNormalRows.Append("");
                    tCSVNormalRows.Append("Reference Number,OEM Number,Retail,Cost,Consumer Copy,Product Type,Product Long Description,Manufacturer,MAC,UOM,Dimensions,Sell Weight,Product Category,Stock Status,QOH STL,QOH CAR,QOH DAL,QOH FRN,UPC,Country Of Origin,Sell Qty,Product Name");
                    tCSVNormalRows.AppendLine();
                    //read the whole stream
                    while (!tReader.EndOfStream)
                    {
                        var line = tReader.ReadLine();
                        string[] tstrMain = line.Split(',');

                        //Normal rows: read them into string builder for regenerate the file
                        tCSVNormalRows.Append(line.Replace("," + tstrMain[13].ToString().Trim(), ""));
                        tCSVNormalRows.AppendLine();
                    }

                }

            }

            if (tCountProType == 2) //Remove the secord [product type] column
            {
                //Rename the existing file
                if (File.Exists(pActualFilePath))
                {
                    //Delete the file
                    File.Move(pActualFilePath, pRenamedFilePath);
                }

                //Create the file
                File.Create(pActualFilePath).Dispose();
                //Write all the normal data lines into the above created csv file
                File.WriteAllText(pActualFilePath, tCSVNormalRows.ToString());
            }

        }

No comments:

Post a Comment