Export CSV to Excel XLSX

[jdrummey]

There was a question from the VizAlerts presentation at TC16 https://community.tableau.com/docs/DOC-10039 on exporting directly to Excel .XLSX format since apparently some users demand that instead of CSV.

This is a pretty common feature request in Tableau, I think VizAlerts could be a good place for it once file export is implemented. I think the way to do this is with a new content reference like VIZ_XLS().

There's a pandas.DataFrame.to_excel method that could be used for this. Here are possible features in rough order of how much I could imagine people wanting them, this issue could be split up or modified as specific functionality is implemented:

1. Barebones conversion with write to specified workbook & default worksheet name, the existing |filename option could be used for the workbook. This would overwrite any existing workbook.
2. Support naming a worksheet in a workbook, this could be a new |worksheet option. This would overwrite an existing worksheet name in the workbook.
3. Support appending a worksheet to existing workbook (create worksheet and potentially workbook if it doesn't exist). This could be a |append option on the content reference.
4. Support ordering of columns & data types. For this I'm thinking of a side-along sheet (probably an additional view rather than a file) that would have the following fields: Original Field Name, Output Field Name, Output Field Order, and Output Data Type, this could an option on the content reference like |schema=schemaworkbook\view-name
5. Support downloading data into multiple worksheets into a single workbook.
6. Support appending downloaded data to an existing worksheet.
7. (un)pivoting export of Measure Names/Values tables to make a "wide" sheet. This particular feature could be extended to the VIZ_CSV() export as well.

Several other requirements:

• The code would need to check for the # of rows output due to the 1M row Excel limitation. I'd suggest creating additional worksheets with _1, _2, etc. appended to the worksheet name in this case.
• Unicode needs to be accepted.
• Dates need to be handled well, though I'm not totally sure what "well" means.
• Leading 0's in postal/zip codes need to be handled (or well documented so they don't get truncated).
• The code would need to gracefully handle situations where the workbook was open and not writeable. My thought here is to a) (in the case of append) copy the existing workbook to the temp folder, b) do all writes on the temp version, then c) copy the updated version back to the actual location. If c) fails then an informative error message could tell the user that a prepared workbook exists in the temp folder.
• The code would need to be threadsafe. I'm not sure how this would be accomplished.

[aslabbekoorn]

can we add a |mergexls function to this similar to mergepdf so we can have each view/sheet in a separate excel tab. We just launched vizalerts yesterday at our company and people love it. Out of training session came the following questions. 1. Excel email export and multiple sheets merge into one excel file from one or different workbook views. 2. Send to fileshare of any output format both local c:\ as well as fileshares. 3. Send to onedrive (office365) share. 4. Send to (S)FTP location with option to setup URL and credentials. 5. Option to zip on ore more attachments..

[jdrummey]

Hi Arnold,

To respond to your questions:

1. Merging multiple CSVs like multiple PDFs is a great idea! The idea of a VIZ_XLS() content reference is to explicitly support all supported destinations for content references, so certainly email right away and file export once we get there.

2. File export is covered by issue #26. Export to local C: drives will only be supported if the VizAlerts admin goes to the trouble of creating file shares for them. (VizAlerts can only go as far as the OS and network allow).

3. I'm not sure how a OneDrive (Office365) Share is set up, do you have any information on that? If so can you add it to issue #26?

4. Sending to sftp/ftp would also be great.

5. Zip files are also described in issue #26.

One question I have (that would be better to discuss in issue #26) is that for these last three options where VizAlerts would need to have access to various credentials (e.g. the password for encrypting a zip file) what are the security concerns and requirements? For example does VizAlerts need to blank out the password when writing into the log file? (I'm just going to repeat this in #26).

[petrborilj]

Hi Jonathan,

We are implementing VizAlerts in our company and we really miss the possibility to export data directly to excel.

Is there any chance this funcionality would be implemented in Vizalerts in near future? Or do we have to wait until Tableau implements something first?

Thank you for your contribution so far.

Petr

[ghost]

Hi Petr,

Out of curiosity, what Is the benefit that you expect to get out of a change in format?

Excel natively understands csv format and should open just fine in Excel the same as xlsx format depending on your operating system associations.

On Wed, Apr 18, 2018 at 3:29 AM Petr Boril [email protected] wrote:

Hi Jonathan,

We are implementing VizAlerts in our company and we really miss the possibility to export data directly to excel.

Is there any chance this funcionality would be implemented in Vizalerts in near future? Or do we have to wait until Tableau implements something first?

Thank you for your contribution so far.

Petr

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/tableau/VizAlerts/issues/75#issuecomment-382325115, or mute the thread https://github.com/notifications/unsubscribe-auth/ATUUxopP8t6tBfYbx52OZxK5PYTXPTJUks5tpwePgaJpZM4K0mWh .

[petrborilj]

Hi,

the problem is quite straightforward. Our company doesn't use English in the Office products and to open a csv file in Excel (with data organized in columns) we would need the data separated with a semicolon (comma in Czech version is used as a decimal separator).

To open a csv with comma instead of semicolon we would need to change the settings of Excel or use importing wizzard every time and that could be very inconvenient.

Regards, Petr

[AirCooledNut]

Petr, thank you for the details, that is VERY informative and help establish a use-case.

I wonder if something could be implemented like VIZ_CSV([workbookname/viewname] [,field_separator]) where the field_separator is an optional string character to use instead of the default comma ","?

Given this, maybe we make the currently optional [workbookname/viewname] be required and if the current workbook/view is needed then a token like "me" be used e.g. VIZ_CSV(me, ";") would output the current view into a CSV using the semi-colon as the field separator. I don't know Python so I don't know if VIZ_CSV(,";") would be legal for a function.

[petrborilj]

Adding the field_separator as a optional parameter in VIZ_CSV command would solve our problem, it's a good idea.

[ericknizard]

Is the ability to generate an xls/crosstab file being evaluated as an enhancement? will be sooooo useful !