Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Vinnie_Pisaniello
Contributor II
Contributor II

Replace pipe characters in data rows, in a pipe delimited txt file

I have a pipe delimited text file. The data is NOT surrounded by double quotes.

Sometime we receive pipes in the data fields themselves. IE |Rob|ert| (instead of Robert)

In SSIS I use a C# component to strip these extraneous pipes using the script below. I just don't know how to convert that into a Java routine to accomplish the same task when reading from a tFileInputDelimited file.

# region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Text.RegularExpressions;

using Microsoft.SqlServer.Dts.Pipeline;

# endregion

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

       public override void ProcessInput(int InputID, PipelineBuffer Buffer)

   {

       string pattern = "[|]";

       Regex reg_exp = new Regex(pattern);

       int index = 0;

       String DataBackup = "";

       while (Buffer.NextRow())

       {

           try

           {

               for (int columnIndex = 0;

                 columnIndex < Buffer.ColumnCount;

                 columnIndex++)

               {

                   index = columnIndex;

                   string columnData = null;

                   if (Buffer.IsNull(columnIndex))

                   {

                       columnData = "is NULL";

                   }

                   else

                   {

                       BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);

                       switch (columnInfo.DataType)

                       {

                           case DataType.DT_WSTR:

                           case DataType.DT_STR:

                               columnData += Buffer.GetString(columnIndex);

                               DataBackup = columnData;//Save backup

                               columnData = reg_exp.Replace(columnData, "");

                               Buffer.SetString(columnIndex, columnData);

                               index = 0; //Reset index;

                               DataBackup = ""; //Reset data

                               break;

                           // add code to support more data types here

                           default:

                               columnData = "";

                               index = 0; //Reset index;

                               DataBackup = ""; //Reset data

                               break;

                       }

                   }

               }

           }

           catch

           {

               Buffer.SetString(index, DataBackup);

               index = 0;

               DataBackup = "";

           }

       }

       base.ProcessInput(InputID, Buffer);

   }

}

Labels (6)
0 Replies