Hi everyone,
In my project, i upload a Excel file from screen then convert it to CSV, i use ConvertToCSV (ExcelConverter) and LoadCSV2RecordList (CSVUtilExtension) to do that.
But the problem is, if in a cell have multi-line text, it will be converted into different rows in CSV
The CSV file need to be same Excel file, how can i achieve that?
I try using 'Excel To Record List' to convert excel file to a list then convert the list to CSV, it' OK, but 'Excel To Record List' require a Entity/Structure have same attributes as header columns, and my Excel file have flexible columns , not fixed in number, so i find other ways.
Thanks,
Hi everyone, i just found out a solution for the issue, it seem to be complex, but it work for me, so
From original excel file, you use AdvancedExcel extension to read file, read cell values sequently and replace the empty line ("Chr(10)") by a special character (like "-") then overwrite cells.
Convert the new excel file to CSV, using ConvertToCSV (ExcelConverter), you will have a new csv file with no empty lines
Then using LoadCSV2RecordList (CSVUtilExtension) to convert file to a list (list Structures), with that list, continue loop the list and replace all attribute values that have special characters (like "-") into empty lines ("Chr(10)")
Finally, convert the list to csv again, can using string methods to append value with "," separator
I think that not a best solution, so if you know better solutions, please sharing
thanks,
There's a risk with your current implementation if your data already contains a hyphen. I noticed that enclosing multiline data within quotes displays it correctly. Could you check if this approach works for you? This way, you can use quotes instead of replacing characters with hyphens.
Thanks for the idea. I think this is much cleaner solution!
Thanks, i just tried, i enclosed cell values with quotes by using code: """" + Cell_Read.CellValue + """", then convert to CSV file, it like below, maybe i used wrong syntax to enclose?
if this seem like below image, i must handle data to remove quotes
Looks like there is a shortcoming in the ExcelTool while passing double quotes. Please see a similar incident in another forge component CSVUtil (https://www.outsystems.com/forums/discussion/13427/csvutil-double-quotes/) and see how they solves it.
Please refer to similar question in https://www.outsystems.com/forums/discussion/96865/convert-excel-file-to-csv/#Post418493
thanks, i found out a solution by your option