Как преобразовать дату в файле .csv в формат SQL перед массовой вставкой

У меня есть CSV-файл с парой тысяч дат игр, но все они в формате ММ/ДД/ГГГГ.

2/27/2011,3:05 PM,26,14

(26 и 14 - это идентификаторы команд), и попытка поместить их в SQL таким образом приводит к тому, что 0000-00-00 помещается в поле даты моей таблицы. Это команда, которую я пытался использовать:

LOAD DATA LOCAL INFILE 'c:/scheduletest.csv' INTO TABLE game
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(`date`, `time`, `awayteam_id`, `hometeam_id`);

но опять же, это не соответствовало бы датам. Есть ли способ, которым я могу преобразовать дату, когда он пытается ее вставить? Я нашел еще один SO вопрос, похожий на этот , но я не мог заставить его работать.


mysql sql load-data-infile
person cfrederich    schedule 23.06.2011    source источник
comment
Мой ответ ранее показывался как принятый, но больше не является. Это не сработало, или это гремлин передумал?   -  person Cez    schedule 24.06.2011
comment
@Cez Думаю, я случайно дважды щелкнул по нему и сделал его невыбранным. смешной. Ну вот   -  person cfrederich    schedule 24.06.2011
comment
Весьма признателен. Рад, что решил проблему   -  person Cez    schedule 25.06.2011

Ответы (6)


arrow_upward
8
arrow_downward

Вы пробовали следующее:

LOAD DATA LOCAL INFILE 'c:/scheduletest.csv' INTO TABLE game
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@DATE_STR, `time`, `awayteam_id`, `hometeam_id`)
SET `date` = STR_TO_DATE(@DATE_STR, '%c/%e/%Y');

Для получения дополнительной информации в документации есть сведения об использовании пользовательских переменных с LOAD DATA (примерно на полпути — найдите «Пользовательские переменные в предложении SET» на странице)

person Cez    schedule 23.06.2011
comment
@amosrivera: я добавил ссылку на синтаксис ЗАГРУЗКИ ДАННЫХ - person Cez; 24.06.2011
comment
Я придумал это, или это было принято и теперь показывается как непринятое?? - person Cez; 24.06.2011
comment
@amosrivera: Спасибо, что сохранили мой рассудок! - person Cez; 24.06.2011

arrow_upward
1
arrow_downward

Вы можете использовать переменные для загрузки данных из csv и запускать на них функции перед вставкой, например:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(@datevar, @timevar, awayteam_id, hometeam_id)
SET date = STR_TO_DATE(@datevar, '%m/%d/%Y'),
SET time = etc etc etc;
person haloperidol    schedule 23.06.2011

arrow_upward
0
arrow_downward

Мое предложение состояло бы в том, чтобы вставить файл во временную таблицу хранения, где столбец даты является символьным типом данных. Затем напишите запрос с конверсией STR_TO_DATE, чтобы переместить данные из таблицы хранения в конечный пункт назначения.

person Joe Stefanelli    schedule 23.06.2011

arrow_upward
0
arrow_downward

  1. Преобразуйте поле, которое вы используете для даты, в тип varchar, чтобы оно совместимо с любым форматом.

  2. Импорт CSV-файла

  3. Преобразуйте даты в допустимый формат даты mysql, используя что-то вроде:

    UPDATE table SET field = STR_TO_DATE(field, '%c/%e/%Y %H:%i');
  1. Затем верните тип поля на сегодняшний день
person amosrivera    schedule 23.06.2011

arrow_upward
0
arrow_downward

Используйте функцию для преобразования формата по мере необходимости.

Я не эксперт по MySQL, но http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date выглядит многообещающе.

Если вы не можете сделать это напрямую в команде загрузки, вы можете попробовать создать таблицу, которая позволит вам загружать все значения как VARCHAR, а затем выполнять вставку в вашу таблицу game с помощью оператора select с соответствующим преобразованием.

person BQ.    schedule 23.06.2011

arrow_upward
0
arrow_downward

Если ваш файл не слишком велик, вы можете использовать функцию Excel ТЕКСТ. Если, например, ваша дата находится в ячейке A2, то формула во временном столбце рядом с ней будет =ТЕКСТ(A2,"гггг-мм-дд чч:мм:сс"). Это сделает это, а затем вы можете вставить значения результата формулы обратно в столбец, а затем удалить временный столбец.

person Jennifer Williams    schedule 09.05.2017