How do I create a data table from a single row of data with multiple columns

Hello friends,

I am trying to make a data table of sales from a single row of data that goes across many columns. So I am trying to separate this single row into separate rows going down based on dates in the data. any ideas thanks

Thank you

Hi @amendez3 can you breif some extra detail

So I have data that I copied from a pdf file that I pasted in an excel worksheet. When I pasted, it copied everyting in one cell. I used text to columns and dilimited by space. The data was seprated really nice, but now it all on one row across many columns. so each data is in its own cell. The formatting is like this except the data is on the same row.
9/1/2018 13,227.41 609.68 0 0 0 -11,346.34 0 0 2,490.75 9/2/2018 12,489.67 293.92 0 0 0 -10,482.49 0 0 2,301.10 9/3/2018 7,506.29 658.62 0 0 0 -6,695.12 0 0 1,469.79 9/4/2018 5,019.25 54.44 0 0 0 -4,160.33

I want to move the data so that it is in this exact format like a data table like this:

9/1/2018 13,227.41 609.68 0 0 0 -11,346.34 0 0 2,490.75
9/2/2018 12,489.67 293.92 0 0 0 -10,482.49 0 0 2,301.10
9/3/2018 7,506.29 658.62 0 0 0 -6,695.12 0 0 0 1,469.79
9/4/2018 5,019.25 54.44 0 0 0 -4,160.33 0 0 913.36
Etc …
So the date should always be first and then the next 9 data points should be in the same row but different columns. I would need to work for all months for example 30 days 31 days and/or 28 or 29 days for February

I posted images as well hopefully you can see them

please let me know if you need more information

A better image of end result. I only do this by month

Hi @amendez3, there is not need to paste the data in excel and then, do the formating.
1.directly read the PDF data with Read PDF text activity into a string variable
2.split the string with the delimiter into a string array
3.loop through this string array and using if condition either items to temp string array or add row to datatable

1 Like

Hello friends,
This is what I have done
I used a read PDF text and created a variable called CreditCardSales into string.

Then I used a Split String activity to dilimit it by space. This created another variable I named Stringarray
(I will post an image in the second message)

Then this is where I am having trouble. I used a For EAch activity then put an If activity inside. I wrote the same code in the condition ( i really don’t understand the code.) That did not give me an error.

Then I put the squeuence in the then area and when I put the assign activity I got an error. Is your TempArr(index) an other variable?
Also when I am using the add data row what variable would I put in the Datarow input?

Thank you so much for the help. I will post images

This is my split string activity and what I did. Spliting it by space should work. That how it work when I dilimit it manually.

This is my for each activity The error started in my assign activity.

Also, one last question if the data table is created what would I use to put in in excel writerange?

Thanks

Also this is the PDF I am using. Would this still work with exracting this?

Thanks

HI @amendez3,

PDF-To-Excel.xaml (15.2 KB)

This is the error I got