Converting a string to date/time data type

Contents[Hide]

1. Overview

This article shows you how to change the data type of a column to Date/Time in an Excel or flat file data connector. With a date/time data type, you can use a time dimension, customize the format, and more.

2. Connect to a flat file or Excel file

To import a flat file or an Excel file, follow the steps in Drag and drop an Excel file. You can also choose to create a data connector yourself for an Excel file or flat file instead.

After creating the data connector, you can drag a sheet or table onto a metric set or dashboard to view it.

Before changing data type
Before changing data type

In the example above, the date column was actually entered into Excel as a number containing a year, month, and day, so a column of numbers was imported into Dundas BI. It is also common for a column of dates to be entered as text in Excel and imported into Dundas BI as text (the String data type), which can't be reformatted or used with a time dimension.

3. Setting up the date/time data type

As an alternative to converting the values into dates in Excel, the Dundas BI data connector allows you to customize the structure of the imported data including each column's data type.

Right-click the data connector created for the Excel file and select Edit.

Edit the data connector
Edit the data connector

Click on Define structure near the bottom of the dialog.

Define structure
Define structure

Select the table name or sheet with the column you want to edit.

Select a table
Select a table

Select the column you want to edit. If the columns are not shown, click on Re-discover table.

Select the column
Select the column

Under the selected column, set Data Type to Date/Time.

Data Type of column
Data Type of column

Once the data type has been selected, the Date Format field appears, where you can enter the format that matches the dates in your column so that each of its components (e.g., year, month, and day) are imported correctly. This format can be set the same way as formats elsewhere in Dundas BI, including time dimensions.

Enter the date format
Enter the date format

Click the submit button at the bottom of the dialog to save the changed information.

This converts the data into a date format, which allows you to use time dimensions, customize the format, and more.

After changing the data type
After changing the data type

4. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri