Split plain text into table

[mmostaquim]

Hi,

I have a dataset which is exported from SAP in the txt file. It is required to split txt file data into a table based on delimiter. Is there any VBO in Blue Prism that can split plain txt into a table?

Demo data:

222345  | Konto A   | 39500.45 

Required Output:

Regards,

Ekram

[Chakkravarthi_PR]

Hi @mmostaquim , 

After the split text you get this collection, what do you want to achieve from the collection? 
1. Is that to make the first row as Header? 

2. Does each line in the text file follow the same structure as the demo data provided?

3. Does the split text has special characters or escape sequences?

[michaeloneil]

Hi @mmostaquim 

Have you tried the Utility - Strings vbo with the action split text? it has the option to split text by character. Although i've never tried it with multiple columns required it will likely put all text into a single column. You could try it by dropping the text into excel then splitting it in there.

[mmostaquim]

Hi @Chakkravarthi_PR,

For my use case, I need to export SAP Table in the txt file. This table contains rows more than 2000. The structure looks likes  "account number | account name | value". In the demo, I showed only one row. The position of the row is dynamic depending on booking done by SAP each month. Using the loop logic, i can find out the required account number and account name with the value. And yes split text has special character "|".

[mmostaquim]

Hi @michaeloneil,

I tried it with Utility - Strings vbo, Split text action. I have split text by the special character “|”. The information is stored in a single column. However, I want three columns containing account number, account name and value.

[EricNewton]

I would use the Data - OLEDB VBO and just set up with a schema.ini file specifying your columns and pipe delimited.

The schema.ini file just needs to be in the same location as the text file you are reading from and reference it. So if your file was named "test.txt" then your folder looks like this -

You schema.ini file then contains the following text

[test.txt]
ColNameHeader=True
Format=Delimited(|)
Col1="account number" Text
Col2="account name" Text
Col3="value" Double

Your connection string is

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YOUR_PATH_DETAILS\example;Extended Properties='text';

The query you run is 

SELECT * FROM [test.txt]

Process would look something like this

So for your actual file you have to replace the test.txt with your file name, data source with you folder location where the file is, and make sure the schema.ini file is in the same location and references your file name. Typically I just create the schema.ini on the fly using Utility - File Management VBO and Write Text File action.

[stepher]

Good morning, @mmostaquim ,

I use a slightly different action in the 'Utility - Strings' VBO.  I use the 'Get Delimited Text As Collection' to parse out pipe-delimited files (my preferred de-limitation).  I have also used this for my less preferred delimited files {carets (^) and tildes (~) when I absolutely need to}.

Your exemplar information was only a single line, so it is possible that there could be in an issue with the End of Line markers/characters.

Good luck,

Red

[mmostaquim]

Hi @EricNewton and @stepher , Thank you for your solution. I will have a look at it and let you know the feedback.

[michaeloneil]

This is good to know @stepher I might be using an older version of the vbo so ill need to get the latest one if this is an action included in it.