Convert a Tab-delimited File to a CSV

 

Question

I am trying to import a text file which is tab delimited and where text qualifier is double quotes.

I want to convert the following text:

"655295" "Keisuke" """Ueda""1-2-2, Central Park East F201" "Utase, Mihama-Ku"

To:

"655295","Keisuke","","Ueda","1-2-2, Central Park East F201","Utase, Mihama-Ku"

I tried derived column transformation, but it did not help. I tried script component, but that didn't work either. Can someone please help me out?.

Thank you in advance!!

 

Below is my code (there’s a lot to be improved):

public override void Input0_ProcessInputRow(Input0Buffer Row)

{

/*

* Add your code here

*/

String inputString = Row.line.ToString();

 

int count = Row.line.Split('"').Length - 1;

int counter = 1;

 

while (counter < count)

{

if (counter % 2 == 0)

{

int StartIndex = GetNthIndex(inputString.ToString(), Convert.ToChar("\""), counter);

int EndIndex = GetNthIndex(inputString.ToString(), Convert.ToChar("\""), counter + 1);

 

inputString = inputString.ToString().Substring(0, StartIndex + 1) + "," +

inputString.ToString().Substring(EndIndex);

 

}

else

{

}

counter = counter + 1;

}

Row.OutputLine = inputString;

}

int GetNthIndex(string s, char t, int n)

{

int count = 0;

for (int i = 0; i < s.Length; i++)

{

if (s[i] == t)

{

count++;

if (count == n)

{

return i;

}

}

}

return -1;

 }

 

Answer

You can change the separator in an independent computing engine. Here I do it in SPL (Structured Process Language):

A

1

=file("d:\\source.txt").import()

2

=file("d:\\target.txt").export(A1;",")

A1: Import the text file source.txt.

A2: Change the separators into commas and export the file into target.txt.

About calling an SPL script in another application, you can refer to How to Call an SPL Script in Java.