Ajelix BI

āŒ˜K
  2. Home
  4. Docs
  6. Ajelix BI
  8. Get Started
  10. How to format files before data upload?

How to format files before data upload?

To create reports with Ajelix BI first, you need to upload data files. This guide will explain acceptable data formats and walk you through each step of data upload.

Table of contents:

  1. Excel file
  2. Google Sheets file
  3. CSV
  4. Copied data

Currently, itā€™s possible to upload Excel, CSV or connect with Google Sheets file. In the future, weā€™re planning to expand the horizon with different file types ā€“ so stay tuned.

How To Format your Excel file

Before you venture into the world of Ajelix BI, your Excel data needs preparation. Here are a few things to keep in mind when using Excel as a data source.

1. The data visualization tool will take data only from the first sheet. Be aware that if you upload a file with several sheets system will process data from Sheet1 and the rest will be ignored. If you want to visualize all data from the workbook make sure to transfer data to one sheet.

2. All data should start from cell A1. If your data table starts from cell C2 or other cells make sure to move it A1.

start data from cell a1 for successful bi reporting - ajelix guide
Example on how to format Excel file correctly. Credit: Ajelix

3. Your Excel file can have an unlimited count of rows and columns.

4. Make sure your file consists of only one data table in a sheet. Check the example below to avoid blanks in your charts šŸ‘‡

bad example of data file formatting in excel with several columns
Example on how to format Excel file correctly and exclude several tables. Credit: Ajelix

5. Format cells as ā€œDateā€ to create a time series chart that summarizes big date data. Hereā€™s how šŸ‘‡

change cells to data format - ajelix bi upload
Excel screenshot on how to format data as date. Credit: Ajelix

6. Remove unnecessary formatting (except for the date format)

Remove currency formatting if you want to visualize data as numbers. The system won’t be able to recognize currency as a number.

Remove any other custom format that you have set for the data. As it could cause an error and you won’t be able to upload your file.

7. Format your headers correctly:

  • start with a letter (never start your headings with numbers, e.g. “21Data”),
  • make sure your headers are formatted as plain text, avoid date formats or any other custom formatting,
  • consisting of letters, numbers, and underscore (_) symbols only. Don’t include symbols such as %,$,&,|, etc.

How To Format your Google Sheets file

Google sheets files are subject to the same formatting conditions as Excel.

How To Format CSV file

CSV file import is pretty easy and it doesnā€™t have as many restrictions as Excel.

1. Similar to Excel your data should start at cell A1 and you should avoid adding blank columns or rows unless you want to see that in charts.

2. Format dates with ā€œDateā€ formatting to do that follow the instructions above.

How To Format Copied Data

To ensure your pasted data is processed correctly, please verify that your copied data meet this criteria:

  • Data is in a supported format: Excel, CSV, or JSON
  • For Text: Data is formatted in columns and rows
  • For Text: Column names are present in the first row
  • For Text: You can paste data directly from Excel into the text field
  • For JSON: Data is in an array of objects, each representing a row; keys are column names
  • Avoid special characters in column names (use only alphanumeric characters A-Z, 0-9 and underscore)

With your data organized and verified, itā€™s time to upload it on Ajelix BI.

Didn’t find the answer to your question? Contact us via: support@ajelix.com