How to convert unusual data (txt format ) to excel ?

[Lakshraj]

Hello,

Could anyone please help me with converting the data from .txt/csv format to normal excel columns ? Basically, have received the data in CSV format which looks like a coding in Notepad (Screenshot 1). But when i copy paste them into MS excel its appears like normal data (Screenshot 2). However, the real problem is, i have more than 2.5GB data received on batches (overall 18 batches). I tried with Text to column option and even reg ex. Since the data format for each column is not consistent, am not able to achieve the end result like screenshot 2. Can any one please help me on how to achieve this ?

Many thanks in advance.

Below screenshots are created for Dummy reference :

Screenshot 1

Screenshot 2

[DavidP]

It looks like the columns are separated by multiple spaces, so you can do this in a couple of steps.

1. Load the txt file with an input data tool as a csv file with the delimiter set as \0 and uncheck "first row contains field names"

2. Formula tool with the replace formula:  Replace([Field1],"   ","|")

3. Text to columns tool set delimiter to | and number of columns to 12

4. Use data cleanse tool to get rid of leading and trailing spaces

[Lakshraj]

Hi David,

thanks for this, I tweaked my flow and i could see the split (it was embaded with Tabs) but now the issue is In ID column if there are blanks, its not arranging in line. Columns are mismatching. Is there any way to that ?

[TomWelgemoed · Accepted solution]

Just a quick view here, but can't you just use the Text to Columns tool and make \t your delimiter?

[danilang]

Hi @Lakshraj 

When the ID is blank, what does the rest of the row look like?  Can you provide some samples?

Dan

[Lakshraj]

Thanjs that works amazing. However the data load js tKing lot time now. Its has 35gb data but taking more than an hour. I hope it shouldn't be. Anyway to run the tool faster? 

[TomWelgemoed]

Hi @Lakshraj ,

Try to put it into an Alteryx database - i.e. an Alteryx .yxdb file first. This maybe takes a bit of time in itself, but it will run a lot faster once it's in that format. This is useful if you need to change something & re-run.

Hope that helps.

Tom