WinAutomation Logo

Problem:

Solution:

We can use the job displayed above to get the contents of a text file appended into an Excel file, after they have been split into categories.

Discussion:

We start by launching the Excel to open a new blank document. We use the "Read Text from File" action to read the contents of the file that contains the text we want to categorize into our Excel spreadsheet.

In order to make sure that the right content is sent to the right column of our Excel Sheet, we need to make sure that all lines of our text file contain the same type of information. That can be done, by comparing the first line that contains the headings of the information contained within our text file, and they also use the same delimiter to separate the information.

In order to accomplish that, we use a "Split Text" action, that will split the first line (remember that here too our index is zero based), into as many categories, as many as the headings of our text file are.

Having done that, we need to get the number of elements the first line was split into so that we can use it as a standard for every row of our list. To do that, we use the property count of the %Headers% variable, and we store it into the %HeadersCount% variable.

We also need to keep track of the line we are currently using, so that we can move between rows and know to which line we should append the information retrieved.

Using the "For each" action, we go through the items of the list, and configure a set of actions to run for each item of the list. So each line, we want to use the "Split text" action, to split the line into categories using the “-“ delimiter. Having done that, we need to make sure that all the categories of our line are filled in, otherwise, since we will not know from which column a value would miss, the existing values would be appended to the first free cell.

So, using the "If" condition action, we compare the splited current line’s items total, to the number of headings. If the condition is true, then we want it to start from column one, and using the "For Each" loop action, for each item retrieved by the "Split Text" action, we write its value to the Excel spreadsheet we previously opened. This way, the first item contained in the %SplittedLine% variable, is written to the 1st column and 1st row, the 2nd item’s value should be written on the same row, but the 2nd column. So within the "For Each" loop action, we add an "Increase Variable" action to increase the %CurrentColumn% value by one. This will repeat as many times as many the items of our current line are.

Once we have exited the loop, we need to increase the line index, to make sure that the next time the loop runs, it will not write the new line’s values to the same line, but in a new line. In the event that a line does not have as many items as it should, we need to specify what should happen. In this case, using an "Else" condition action, followed by a "Write Text to File" action, we specify that if the number of the items retrieved from the current line is not equal to the number of headings, it should then append the current line to a text file, so that the user can review it and fill in the missing details.

Finally, we should save and "Close the Excel File" to keep the information we just got from the text file.

Leave your comment