Difference between revisions of "Exporting Reports with Special Characters to Excel"
Ann Vincent (talk | contribs) m (→Import Data) |
|||
| (6 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | In order to support '''Special Characters''' such as French accented letters, | + | In order to support '''Special Characters''', such as French-accented letters, Euro symbols, etc., [[SmartSimple]] uses UTF-8 encoding throughout the system, including when reports are exported. |
| − | When opening files Microsoft Excel always imports using ASCII, so special characters encoded using UTF-8 do not appear correctly. This is a flaw with Excel. In order to export data containing special characters to Excel use one of the procedures outlined below. | + | When opening files Microsoft Excel always imports using ASCII, so special characters encoded using UTF-8 do not appear correctly. |
| + | * This is a flaw with Excel. | ||
| + | |||
| + | In order to export data containing special characters from [[SmartSimple]] to Excel, use one of the procedures outlined below. | ||
| + | |||
| + | |||
| + | ==Import Data== | ||
| + | * Open a blank worksheet in Excel | ||
| + | * From the '''Data''' menu select '''Import External Data''' followed by '''Import Data''' | ||
| + | *:* This will vary slightly depending on your version of Microsoft Excel. Check the Microsoft Website or Help Menu in Excel for assistance. | ||
| + | |||
| + | * [[File:ExcelImportData.png]] | ||
| + | ** Browse to the location of the exported report .txt file and click '''Open''' | ||
| + | ** Select '''Unicode (UTF-8)''' as the '''File Origin''' and click '''Finish''' (UTF-8 normally appears near the bottom of the list) | ||
| + | *: [[File:ExcelFileOriginUTF8.png]] | ||
| + | * | ||
| + | ** Choose the cell you wish to place the data and click OK. | ||
| + | |||
| + | ===CSV Data Security=== | ||
| + | When exporting data into a CSV format, files can become vulnerable to [https://owasp.org/www-community/attacks/CSV_Injection malicious CSV injection attacks]. As a precautionary measure, an additional space will be added at the beginning of any values starting with at signs ( @ ), plus signs ( + ), minus signs ( - ), piping symbols ( | ), or equals signs ( = ). Therefore, when you export a file from Platform3 into a CSV format and later need to import that file into another system, please ensure you trim the additional space in front of any values. | ||
==Copy and Paste from Notepad/Wordpad== | ==Copy and Paste from Notepad/Wordpad== | ||
| Line 16: | Line 35: | ||
'''Note:''' Notepad will not open very large reports, so another text editor such as Wordpad or Notepad++ should be used. Microsoft Word is not recommended. | '''Note:''' Notepad will not open very large reports, so another text editor such as Wordpad or Notepad++ should be used. Microsoft Word is not recommended. | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| + | '''Note:''' When using the Copy and Paste method for '''csv''' exported files you will need to use the '''Text to Columns''' function of Excel after following the above procedure. | ||
==See Also== | ==See Also== | ||
| − | * [[Exporting | + | * [[Exporting Reports]] |
| − | [[Category:Reports | + | [[Category:Reports]] |
Latest revision as of 14:15, 10 February 2021
In order to support Special Characters, such as French-accented letters, Euro symbols, etc., SmartSimple uses UTF-8 encoding throughout the system, including when reports are exported.
When opening files Microsoft Excel always imports using ASCII, so special characters encoded using UTF-8 do not appear correctly.
- This is a flaw with Excel.
In order to export data containing special characters from SmartSimple to Excel, use one of the procedures outlined below.
Import Data
- Open a blank worksheet in Excel
- From the Data menu select Import External Data followed by Import Data
- This will vary slightly depending on your version of Microsoft Excel. Check the Microsoft Website or Help Menu in Excel for assistance.
-
- Browse to the location of the exported report .txt file and click Open
- Select Unicode (UTF-8) as the File Origin and click Finish (UTF-8 normally appears near the bottom of the list)
-
- Choose the cell you wish to place the data and click OK.
CSV Data Security
When exporting data into a CSV format, files can become vulnerable to malicious CSV injection attacks. As a precautionary measure, an additional space will be added at the beginning of any values starting with at signs ( @ ), plus signs ( + ), minus signs ( - ), piping symbols ( | ), or equals signs ( = ). Therefore, when you export a file from Platform3 into a CSV format and later need to import that file into another system, please ensure you trim the additional space in front of any values.
Copy and Paste from Notepad/Wordpad
- Configure the report to export with txt as the file extension
- Export the report
- Open the report in Notepad or another text editor.
- You may need to right-click on the file and select Open With depending on what application your PC is configured to use for .txt files.
- Select and Copy the entire text of the report (Ctrl-A and then Ctrl-C)
- Open a blank worksheet in Excel
- Select the top-left cell and paste the text you copied (Ctrl-V)
Note: Notepad will not open very large reports, so another text editor such as Wordpad or Notepad++ should be used. Microsoft Word is not recommended.
Note: When using the Copy and Paste method for csv exported files you will need to use the Text to Columns function of Excel after following the above procedure.
