You can pull files from either your computer or a variety of online stores. For information on accessing files from online sources, see Connecting to files from online sources. You can access either Excel or CSV files (flat files) from your computer.
This article covers the following topics:
Connecting to Excel and CSV files
You can connect to files from your computer in the Data source Builder. For files larger than 50MB, please use BIME Desktop.
- Click the Data sources library icon () on the left panel.
- Click the + New button in the right corner to add a new data source.
- Select either Excel or a CSV file icon.
- Enter your new connection name and category.
- Underneath Source file, click Pick.
- To choose a file from your computer either select Choose file button or drag the file to your browser.
- For Excel files, you can select a sheet to use as your dataset.
- For CSV files, you can specify parameters to change how your dataset will be presented. For more information, see Editing CSV specifications below.
- Click the Next button to view the schema of your dataset. Your data will automatically be assigned as either a metric or attribute. For more information on metrics and attributes, see Adding metrics and attributes.
- Click the Next button to store your data.
- If you would like to edit the time zone or caching options for your results, select the Advanced option at the top of your page.
- Click the Save button when you are finished.
Excel and CSV best practices
Formatting Excel files
- Remove empty lines and empty columns in your spreadsheet. Data will stop being read at the point of a break, so your data capture will not be complete.
- Avoid using column headings over multiple cells. Any data in a merged-cell header will only relate to the first column. Any data in a column with no header or a merged header will be given a random heading. This could cause your data set to be difficult to read in a complex data source.
- Fill in blank cells and missing headers.
- Remove text that is not part of the main data table. For example, any titles or footnotes should be removed.
- Remove totals and subtotals. You can add totals and subtotals in the Query Builder.
Below are examples of Excel sheets with poor and correct format.
Editing CSV specifications
To ensure that your dataset is presented in the correct format, you can edit the parameter specification options located beneath the source file selector. Default values will be automatically assigned.
The following are the available specification options you can edit:
- Data separators
- The text delimiter
- The decimal separator
You can also edit the format of any date columns in the file. For example, you can use dd/MM/yyyy for a 31/01/2014 format. Click the More info button for guidance on date formats.
You can check if your data is parsed correctly by clicking the Preview button.