My question boils down to how to grab a dynamic group of text from one column while another column equals another value and drop it into a text file. I know how to read the Excel into a data table and use the for each row, but having trouble getting the logic write to make sure all the info for a certain item is copied into a .txt file then add to the counter to go to another. There are 300 items in the list but the excel has ~56,000 rows. Thanks
Based on my understanding about the scenario you mentioned… you can follow the below steps to get it done…
- Read the excel into a datatable
- Use an Assign activity, and do the condition filtering there… The result is saved on to a datarow array.
ExcelDataTable.Select("ColumnNameB = 'Another Value'")
Now, since the condition filter is done, you have all the required dataset ready in a datarow array. However, you only need the values of one column… Let’s do it here…
Refer the sample I have attached on how to write data to a text in a loop without overwriting existing data…
WriteToText.xaml (8.6 KB)
Hope it helps!!
Thanks, I actually figured it 90% of the way out now, the only issue I am having is when I output data table to write a txt file there are " " around each line which I need to remove (the column is actually SQL Code that I need to put into another application). Tried using trim but its not working
Try using the replace function to replace " " with “”
YourString.Replace(" ", "")
So the strg.replace(" “, “”) does not work. I found a work around using the Find and replace in Notepadd++ (basically using a cached " in the replace search because it is a reserved character. I thought I had fully resolved it, but then realize within the 537 different SQL queries there are multiple instances where it is selecting a %Like% " xyz” which in turn breaks about 50 queries in the set. Turns out the " are coming from changing a xlsx file into a data table then outputting to text (old subject on excel forums for multi-line strings, normally isn’t an issue but this happens to expose the ‘invisible’ characters). I am working on a work around now to save the filtered data table as its own .xlsx which works fine, and then copy and pasting from the excel file back into the code repository, issue being that it still pulls in the bloddy " (even using the clean function in Excel) so I am starting to get frustrated.