Support » Plugin: Participants Database » Convert date from Excel to PDB (MariaDB)

  • Hello!

    First of all, thank you, thank you for this amazing plug-in!! It is exactly what I’ve been looking for, not for a member type database but more for a listing of music performances.

    I am having an issue trying to figure out how to take a date in an Excel spreadsheet and format correctly in the .csv file so it will import into PDB. I’ve done a bunch of Google searches showing what format mySQL/Maria DB is expecting and I’ve been converting the dates to a text based YYYY-MM-DD format. However, when I import the csv file to PDB into a field defined as a date type, I get blank field data. If I export the PDB file back out to CSV, it shows 0000-00-00 as the date. If I manually enter the date into the record, as, for example, 2018-11-01, it enters correctly in the PDB database. When I check the SQL database, the data is stored as 1541030400 for 2018-11-01 so I assume there’s a further reformat from PDB to the SQL standard where dates begin at 1970-01-01.

    Can you tell me how to format the Excel CSV field so that it imports correctly? I seem to be missing something since it’s not being accepted in the import.

    Thanks!

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author xnau webdesign

    (@xnau)

    The way this works is the CSV import/export uses human-readable data where possible. This means that date fields will be exported using the date format determined by your site settings.

    When importing, date fields are parsed (converted from a human-readable format to the UNIX timestamp that is actually stored), so any parseable date format will work. Some formats are problematic…for example purely numeric dates that use the European format as opposed to the American format. In most cases, the plugin can deal with any parseable date format you want to use.

    Timestamps are a little different, they are actually exported in the MySql timestamp format, and are expected to be imported in that same format.

    I hope this helps…if you’re still having trouble with it parsing your dates, there are ways you can help that.

    Thanks for your quick reply and explanation. By synching the format on the import to my site date format, I was able to complete the import correctly.

    Thanks, again, for this great plug-in.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Convert date from Excel to PDB (MariaDB)’ is closed to new replies.