Wednesday, 03 August 2016 10:14

    How to convert CSV date format into MySQL date format in Excel

    Written by
    Rate this item
    (1 Vote)
    How to convert a 10 Digit Number (or Unix Timestamp) into a date in excell How to convert a 10 Digit Number (or Unix Timestamp) into a date in excell Microsoft

    In this article, you will find a simple way to format CSV dates ready to import in mySQL database

    Exported date fields from mySQL become strings in Excel CSV as follows 01/05/2016 00:00, The following simple tutorial explains how to convert the dates back into MySQL format

    • Open your CSV in Excel or Export your MySQL DB as CSV in excel.
    • Select the cell containing the date you wish to convert.
    • Select "custom" (or "more Number formats") from the format cells dropdown (See image below).

      Converting CSV date to MySQL dates screenshot 1

    • In the type field type Enter “yyyy-mm-dd hh:mm:ss” (see image below)

      Converting CSV date to MySQL dates screenshot 2

    • Select the cell, copy it and paste the format to all other cells containing dates.

      Converting CSV date to MySQL dates screenshot 3

    • Import your CSV into MySQL.

     

    If you like this article please add a link to it from your site, blog, facebook page, twitter or any online page. Thank you.

     

     

     

     

    Read 2784 times Last modified on Tuesday, 15 May 2018 10:49
    Published in Microsoft Excel
    Tagged under
    Fabien Bello

    Fabien Bello has been working in web and app develoment since 1997. He has held numerous positions from game developer to head of creative and product development for major firms such as Xerox, Gala Coral Group. Fabien continues contributing to online community, owns and manages several websites as well as offering consultancy to all size businesses.

    Latest from Fabien Bello

    Related items

    More in this category: « Using Concatenate Function in Excel How to change a text to lower case and replace space with a dash in Excel »

    2 comments

    • Comment Link Fabien Tuesday, 02 April 2019 08:23 posted by Fabien

      When you export, try to export as CSV rather than csv for Excel.

    • Comment Link Luke Sunday, 05 August 2018 18:41 posted by Luke

      So I exported a MySQL table to CSV format, opened in Excel and the date fields appear like this "1491872184" instead of your '01/05/2016 00:00' format. I tried your solution but it doesn't work.

    Leave a comment

    Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

    back to top