Help with data processing | tabs in the middle of fields

[Ivanmt01]

Hello community

My "text" field has tabs to separate information instead of commas or semicolons (attached txt). As you can see, my fields are also separated by tabs and that is my problem. 

Could you recommend a solution to replicate it in a database with several more rows and fields?

P.s:I have to insert it as txt and not as csv

[CharlieS]

When you add this file with an Input tool, you should be prompted for more information on how to parse it. Tab is one of the available options in that window. Does this work for you? I would try this, but there seems to be a problem with the file you attached to your post. 

Once the the fields and records are defined, it should be simple to Output that data as you need.

[geraldo]

hi

Change option start data import on line to 2 and deliters for |

and another entry starting at line 15 with no name in the first line

[danilang · Accepted solution]

Hi @Ivanmt01 

Things are a little more complex than just using a specific delimiter in your input.  Some of your lines have both tabs and pipe characters that make up the content of the field.  The way to approach this is to load the file with no delimiter and then clean it up internally.

The top branch of this uses Text to Columns to parse the header names.  The body branch uses a 7 part Regex expression to split the fields since the Text column has embedded "|" characters.  Once the fields are parse the the formula tool cleans the trailing "|", trims the trailing spaces and replace the embedded "|" with ":".  After the union, a Dynamic rename takes the column names from the first row of data

The results are as follows

Dan

[Ivanmt01]

I attached a new txt.

The problem occurs when I run the data, along with other files through (* .txt), and the error jumps through the tabs within a field: E100030916 | E1-0020-00036042 | E1-002. These should have another separator

[Ivanmt01]

@danilang  thank you 🙂

the idea of dividing the data into bodies had not occurred to me. 

[danilang]

You're welcome @Ivanmt01 

If this solves your problem, please mark the solution as solved.  This is a fairly common problem and having it show up as solved helps other users find the solution.

Dan