Problem With Text File To Data Table To CSV File Conversion

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.

Original Text

Data Table
image

CSV File

Many thanks for any suggestions.

@worldwynj ,

if passable kindly share your workflow screenshot

Thanks,
Rajkumar

@RajKumar_DC Here are the screen shots:




image

Many thanks!

Why are you using space as your column separator instead of comma?

I started using comma but all the data gets loaded into the first column. Using space I get to 10 of the 12 columns.

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.

Many thanks!

I don’t know of any other way to do it. I can’t find any UiPath activities that support fixed-width text, which is surprising.

HI,

Can you share your input text as a file? It’s no problem if dummy data.

Regards,

Hello Yoichi,

Here is a mock-up of the input text file.

Many thanks!
dummy data.txt (7.6 KB)

Hi,

Can you try the following sample?

Sample20220922-1.zip (5.1 KB)

Regards,

Hello Yoishi,

Thank you for the solutions for this text file. I will work through it.

Regards,

Hello Yoishi,

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.

The CSV file reflects the Data Table:
image

If I change the Data Table column separators to [Tab] the data table and CSV file are not null:

However, the CSV file still has the original problem with the 12 columns being reduced to 5 as the last 7 columns are treated as a single column.

Regards,

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?

image