I am starting with a text file that contains data visually organized as multiple tables preceeded and followed by informational text. I use Read Text File to load the whole text string into Studio. After stripping away information that is not needed I end up with the table data as a string. Using Build Data Table I define the column headers and data types, string, date, and int32, for 12 columns. I use Generate Data Table to load the text string into the data table. I then use Write CSV. The result is 10 columns rather than 12. The last 4 numeric columns have been combined into 2 columns in the format ,nnn.nn nnnn.nn,nnn.nn nnnn.nn. Each of the 12 columns in the original text string has a different number of extra spaces ahead of the data. The total width of each column stays consistent from row to row with more or fewer spaces depending on the number of digits in a data item. The problem columns have far fewer digits and therefore more spaces than the other columns. I tried stripping out all the spaces except one to see if consistency would help. The result is worse with the number of columns reduced to 6. Multiple numeric columns are combined into a single comma separated value. I tried changing all the column formats in the data table to string and set the max column width to match the total number of characters and spaces to match the original text file. No change in the results.
Sorry, I thought your last screenshot was your source.
Looking again, your source file - the first screenshot - appears fixed length, meaning there is no delimiter. Each column has an explicitly defined length.
You may need to Read Text into a variable, then For Each on a split(vBcRLf) to loop through the rows, then for each row use substr to split each column up in an Add Data Row.
Thanks Paul. I was hoping to avoid having to loop through the rows but will do so if I am not successful with Text to DT. I tried changing my data table columns all to string and set the max column width to match the original document thinking that would address the fixed column width. Any further thoughts about this idea? I will switch to looping through the rows if nothing else looks hopeful.
I have worked my way through your Sample bot. I added Write Line activities after the two Assigns and an Output Data Table and Write Line to understand each step. I think I understand the two Assign activities. However something strange is happening in the Create Data Table activity. Here is a screenshot of the last lines of the Write Line output from the second Assign followed by the output from the Output Data Table / Write Line.
I got 12 columns using Yoichi’s workflow by unchecking the option “Ignore First Column” and changing the “Column Separators” to tab. Have you verified that you have the latest version of the Generate Data Table activity?
Akash_Javalekar1
Yoichi
ashokkarale
arun.v7
Jon_Smith
rashmisingh2695
ppr
jast1631
pere
Dheerendra_vishwakarma