Configure data formats and types for CSV file import

Author: Stacy Smith

Last update: December 18, 2024.

When uploading a CSV file to BI Builder, configure the data formats and types for columns. This ensures that BI Builder recognizes and processes the data correctly in the dataset.

If you skip this step during import, the dataset might not display correctly. For example, if your CSV file has dates like 2025-03-01, but the settings use the month/day/year (MM/DD/YYYY) format, BI Builder won't recognize them as dates. This could cause the column to stay empty or display it as text.

This article shows how to configure columns for dates, numbers, and text to display data correctly.

Once you save the dataset, you can't change the data formats and column types. To avoid errors, double-check the settings before saving.

Check data formats in CSV file

Before uploading, open the CSV file in a text editor to review the values and check the data in each column. Make sure to set the date, time, and number formats in BI Builder. For example, if a date appears as 2024-02-18, choose the year-month-day (YYYY-MM-DD) format. If an amount appears as 2 000,00, select a comma as the decimal separator.

Configure data formats in BI Builder

Go to BI Builder > Analytics hub > Datasets > Create. Upload your CSV file and proceed to Data format.

Match the data formats to the entries in your CSV file.

Date: Choose the format that matches your CSV file. If dates are shown as 2024-02-18, select the YYYY-MM-DD format. If you select a different format, BI Builder won't recognize the dates, and the column will stay empty.
Date and time: Set the format for columns that include both date and time. For example, if entries appear as 2025-03-01 15:45:30, choose the YYYY-MM-DD hh:mm:ss format. If you select a different format, the date and time won't be displayed.
Money: Specify the separator between the whole and decimal parts. If monetary values are specified as 12345,67, select a comma.
Number: Select the separator for numbers with a decimal part. For numbers like 1.23, select a dot.

Use a custom data format to define how dates or date-time entries are shown in the CSV file, ensuring BI Builder recognizes them correctly. This is helpful if the data is in a non-standard format not included in the template list.

For example, the date and time values are shown as 2023 05 01 09:30:00, separated by a space, where:

2023 is the full year.
05 is the month with a leading zero.
01 is the day of the month with a leading zero.
09:30:00 represents hours, minutes and seconds in 24-hour format with leading zeros.

To display the data correctly in BI Builder, specify the format for the Date and Time fields as YYYY MM DD hh:mm:ss, where:

YYYY is the full year, like 2023.
MM is the month with a leading zero, such as 05 for May.
DD is the day of the month with a leading zero, like 01.
hh:mm:ss represents hours, minutes and seconds, for example, 09:30:00.

If the file data is specified in a non-standard format, review the table to specify the needed format in settings.

Supported date formats

For dates:

YYYY: Full year, e.g., 2023.
YY: Last two digits of the year, e.g., 23 for 2023.
MM: Month with a leading zero, e.g., 01 for January.
DD: Day of the month with a leading zero, e.g., 05.

For time:

hh: Hours in 24-hour format with a leading zero, e.g., 09.
mm: Minutes with a leading zero, e.g., 07.
ss: Seconds with a leading zero, e.g., 05.

For date and time separators:

Dot (.) e.g., 2023.05.01
Hyphen (-) e.g., 2023-05-01
Slash (/) e.g., 2023/05/01
Space ( ) e.g., 2023 05 01
Colon (:) for time, e.g., 14:30:00

Preview data display

The data preview section shows how your data will appear in the dataset. If you see empty values in the dataset columns, check if the data formats match those in the CSV file. Adjust the settings and check the preview. If the format is wrong, columns might show empty values or misinterpret the data.

Set types for dataset columns

After setting the data formats, go to the Dataset columns section and choose the type for each column to match the data format in the CSV file.

For example, if the file contains order dates, set the column type to Date to analyze date-related data. When types are set correctly, you can use the data in reports and filters, perform calculations, and build charts.

Date: Use for columns with dates to filter and group data by time. If the date format is set as YYYY-MM-DD and the column type is Date, BI Builder will recognize and display the values correctly.
Number: Use for columns with integers or decimals to perform calculations.
Money: Use for monetary values, so BI Builder can process amounts accurately.
Text: Use for columns with text data, like names and other information.

In brief

When uploading a CSV file to BI Builder, configure the data formats and types for each column to ensure the data is recognized and processed correctly.
Before uploading, open the CSV file in a text editor to review the values and check the data in each column.
In BI Builder, configure the formats and types to match the file data. For example, for dates like 2024-02-18, select the YYYY-MM-DD format.
Use the data preview section to verify the format settings before saving the dataset.
Once you save the dataset, you can't change the data formats and column types. To avoid errors, double-check the settings before saving.

Read also:

Was this information helpful?

Integration specialist assistance

That's not what I'm looking for

Complicated and incomprehensible text

The information is outdated

It's too short. I need more information

I don't like the way this tool works

Go to Bitrix24

Don't have an account? Create for free

Create a dataset from a CSV file in BI Builder BI Builder datasets: workflows BI Builder datasets: deals BI Builder datasets: companies and contacts BI Builder: solving CSV file import errors BI Builder datasets: Smart Process Automation BI Builder datasets: tasks and projects BI Builder datasets: activities, CRM stages, and CRM item links BI Builder: how to use ad account data BI Builder datasets: leads

Bitrix24Care