Trouble with .txt input delimited with a |

[lascurainge]

Hi y´all:

I'm having trouble with a input tool for a .txt file from SAP.

The delimiter for the file is "|", not a comma. I´m not able to input it as a CSV file delimited with a |. So I decided to just input it as a .csv with no delimitation and after that treat the data with different tools (text to columns) to get the data format I need.

When I try to input the data, it says the following error is occurring  "Too many fields in record #20698"

The file I'm trying to upload has way more fields that 20698. More like 1.5 mil.

I don´t if you guys can help me with this. I´m really struggling here since i have to input like 150 + files.

I´m not able to upload the file since it´s confidential, but if it's necessary I can create a example so you guys can try to input it.

Thanks very much.

[wdavis]

Hi @lascurainge 

In the configuration of the Input Tool, the 7th option is 'Field Length', can you try increasing this and seeing if that solves your problem?

If that doesn't work and you are able to post a sample of the data, I am happy to take a further look!

Kind Regards

Will

[lascurainge · Accepted solution]

Hi @wdavis,

I just solved the problem by setting the input as a .flat file. After the input with a text to columns tool i get the data format I need.

Thanks very much

[DavidP · Accepted solution]

My guess is that what's happening is something like this:

Input file looks like this. All rows have 5 columns, but row 3 has 6.

col1|col2|col3|col4|col5
1|1|1|1|1
2|2|2|2|2
3|3|3|3|3|3
4|4|4|4|4

Then this happens with the input data tool when I set the delimiter to a |

If I load it as a CSV with \0 as delimiter and 1st row contains field names unchecked, it looks like this:

Now with a Text to columns tool, I can parse [Field 1] - note, row 3 will have 2 values in the last column. If you want, you can use Dynamic Rename to take 1st row of data as field names.

[NeilFisk]

If you're file is delimited by pipes (i.e., |), you can substitute the comma that is in the Delimiters field with the pipe (|) and it will work without having to do any additional changes.  If your data has the Field Names in the first row, then leave the First Row Contains Field Names checked.

[PARESH]

 Hi,

I am also trying this, just wanted to check with you what did you mentioned in Delimiters field (In my case its tab)

Thanks in advance !

[DavidP]

Hi @PARESH 

If you want to use tab as the delimiter in text to columns, type \t