Hey Everyone, This is my row in data table:
“05/08/04 OUTPT LABORATORY 35.00 35.00 35.00 0.00 0.00 0.00”
I want to store it column-wise in Excel but I am confused any help will highly be appreciated.
Excel Output would be like:
Date: 05/08/04
Type: OUTPT LABORATORY
Fare1: 35.00
Fare2: 35.00
Fare3: 35.00
Fare4: 0.00
Fare5: 0.00
Fare6: 0.00
Hey @Muhammad_Anas_Baloch
you can split it based on spaces and use indexes to assign
In case of OUTPT LABORATORY space case will fail dear.
Could you provide us with some more samples to confirm the Pattern and also let us know if the number of values will always be 8.
Here are the patterns:
05/08/04 OUTPT LABORATORY 35.00 35.00 35.00 0.00 0.00 0.00
05/10/04 OFFICE MEDICAL 14.00 9.50 0.00 0.00 9.50 0.00
05/10/04- HOME MED EQUIP 32.00 32.00 32.00 0.00 0.00 0.00
05/10/04- HOME MED EQUIP 10.00 3.75 0.00 0.00 3.75 0.00
05/14/04 PHYSIOTHERAPY 23.00 23.00 7.00 0.00 16.00 0.00
05/14/04 PHYSIOTHERAPY 14.00 9.00 0.00 0.00 9.00 0.00
Yes, there will always be 8 values.
As per my knowledge Generate dataTable will generate the table how this thing will resolve my problem?
sorry for that i think you will the input as the string format
@Muhammad_Anas_Baloch can you try assigning the indexes
also if you have two words then you can use regex to assign
05/08/04 OUTPT LABORATORY 35.00 35.00 35.00 0.00 0.00 0.00”
In the string index 1 wil retrun OUTPT and then put regex to check if next index is [A-Za-z]+
If yes then concatenate with a space with previous index and then assign
We could try using Regex Expression for the capturing part :
Regex :
(\d{2}\/\d{2}\/\d{2}).*?\s+(.*?)\s+([\d.,]+)\s+([\d.,]+)\s+([\d.,]+)\s+([\d.,]+)\s+([\d.,]+)\s+([\d.,]+)
But could you let us know if we would need to ignore the last 1 value, as it would be the 9th Value in the Text. You could also see that it is not captured by the Regex as the requirement was for 8 values.
Thank you so much. Also, would you let me know how we can store values one by one in the Excel cell after using it? Do we need 8 match activities and 8 for each loop to iterate over collection?
Apologies, I have updated the Previous Post with the Regex Expression.
Please suggest to me by using this Regex how can I store the values one by one in a cell?
Not needed. You could configure in the below Manner :
Note, we create a Datatable using Build Datatable activity with One Column. We use the Find Matching Patterns/Matches activity and get the Matches Collection Output and take the First Match (As the input is always a Single line text). From the First match, we are collecting the groupings and iterating through it and Adding each group value to the created Datatable.
mc.Cast(Of Match).First.Groups.Cast(Of Group).Skip(1)Output From Debug Panel :
When using Write Range activity do not check Add Headers option.
Easiest to do it in a datatable first. Say you’ve read your source into DT_Source, you also create DT_Final with the one column you need (Build Datatable).
Now you have DT_Final the way you want and can just write it to Excel. Also this method is dynamic so it’ll work no matter how many columns you have in DT_Source.
This is my requirement @supermanPunch. I am confused about looping and maintaining the data in such a way as shown in the below image. Please suggest me, your help is highly appreciated.
I believe you could try the method provided by @postwick , as it is more closer and easier approach to be implemented for your case, as your data is already in an Excel sheet or in a Datatable.
But we would be able to properly suggest a Solution if you could provide us with what is the Input that you receive and what should be the Expected Output. (Initially, the Input we thought was a String, But I believe you would not need to convert it to String for your case.)
@supermanPunch, I have a text file from where I fetch the data and store it in a collection using matches activity. The result stores in collection is in row like this one "05/08/04 OUTPT LABORATORY 35.00 35.00 35.00 0.00 0.00 0.00”.
Next part is to store each item separately into there relevant cell in Excel.
Could you help us clear the confusion, we see you have asked two different Output representations, Or if you could provide us a Sample Input and the Expected Output representation, we can conclude the requirement.
So far, what we can understand :
Input → Text File
Output Representation → In Excel/Datatable (But what format ?)
@supermanPunch, I have a text file that contains so many paragraphs. I am using regex to extract all the strings that contain data in this format "05/08/04 OUTPT LABORATORY 35.00 35.00 35.00 0.00 0.00 0.00” and storing it in a collection (LineRegexCollectionDT).
Next, I want to iterate the collection (LineRegexCollectionDT) and store them in the format shown in the image in Excel (The image is shared in the previous thread).
