I am attempting to import an Excel file with a Date column into Watson Analytics, however the Date column is not recognized as such, and the column is imported in text format.
How can I upload the Excel file and have Watson Analytics recognize the Date column?
When data is being brought into Watson Analytics, the various columns of the data set are automatically identified as having a particular format in order to allow the tool to properly digest the information presented. Currently, the available data formats are: Number, Text, and Duration (Time interval) and Date. It should be noted that not all of these options are necessarily available for all columns, depending on the nature of the data itself (i.e. a column containing only text values may not be formatted as Number).
In the case of the ‘Date’ format specifically, users have found that Watson Analytics does not always identify a given column as they would have expected. Without the ability to modify the column type in Watson Analytics directly, we need to change the source data by opening the file in Excel and manually formatting the column in question as ‘Date’. The properly formatted file may then be used to replace the incorrectly formatted version, and we will see that WA now recognizes the column in question as having the Date format as expected.
The first thing to try is to open the file in Excel and highlight the Date column in question. Use the ‘Format Cells’ feature to set the column as a Date. Upload the updated file to Watson Analytics and confirm if the issue is resolved.
In rare instances, the file may be corrupt and the formatting may not be applied correctly by Excel. If you experience this behavior, follow the troubleshooting steps outlined below.
- Highlight the column which is not formatted correctly
- Go to the Data Tab
- From the Data tab, Select ‘Text to Columns’
- This will open a Wizard called “Convert Text to Columns”
- You will see 2 choices for your original data type: Delimited or Fixed Width
- If your current date format has commas, tabs, slashes, etc., then click Delimited.
- Click Next until you see a screen that lets you select the data format that you want
- Select the DATE button
- Then, from the drop down, select the new date format you want to use.
- Click Finish
- Re-Upload Excel File to Watson Analytics and confirm Date column is now recognized
You will also notice that because Watson Analytics now recognizes the column as a date, it will automatically create a set of Hierarchy columns based on the date format “Year (Date), Month (Date), and Day (Date)”. This is the expected behavior when a data set is recognized to contain a column in date format. You may now proceed with your analysis while leveraging the Hierarchy columns Watson Analytics has generated for you.
Please review the relevant documentation regarding Adding Spreadsheets and .CSV files as a data asset, Creating Hierarchies, and Resolving problems with Dates for a more in depth discussion of these issues.