Help with data processing | tabs in the middle of fields

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

DATA.txt

Input

Tips and Tricks

Best Practices

Transformation

Preparation

Optimization

Accepted answers

danilang

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

Parse.yxzp

All comments

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

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

Hi @Ivanmt01

The results are as follows

Dan

Parse.yxzp

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317