How to save a collection as a .csv

[JamesACA]

Hello,

I was wondering if anyone knows how I can save a collection as a .csv with an encoding of UTF-8. I tried using the excel save as function and saving it as a .csv instead of .xlsx but it did not work, and the Append to Text File through the Utility - File Management does not allow for a collection to be input. The only option I can see using the Append text to file is by writing one column at a time with separators from the collection through a loop.

Thanks in advance!

EDIT: I have attached a file of my current solution, I was looking for something better if possible!

 

[sivagelli]

For saving collection as csv file, you can do the following-

• In the Excel VBO, make a duplicate of the 'Save Workbook As' action and save it as 'Save Workbook As CSV'
• Open the Code Stage on 'Save Workbook As CSV' action
  • re-name the Code stage to 'Save Workbook As CSV' to avoid duplicate name error with code stage of 'Save Workbook As' action
  • Go to Code tab and make changes to the line# 6 to wb.SaveAs(Filename:=filename, FileFormat:=6)
  • FileFormat:=6 is enum equivalent of xlCSV
  • This change will save the file with csv format, but not of UTF-8. To save as csv with utf-8, change the FileFormat:=62 in the above line of code. File will be successfully saved as csv with utf-8 only if you have MS Excel build equal to or later than 16.0.7466.2023 else you will get an error while using this save as action
• Publish this action
• Save the VBO

Now, you are ready to call this action: 'Save Workbook As CSV' to save the excel as csv file. So, to save collection as csv, the flow would be-

• Start
• Create Instance
• Create Workbook
• Activate Worksheet
• Write Collection
• Save Workbook As CSV
• Close Instance

Post back how it goes!

 

[shivam09]

For saving collection as csv file, you can do the following-

• In the Excel VBO, make a duplicate of the 'Save Workbook As' action and save it as 'Save Workbook As CSV'
• Open the Code Stage on 'Save Workbook As CSV' action
  • re-name the Code stage to 'Save Workbook As CSV' to avoid duplicate name error with code stage of 'Save Workbook As' action
  • Go to Code tab and make changes to the line# 6 to wb.SaveAs(Filename:=filename, FileFormat:=6)
  • FileFormat:=6 is enum equivalent of xlCSV
  • This change will save the file with csv format, but not of UTF-8. To save as csv with utf-8, change the FileFormat:=62 in the above line of code. File will be successfully saved as csv with utf-8 only if you have MS Excel build equal to or later than 16.0.7466.2023 else you will get an error while using this save as action
• Publish this action
• Save the VBO

Now, you are ready to call this action: 'Save Workbook As CSV' to save the excel as csv file. So, to save collection as csv, the flow would be-

• Start
• Create Instance
• Create Workbook
• Activate Worksheet
• Write Collection
• Save Workbook As CSV
• Close Instance

Post back how it goes!

can you provide a general solution (not version specific).

 

[sivagelli]

can you provide a general solution (not version specific).

CSV with UTF-8 is not there as an option in Microsoft Excel prior to the build, 16.0.7466.2023. If that is the limitation with Excel, you have to live with it or get builds later to or equal to the specified build.

 

[JamesACA]

For saving collection as csv file, you can do the following-

• In the Excel VBO, make a duplicate of the 'Save Workbook As' action and save it as 'Save Workbook As CSV'
• Open the Code Stage on 'Save Workbook As CSV' action
  • re-name the Code stage to 'Save Workbook As CSV' to avoid duplicate name error with code stage of 'Save Workbook As' action
  • Go to Code tab and make changes to the line# 6 to wb.SaveAs(Filename:=filename, FileFormat:=6)
  • FileFormat:=6 is enum equivalent of xlCSV
  • This change will save the file with csv format, but not of UTF-8. To save as csv with utf-8, change the FileFormat:=62 in the above line of code. File will be successfully saved as csv with utf-8 only if you have MS Excel build equal to or later than 16.0.7466.2023 else you will get an error while using this save as action
• Publish this action
• Save the VBO

Now, you are ready to call this action: 'Save Workbook As CSV' to save the excel as csv file. So, to save collection as csv, the flow would be-

• Start
• Create Instance
• Create Workbook
• Activate Worksheet
• Write Collection
• Save Workbook As CSV
• Close Instance

Post back how it goes!

Thanks a lot worked wonderfully! Created a CSV with a comma as a delimiter!

I attached a screenshot of the code for future viewers, and I also have included two links below for other file formats you can use and how the Workbook.SaveAs method works!

Workbook.SaveAs
FileFormats

 

[sivagelli]

Good to know that it worked!
Cheers!