Genereate Datatable from String

Help Studio
, ,
1

Hi Team,

I wanted to generate datatable from below string I tried using generate Datatable but not able to acheive. CAn someone please help me to resolve this issue.

Exch Node Trans Fl Ste Rck Sub Slt FP Tie Cable/System Id

NDAH B49 NVJ1 GD 101 1 0 0 1 F NDAH 5051 1
NDAH B49 HJB3 3 1 1 1 N/A 1 F NDAH 5073 1
NDAH B49 7SIS 2 101 1 1 N/A 1 J 7SIS S1NU K 101

2

hhi @Yezdani_Shaik,

You can try to use the “Generate Data Table from Text” activity

image
image1105Ă—782 95.9 KB

Hope this helps.

Kind regards,
Kenneth

1 Like
3

@kennbalobalo Thank you so much for quick response, but not getting last column Values

image
image1330Ă—945 47.4 KB

4

It is because the “column separators” is [space] and so each word that is separated by “space” will be treated as a new column.

5

@kennbalobalo but I need that information as well, is there any other approach can you help me with that.

6

are the column headers fixed?

if yes, then we can build a data table based on the fixed columns and then loop on the rows and pass on the values to the columns of the data table.

7

Hi,

Can you share your input text as a file as we need accurate text data?

Regards,

8

@Yoichi
New Text Document.txt (311 Bytes)

9

Hello @Yezdani_Shaik

You can either use Generate Datatable or Text to Column activity. Use Space as the delimiter.

Thanks

10

Hi @Yezdani_Shaik ,

Could you also provide us with the Expected Output for this text data in the form of Excel ?

We would like to know if we can suggest you an alternate approach to get the required output. (Regex/String Manipulation)

Additionally, if you could also tell us what is the source of this data (Table in website, PDF, SAP, Screen Scrapping), maybe we can fix the extraction from the source.

11

@supermanPunch The source of data is a web application we are exporting into a text file as we don’t have excel export option
New Microsoft Excel Worksheet.xlsx (8.6 KB)

image
image1105Ă—223 6.97 KB

12

@Yezdani_Shaik ,

Check if the below Workflow helps :
Extract_Data_Regex.zip (3.5 KB)

It uses Regex to grab the data as required. Here, I have assumed that the data follows a certain pattern in which there is only a single word in each column except for the last column data.
image

If it doesn’t work for all the samples of data that you are trying to process, then another alternate would be to use Data Scrapping and extract the data as a datatable since the data is in a web application.

Let us know if the above options doesn’t work.

13

Hi,

It seems fixed width format. Can you try the following sample?

image
image1292Ă—665 50 KB

Sample20221003-1a.zip (2.8 KB)

Regards,

14

Data is not able to extract with data scraping, That’s why looking for other alternate way. Thank you so much for your response. :slightly_smiling_face:

1 Like