Working with a Corrupted CSV File
The client has a spreadsheet they receive from a supplier, it is a CSV file they are opening in Excel but has some formatting issues, the Cell Data does not match the Formula Field Data, which I need.
And when Catalytic read the file it only read what the Cell Data shows, which as you can see is incorrect as it doesn’t show the Date and the Time Stamp is cut off.
However I noticed if I opened the file in Notepad, it showed up as a comma delimited csv list
I know that Catalytic can handle text fields formatted like this and convert them to a Data Table, so I just needed to extract as we see it here into a text field.
I initially tried Files: Convert file to text thinking it would take the above txt version and transfer it to a Field. But came up with the below result:
The file definitely seems to be corrupt, if I resave it, the timestamp formatting gets messed up. So I needed to find a way to save it without messing up the formatting.
The Solution:
I found if I used the action Excel: Create spreadsheet or CSV file it kept the formatting even if it looked a little messy in the output…BUT it kept the formatting.
Now I have a file I can work with, but I need to get this into a text field. I know I can convert a table to a text field...so I used the action Excel: Save spreadsheet to Table to get it into a table format.
SUCCESS
Now that I have it in a Table, I use the [Tables: Convert data table to text]
(https://help.catalytic.com/docs/tables-convert-data-table-to-text/) action
I set the row template as ‘columns[1]’ and row delimiter to ‘\n’
Now I have it in a format like my original text file, I can now convert it to back to a Data Table that I can use. Finally I used the Tables: Convert text to a data table action, I set Column Delimiter to ‘;’ and Row delimiter to ‘\n’ and now have a table I can use for my Look Up actions.
