Difference between revisions of "Excel Importing Guide"
| (13 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
| − | + | <languages/> | |
| − | + | <translate> | |
| + | <!--T:1--> | ||
| + | The instructions below will help you make sure your Excel file is formatted correctly to be imported as a TaticView datasource. | ||
| + | <!--T:2--> | ||
| + | '''You can download a sample file here: '''[[Special:FilePath/Sales.xlsx|Sales.xlsx]]''' | ||
| + | |||
| + | <!--T:3--> | ||
| + | '''If you still have problems importing, try [[Importing Problems]].''' | ||
| + | |||
| + | |||
| + | <!--T:4--> | ||
'''Headers''' | '''Headers''' | ||
| + | <!--T:5--> | ||
Headers are not mandatory, but if present they should be contained in just one row (the first one) and not be in merged cells. | Headers are not mandatory, but if present they should be contained in just one row (the first one) and not be in merged cells. | ||
| − | |||
| − | [[File:headers_right.png|link=]] | + | <!--T:6--> |
| + | [[File:headers_wrong.png|link=]] [[File:headers_right.png|link=]] | ||
| + | |||
| + | |||
| + | |||
| + | <!--T:7--> | ||
| + | '''Avoid Multiple Data Tables''' | ||
| + | <!--T:8--> | ||
| + | Only one table by spreadsheet can be imported. If the data in two different tables can not be joined, they must be imported as two separated datasources. | ||
| − | + | <!--T:9--> | |
| + | [[File:multipletables_wrong.png|link=]] [[File:multipletables_right.png|link=]] | ||
| − | |||
| − | |||
| − | + | <!--T:10--> | |
| + | '''Fix/Remove Empty Rows/Columns''' | ||
| + | <!--T:11--> | ||
| + | Remove or Fix (by adding at least a header) to full empty rows and columns. | ||
| − | + | <!--T:12--> | |
| + | [[File:emptyrowcolumns_wrong.png|link=]] [[File:emptyrowcolumns_right.png|link=]] | ||
| + | |||
| + | |||
| + | <!--T:13--> | ||
| + | '''Remove Merged Cells''' | ||
| + | |||
| + | <!--T:14--> | ||
Data should not contain merged cells for repeated values. All cells must be unmerged and values must be repeated for each cell. | Data should not contain merged cells for repeated values. All cells must be unmerged and values must be repeated for each cell. | ||
| − | |||
| − | [[File:mergedcells_right.png|link=]] | + | <!--T:15--> |
| + | [[File:mergedcells_wrong.png|link=]] [[File:mergedcells_right.png|link=]] | ||
| + | |||
| + | |||
| + | |||
| + | <!--T:16--> | ||
| + | '''Remove Aggregate Rows''' | ||
| + | |||
| + | <!--T:17--> | ||
| + | Files should not contain aggregation rows cells for repeated values. Delete that rows, as TaticView will make all necessary aggregations in run-time. | ||
| + | |||
| + | |||
| + | <!--T:18--> | ||
| + | [[File:summaryrows_wrong.png|link=]] [[File:summaryrows_right.png|link=]] | ||
| + | |||
| + | |||
| + | |||
| + | <!--T:19--> | ||
| + | '''Remove All Table External Items''' | ||
| + | |||
| + | <!--T:20--> | ||
| + | Files should only contain the data table to be imported. All non-related data, as main headers, images, charts, must be removed prior to importing. | ||
| + | |||
| + | |||
| + | <!--T:21--> | ||
| + | [[File:unrelatedinfo_wrong.png|link=]] [[File:unrelatedinfo_right.png|link=]] | ||
| + | |||
| + | |||
| + | |||
| + | <!--T:22--> | ||
| + | '''Check for Invalid Numeric Data''' | ||
| + | |||
| + | <!--T:23--> | ||
| + | Numeric columns should be formatted in Excel prior to uploading the file. This can be done by using the “Convert to number” feature in Excel for all numeric fields. [[Value]] data fields can not contain text or symbols, as they cannot be aggregated. When data is not available, cells may be left blank or as zero (0). | ||
| + | |||
| + | |||
| + | <!--T:24--> | ||
| + | [[File:nonnumeric_wrong.png|link=]] [[File:nonnumeric_right.png|link=]] | ||
| + | |||
| + | |||
| + | |||
| + | <!--T:25--> | ||
| + | '''Normalize your Data''' | ||
| + | |||
| + | <!--T:26--> | ||
| + | Normalization of data (where column headers can be converted into attribute values) will result in better analysis. To do this, first convert metric names to represent attribute values, and then consolidate all metric columns into a single column of data. | ||
| + | |||
| + | |||
| + | <!--T:27--> | ||
| + | [[File:normalization_wrong.png|link=]] [[File:normalization_right.png|link=]] | ||
| + | |||
| + | |||
| + | <!--T:28--> | ||
| + | '''Check the order of the worksheet''' | ||
| + | |||
| + | <!--T:29--> | ||
| + | If the file (XLS or XLSX) has more than one worksheet (tab), make sure that the worksheet to be imported is the first one in the file. TaticView considers only the first worksheet (tab) of the file, ignoring the others. In the example below, we show how the order of the worksheets should be, so that we can import the '''Sales''' worksheet. | ||
| + | |||
| + | <!--T:30--> | ||
| + | [[File:spreadsheet_order_wrong.png|link=]] [[File:spreadsheet_order_right.png|link=]] | ||
| + | </translate> | ||
Latest revision as of 14:30, 30 September 2022
The instructions below will help you make sure your Excel file is formatted correctly to be imported as a TaticView datasource.
You can download a sample file here: Sales.xlsx
If you still have problems importing, try Importing Problems.
Headers
Headers are not mandatory, but if present they should be contained in just one row (the first one) and not be in merged cells.
Avoid Multiple Data Tables
Only one table by spreadsheet can be imported. If the data in two different tables can not be joined, they must be imported as two separated datasources.
Fix/Remove Empty Rows/Columns
Remove or Fix (by adding at least a header) to full empty rows and columns.
Remove Merged Cells
Data should not contain merged cells for repeated values. All cells must be unmerged and values must be repeated for each cell.
Remove Aggregate Rows
Files should not contain aggregation rows cells for repeated values. Delete that rows, as TaticView will make all necessary aggregations in run-time.
Remove All Table External Items
Files should only contain the data table to be imported. All non-related data, as main headers, images, charts, must be removed prior to importing.
Check for Invalid Numeric Data
Numeric columns should be formatted in Excel prior to uploading the file. This can be done by using the “Convert to number” feature in Excel for all numeric fields. Value data fields can not contain text or symbols, as they cannot be aggregated. When data is not available, cells may be left blank or as zero (0).
Normalize your Data
Normalization of data (where column headers can be converted into attribute values) will result in better analysis. To do this, first convert metric names to represent attribute values, and then consolidate all metric columns into a single column of data.
Check the order of the worksheet
If the file (XLS or XLSX) has more than one worksheet (tab), make sure that the worksheet to be imported is the first one in the file. TaticView considers only the first worksheet (tab) of the file, ignoring the others. In the example below, we show how the order of the worksheets should be, so that we can import the Sales worksheet.
