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

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 1.PNG

Screenshot 2

Screenshot 2.PNG

Custom Tools

Data Investigation

Accepted answers

TomWelgemoed

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

All comments

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 ?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703