XLSX to CSV , incomplete conversion
AnsweredI have a set of excel files that are being delivered to an SFTP folder. It is a very simple process where the excel files are converted to csv and written to specific sheets in wdesk.
Only the first line in the excel file is being converted, everything else is being ignored.
When one of these files is opened in excel and saved as a CSV, it successfully converts the whole file.
-
Hi Ryan,
Do you have a screenshot that you can share of the Excel sheet and the Command configuration? Please make sure you blur out any confidential information. I am trying to see the structure of the worksheet to be converted.
Tony
0
Data has been randomized, but this is the structure. I have used this near identical process for another client that used files with outline grouping.
*Removing image due to confidential information
0
Thanks. A few things. First, I strongly prefer the Worksheet to CSV Command from the MS Excel Connector instead of the Tabular Transformation Connector. The Excel Connector Command is more robust and has additional options such as working with workbooks that have password protection. Second, you have ending column specified as zz but your screenshot shows data through column C only. I assume you did this to give yourself some room for expansion which makes sense. The Excel Connector Command is a bit more intelligent and you can try with leaving this field blank and allowing the Command to autodetect the range. Same thing for the end row.
Would you be able to modify the Chain to use the Excel Command instead and let me know if it works better for you?
0
While testing this , I did use the excel node, and had the same results. Here is the list file contents of the worksheet to csv node
One weird thing that I can't figure out, is if I open the file on my machine and then run it through a chain via a file upload node, it works.
0
Interesting. I think this one is going to require a ticket so that the support team can take a look. If you are able to open a ticket and provide the Excel workbook with randomized data, that will be immensely helpful. Sorry I couldn't get you going via this forum.
0
I have a ticket , I am just impatient.
0
One last thing.
I used the email notification to deliver the file that is being uploaded from the SFTP folder.
If I take the file without opening it and run it through the manual upload chain with the excel node, it doesnt work.
If I open the file and save it on my machine , just open and save without any modification, and then run it through the manual upload chain, the excel node works and returns a completely converted csv file.
0
Hi Ryan, it sounds like the original file is not formatted correctly. After opening it in Excel and saving it, Excel likely corrected the formatting which it why the file worked on the second try. How is the original file being generated before you open it in Excel? Double check the process to ensure a valid and correctly formatted Excel file is being created.
0
Hey Jeff,
Thanks for the response.
The excel files are being generated by Workday report generator.
0
The file generated from Workday is likely the source of the format issue especially since it is corrected after opening the file and saving it in Excel. I'd recommend starting your investigation with the export process and ensuring a correctly formatted file is produced. Alternatively, you may also be able to resolve this by exporting the file in a different format. For example, are you able to export the file as a csv from Workday?
0Please sign in to leave a comment.
Comments
10 comments