Using Pentaho data integration (Kettle), I read a long string from a text file:
a, 1, 2, b, 3, 4, c, 5, 6, ...
Is there any PDI/Kettle steps or method to split this string to become an n column table format like below (the column name can be define freely):
| column1 | column2 | column3 |
|---|---|---|
| a | 1 | 2 |
| b | 3 | 4 |
| c | 5 | 6 |
the above just a simplify example, my real case is having different separator character and the column number (n) is bigger. But I just want to get the main problem solve first.
I didn't understand about "different separator character". If you have different separators on the same line, such as a comma and a semicolon, then this is a tricky task for the PDI process. Then you need to cast all delimiters to the same type first. For example, in Notepad ++, make a replacement. Notepad ++ does a good job with large CSV files.
Further in the PDI there is a standard separator component "Split Fields".
I have prepared a SOLUTION for you. In my solution I have set N=3, But you can set as many as you want. Also, you require to input Column name in 'Row denormaliser' step if you want to set N =3/4/5/N.
Although , you can set column name dynamic (if you want) using 'Meta Data Injection' step easily.
