4

I'm using a MySQL database with the Sequel Pro interface, and am new to SQL. I'm attempting to import data from a csv file and one of the columns I am importing into is of type datetime. However, the format I receive the data in is mm/dd/yy hh:mm AM/PM or null. Originally, I modified the type of the column to be varchar to avoid the issue but now I need to perform some date functions on the data that can't be done unless the column has a datetime type and format, so I need a way to convert the incoming data to the proper datetime format.

Additionally, people with no knowledge of SQL or databases are going to be running the import statement so it would be preferable to have them simply click file -- import and not have to enter anything complicated into the mysql command line. Also, after running a query I need to export the data in the same format it came in (mm/dd/yy hh:mm AM/PM or null).

Here are some sample values from the column: 

Completion Time
null
6/16/14 10:33 AM
null
null
6/16/14 13:03 PM
6/17/14 13:53 PM
6/18/14 14:38 PM
6/18/14 14:52 PM
6/19/14 13:13 PM
6/18/14 18:56 PM
6/18/14 19:02 PM
null

A possibly simple solution that I've gathered might not be such a good idea from a couple of hours of googling, would be to keep the column type as varchar then somehow extract just the mm/dd/yy portion of the incoming data, convert that to proper MySQL date format and then perform my date functions.

Anyway any help would be greatly appreciated.

djar
  • 69
  • 1
  • 1
  • 9
  • You will have to convert the incoming date as it is not a properly parsable format for mysql. It can not reliably tell which part is mm and which is dd. Output can later be reconverted using this http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format – ToBe Jul 23 '14 at 16:35
  • Is there a way to set up a stored procedure or trigger to convert the incoming date? – djar Jul 23 '14 at 16:53

2 Answers2

5

That's not very difficult with MySQLs date and time functions. STR_TO_DATE does what you need for the import:

the format I receive the data in is mm/dd/yy hh:mm AM/PM or null.

You get your DATETIME value with

STR_TO_DATE(yourValue, '%m/%d/%y %h:%i %p')

You find the specifiers for STR_TO_DATE at the description of the function DATE_FORMAT

For the export you do the reverse with the already mentioned function DATE_FORMAT with exact the same format string:

SELECT DATE_FORMAT(your_datetime_col, '%m/%d/%y %h:%i %p')

Have a look at this Demo

You can do the conversion at the INSERT statement like that:

INSERT INTO example (date_time) VALUES 
(STR_TO_DATE('09/26/14 07:30 AM', '%m/%d/%y %h:%i %p'));

See it working in the updated Demo

Customizing an import with LOAD DATA INFILE

Let's have a table example with two columns id and date_time as

CREATE TABLE example (
    id INT NOT NULL PRIMARY KEY,
    date_time DATETIME
);

We have further a CSV file example.csv with data like that:

id,date
1,09/26/14 07:30 AM
2,07/23/14 07:30 PM

To import this file with LOAD DATA INFILE, you will use this statement:

LOAD DATA INFILE 'path/on/server/to/example.csv'
INTO TABLE example
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES             -- because of the column headers in the first line
(id, @var1)                -- You've got to map every field of your csv file to a column
                           -- of your table.
                           -- You've got to list the names of the columns of your table,
                           -- not the headers in the csv file.
                           -- if one field should be ignored, use another variable for this
                           -- field.
SET date_time = STR_TO_DATE(@var1, '%m/%d/%y %h:%i %p');

If your dates in the csv files contains the literal string 'null' that indicates a NULL value, then use the CASE operator:

date
09/26/14 07:30 AM
null
07/23/14 07:30 PM

then we've got to use

LOAD DATA INFILE 'path/on/server/to/example.csv'
INTO TABLE example
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n' -- your line endings
IGNORE 1 LINES             -- because of the column headers in the first line
(@var1)                     -- read all parts of the date in variables
SET completionTime = CASE 
                     WHEN @var1 = 'null' THEN NULL 
                     ELSE STR_TO_DATE(@var1, '%m/%d/%y %h:%i %p')
                     END;

The problem with fields containing the separator, in this case the comma, you already solved with enclosing those fields (or simply all) with an enclosing character.

But we really should have a look at your real format.

VMai
  • 10,156
  • 9
  • 25
  • 34
  • My fields are not enclosed in quotes and I changed the field names in my comment for anonymity as you said. The issue is that the datetime column is completely null and the other columns have their data all messed up where some characters from the datetime column appear in the other columns replacing the actual data that shout be there, and then their data gets moved over one column – djar Jul 23 '14 at 21:12
  • @djar Since your Date column is the first one, could you give me one or two rows with the date (that shouldn't be problematic) and anonymized data from the second field. Obviously it's messed up just then. If it works for the date column and the first field, then the rest shouldn't be a great problem. – VMai Jul 23 '14 at 21:16
  • @djar sounds a bit like you have commas inside your data messing up the mapping – Michael McGriff Jul 23 '14 at 21:16
  • @MichaelMcGriff That's what I'm suspecting too. In the lines of `10\20\14, 08:12 AM` or some such. – VMai Jul 23 '14 at 21:24
  • @MichaelMcGriff Thanks I didn't notice that, you're correct. But now why would my datetime column still only contain null values? – djar Jul 23 '14 at 21:26
  • @djar: That seems not difficult: please copy the date to your question, so that we can see the format. – VMai Jul 23 '14 at 21:30
  • @djar my guess is that the `STR_TO_DATE` is failing for some reason. Post some actual values in that column so that we can see what exactly it is trying to convert. – Michael McGriff Jul 23 '14 at 21:31
  • Editted into question – djar Jul 23 '14 at 22:10
  • @djar But there's no comma. And does your CSV file contains indeed the string 'null'? If yes, then we must consider this by the import. – VMai Jul 23 '14 at 22:13
  • It does contain the actual string null. And what I posted was only one column, my issue was arising bc other columns contained commas – djar Jul 23 '14 at 23:48
  • @VMai easier than concatenating fields when dealing with a csv file that contains commas is using the enclosed by clause (e.g. enclosed by '"'). Although I'm still having an issue with all of my datetime values being recorded as null – djar Jul 24 '14 at 04:36
  • @djar you could use case in this case, see last edit. – VMai Jul 24 '14 at 05:55
  • Tried it. Still only getting null values in the datetime column. Here's the code: `load data local infile '/Users/me/Downloads/file.csv'into table tableName fields terminated by ',' enclosed by ‘“' lines terminated by '\r\n' (@var1, a, b, c, d, ...) SET completionTime = Case when @var1 = 'null' then NULL else STR_TO_DATE(@var1, '%m/%d/%y %h:%i %p') end;` – djar Jul 24 '14 at 14:16
0

The real answer to your question:

If you have untechnical people running Sequel Pro imports and need data manipulation, then you need to write and import/export script that users can upload and download an excel sheet from.

However...

I had a similar problem with importing dates with Sequel Pro. So here's a "half" solution. Take from it what you will.

(Note: this question was asked 5 years ago so this is for the benefit of anyone who runs across the question and runs into a similar Sequel Pro problem.)

This is a "half" solution:

Reformat the DateTime inside Excel first.

  1. select column
  2. Go to Format > Cells
  3. Select "custom"
  4. For type use "yyyy-mm-dd hh:mm:ss"
  5. For 'null' values enter an obvious wrong date like "2099-01-01 00:00:00:00".

Import into Sequel Pro.

  1. Open Sequel Pro
  2. Go to View > Show Console (Do this to avoid a Sequel Pro CSV import crash)
  3. Open your database connection and select the table.
  4. Import the CSV.
  5. Note any errors that occur

Clean up your data post-import. (I told you this was a half solution)

  1. In Sequel Pro run this query: UPDATE your_table SET your_column = NULL WHERE your_column = '2099-01-01 00:00:00:00';
Adam
  • 1,887
  • 1
  • 21
  • 21