Jump to content
lbb

Date Format Problem in downloaded CSV file

Recommended Posts

When downloading the csv file of my results into Excel 2016 the Date column [mm/dd/yyyy hh:mm:ss] is aligned for each respective month alternating left and then right.  Eg June 2019 is aligned right and May 2019 is aligned left.   

The dates which are aligned right act as Date fields but the dates aligned left do not act as Date fields.

Have tried:

- aligning all column data to the right

- TRUNC function followed by

   - using Text to Columns to correct the issue

- LEFT function to strip just the date element and not the time - this only works for the right aligned data

 

Having alternate months sorted by alignment is good for initial appearance but it seems to be at the expense of corrupting the data.

Is there a fix that either I can do in Excel, or that TMN could do to in the data presentation to resolve this.?

 

Many thanks.

 

Share this post


Link to post
Share on other sites

Can you post a screenshot or recording of what you're seeing.  There isn't supposed to be any alignment like you're describing.

 

Opening with sheets on Mac

Screen-Shot-2019-06-06-at-7.24.57-PM-min.png

 

Opening with Google Sheets

Screen-Shot-2019-06-06-at-7.26.42-PM-min.png

 

Opening the same file with a raw txt editor shows the true CSV (Comma-separated values) content

Screen-Shot-2019-06-06-at-7.27.56-PM-min.png

 

I think that Excel is doing its own formatting.  Post a screenshot so I can see what you're seeing on your screen.

 

 

Edited by CA3LE
minify images

Share this post


Link to post
Share on other sites

Expanding column A to accommodate the data this is is the screenshot of how excel aligns the date column.

In fact it is not alternate months but seemingly a random split.

Those aligned left are text fields and those aligned right are numeric or date fields.

Of the most recent 200 lines they are split as follows

image.png.e73bdc79ebaea5e374342fd588ea5e07.png

image.thumb.png.9b36023739caf34127bd2164b8bb4b93.png

 

 

 

Share this post


Link to post
Share on other sites

From a quick check on my end, the issue is that Excel expects dates to be formatted as DD/MM/YYYY (how we write dates in Ireland), but the CSV file has them written as MM/DD/YYYY, the way the USA writes dates.

 

When Excel reads in the CSV file, it read the lines like 06/01/2019 as the 6th January 2019, 8/02/2019 as the 8th February 2019 and so on until it reaches a 13 or higher digit in the middle, such as 05/13/2019.  As there is no 13th month, it will treat this as a text field and in turn left-align them.  This continues until it reaches a line that contains a digit of 12 or less in the middle of the date, which again it reads as a valid month.

 

Microsoft recently changed the CSV importing process in Office 365 (which I use), so these steps below may not correspond with your Excel version.

 

Go into the Data column (1), click 'Get Data' (2), 'From File' (3) and then 'From Text/CSV' (4):

Excel import results CSV step 1.png

 

Choose the downloaded CSV file, then click 'Transform Data':

Excel import results CSV step 2.png

 

Right-click the Column1 heading (1), then go into 'Change Type' (2) => 'Use Locale...' (3):

Excel import results CSV step 3.png

 

Choose 'Date/Time' for the Date Type field (1), then 'English (United States)' for the Locale field (2), then click 'OK':

Excel import results CSV step 4.png

 

Finally click the "Close & Load" icon (4 in above screenshot) and it should import and present them in your locale date format, e.g. dd/mm/yyyy for my region: 🙂

Excel import results CSV step 5.png

Share this post


Link to post
Share on other sites

Sean,  Your solution worked!  Thank you so much for sorting this mystery.  I am in the UK so the date format is the same as for Ireland.   Another slice of Excel knowledge gained by me .

 

CA3LE - Yes please - if you can add the site option for the date format that will make it a whole lot easier for those of us on this side of the pond.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...