Trouble with .txt input delimited with a |

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.

Datasets

Preparation

Accepted answers

lascurainge

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

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.

All comments

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

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

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703