Formatting date and time values in CSV files

Identifying the columns correctly

The first step in importing a CSV file into Easy Excel Analysis is to ensure that each column is correctly identified.  In this example, we will work with the sample CSV file:

When we first open this file in Easy Excel Analysis and click on the Preview button, an error is raised regarding the number of columns.

Our CSV file encloses text in quotes, so we’ll need to select the Field values are quoted using “ option (since we’re using double quotes as the quote character), and click Preview again:

All the columns seem to be in order, so we can proceed.

Adjusting the date format

On the next page, Easy Excel Analysis has set up the date and time formats based on our computer’s settings.

The date format is different from that in our CSV file:

If we continue to the next step, our OrderDate column will be identified as a Text field, because the date format in the CSV file does not match our settings.

Thus, we’ll need to change the date format settings accordingly.  Go back to the previous step, and select the correct date format from the list of pre-defined formats, or enter the format manually.  Note that d represents the date, m for month, and yyyy for the year.

Once we have set up the date format to correctly match that in our CSV file, Easy Excel Analysis will set up the OrderDate column as a Date and time column.

If your date columns contain month names e.g.

then just enter a blank value as the date format.  Easy Excel Analysis will then try to parse the value as best as it can.

Adjusting the time format

Adjusting the time format is similar to the steps described above for date formats.  You can manually enter a format value if none of the listed formats match.



Leave a Reply

Your email address will not be published. Required fields are marked *