Split Text File into Two Datatables

Help Studio
, ,
1

Does anyone have any good suggestions how to potentially split a text file into two different data tables using Generate Data Table or another activity? The two data tables would have to use different delimiters to separate the columns, one splits on a “:” and the other splits on “,”.

Thanks

1 Like
2

Please post the sample text file.

Thanks
#nK

3

@jpreziuso Please provide input and expected output files

4

ExampleFile.txt (293 Bytes)

Here is an example file I created. I would want to take everything with “test” in it and make a datatable out of that. And then take everything with “example” in it and create a separate datatable

5

@jpreziuso I tried with generate data table activity but the output not as expected with the data you have provided

Using regex you can simply get the data and store in the data tables. Please find workflow below using regex

Example.zip (12.1 KB)

6

@jpreziuso Here you go with exact output.

dt1.xlsx (8.4 KB)
dt2.xlsx (7.2 KB)
Sequence1.xaml (13.3 KB)

image

7

image

I received the following error when running this but I think it is on the right path.

I think the issue is that my example file should have looked like this.

ExampleFile2.txt (323 Bytes)

There is a second empty line which should really be the splitting point. Also if possible, I need the first data table to use the values before the colon as the field names. So it would be 12 fields with 1 row of data each

Thank you

8

Split_test.zip (19.7 KB)
Hi @jpreziuso ,

Please find the XAML file and confirm if it is a desired output which you are looking for.

9

Hi @jpreziuso ,

Since it will be always be two Datatables/ two tables to extract from the Text file.

We would require to Understand the Delimiter between two tables. Here in this case, we could take the Delimiter as example since it is the Starting Point of the Table2.

Where we assume that the example word is unique for the Table2.

We could then Split the Text file based on this Delimiter, splitting two tables.

We then Pass the Splitted texts into Generate Datatable Activity with it’s respective Column Separators to Generate two Datatables.

Check the workflow Below and Let us know about the delimiter in the actual data, or whether the Starting Data of Table2 is Good enough to be the Delimiter.
Extract_DT_FromText.xaml (6.4 KB)

11

The first “word” of the second data table is not always going to be the same, I was just creating example text to show what needs to be in the first data table and what needs to be in the second

12

This does a great job of splitting up the data correctly. But I need it to be formatted in a table.

For the “test data”, the values before the : should be the column names. And for the “example data”, it should be comma separated into columns.

Does this make sense?

13

Hi @jpreziuso ,

Could you please send me the desired output file to better understand scenario.

14

Hi @Rangu_Pranaviteja

Based on this file
ExampleFile2.txt (323 Bytes)

The desired output would be like this
ExampleFile2_Output.xlsx (10.2 KB)

15

Split_test1.zip (21.5 KB)
Hi @jpreziuso ,

Please try the attached file created as per the given desired output file and confirm if it works.

16

This works great. Is it possible to wrap everything in a for each loop so it can handle multiple files at once?

Ideally it would make a new output file for each input file being read.

I am trying to restructure it to work that way but having some issues

@Rangu_Pranaviteja

1 Like
17

Hey @jpreziuso

Yes, that should work.

If any issue you face in this, kindly post that for suggestions.

Thanks
#nK

18

Hi @jpreziuso ,

Good to hear that it worked in your use case. Could you share the issues facing when handling the multiple files

19

@Rangu_Pranaviteja The “example” portion of the data is working perfectly. Thank you

However the “test” part of the data is formatting wrong when put into a for each because it is appending the data for each line instead of doing it all at once

The “test” portion is outputting like this for one singular file
image

Instead of like this
image

See how it is appending each line? I am trying to change some things in order to figure it out as well

20

@jpreziuso ,

I think we need not put further for each since it would work for n number of (test : test) like data. Please verify without modifications and confirm if it looks good.

21

I’m not understanding your suggestion, sorry. Could you please explain further? Thank you again