SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 09.02.2009 18:34:15

Twix
Завсегдатай
Зарегистрирован: 21.02.2008
Сообщений: 88

Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Возникла необходимость занести данные из txt файла в таблицу БД MySQL.
Формат данных хранящихся в файле имеет следующий вид :

356837062;09.01.2008 10:29:59;"DSFD";"ETTT";"B";31.83;6;19098
356837063;09.01.2008 10:29:59;"EQBR";"YYRY";"S";31.83;9;28647
356837064;09.01.2008 10:29:59;"TTTY";"WERT";"B";31.83;35;111405
356837065;09.01.2008 10:29:59;"RYRY";"UITR";"B";31.83;465;1480095
356837066;09.01.2008 10:29:59;"YTRY";"OPUT";"S";31.83;168;534744

Подскажите , можно ли это сделать средствами MySQL ???
Или стоит писать программу для чтения строк из файла и последующего insert into ???

Неактивен

 

#2 09.02.2009 18:40:38

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Неактивен

 

#3 13.02.2009 02:04:13

Twix
Завсегдатай
Зарегистрирован: 21.02.2008
Сообщений: 88

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Респект за наводку.
Только вот у меня такая сложность . Текстовый файл содержит 10 полей , а таблица в которую будет производится загрузка - 7 . При этом порядок полей в файле не соответствует порядку следования полей в таблице .
Если я правильно понял из мануала , есть возможность выбрать поля таблицы которые можно загрузить , а как  выбрать поля в файле которые хотелось бы получить посредством запроса ???
Или тупо создать дополнительные поля в таблице , для загрузки дополнительных ненужных полей из файла ???

Неактивен

 

#4 13.02.2009 12:08:26

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:


LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);


Т.е. вместо ненужных полей ставишь переменную.

Неактивен

 

#5 15.02.2009 22:09:33

Twix
Завсегдатай
Зарегистрирован: 21.02.2008
Сообщений: 88

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Хм , это всё конечно хорошо , только вот у меня в таблице поле с значением TIME,  а в файле TIMESTAMP .
Есть ли какие нибудь возможности для того чтобы из файла считывалась только часть , относящаяся к TIME ???
Или прийдётся как то извращаться , подскажите .

Неактивен

 

#6 16.02.2009 17:52:55

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Те же действия, только потом еще обрезаете так, как Вам надо через SET.

Неактивен

 

#7 17.02.2009 11:12:02

Twix
Завсегдатай
Зарегистрирован: 21.02.2008
Сообщений: 88

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Да спасибо , составил вот такой запрос :

LOAD DATA LOCAL INFILE "C:\\DATA_EXPORT\\data.txt"

    INTO TABLE `dataauto`.`data_ticks`
    FIELDS
        TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'


    LINES TERMINATED BY '\n'
    IGNORE 1 LINES(id,@d,@dum,Symbol,Operation,price,vol,Turnover)
SET T=DATE_FORMAT(@d,'%H:%i:%s');


MySQL выдаёт сообщение об ошибке : ERROR 1048 (23000): Column 'T' cannot be null ???
Непонятки , когда закачивал в туже таблицу данные из файла ( где поле имело тип Time , такое же как и у меня в таблице ) без использования SET ,  таких  ошибок не возникало .
Если я правильно понимаю в команде SET нельзя использовать такие функции , а как же быть тогда с конвертацией поля TIMESTAMP до TIME  ???

Неактивен

 

#8 17.02.2009 13:12:24

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

попробуйте
SET T=IFNULL(DATE_FORMAT(@d,'%H:%i:%s'), now());

Неактивен

 

#9 18.02.2009 00:42:45

Twix
Завсегдатай
Зарегистрирован: 21.02.2008
Сообщений: 88

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

С использованием такого запроса ( SET T=IFNULL(DATE_FORMAT(@d,'%H:%i:%s'), now())  )  данные из файла загружаются . Но только вот now() портит всю малину , мне надо что бы данные TIME были такие же как и в txt файле ?!

Неактивен

 

#10 18.02.2009 00:46:19

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

now() срабатывает только в случае, когда на этом месте был бы NULL

Неактивен

 

#11 18.02.2009 01:02:29

Twix
Завсегдатай
Зарегистрирован: 21.02.2008
Сообщений: 88

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

"   IFNULL(expr1,expr2)
Если expr1 не равно NULL, то функция IFNULL() возвращает значение expr1, в противном случае - expr2.   "

Так вот и получается , что при загрузке данных в моей таблице появляется  не те данные TIME из файла , а так же получаю предупреждение " Truncated incorrect datetime value: '19.11.2007 10:29:29' "  -  ( некоторое время назад протестировал и получил в поле  T , в каждой строке , одинаковое значение  00:19:49  ).
???

Отредактированно Twix (18.02.2009 01:18:40)

Неактивен

 

#12 18.02.2009 03:27:06

coin
Гуру
Зарегистрирован: 15.07.2008
Сообщений: 66

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Код:

SET T=SUBSTR(@d,-8);

Дата в MySQL передаётся в формате YYYY-MM-DD или YYYYMMDD.

Неактивен

 

#13 18.02.2009 10:48:44

Twix
Завсегдатай
Зарегистрирован: 21.02.2008
Сообщений: 88

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Дата в файле ( из которого производится загрузка ) в  MySQL  имеет формат 19.11.2007 10:29:29  .

Отредактированно Twix (18.02.2009 10:49:23)

Неактивен

 

#14 18.02.2009 15:54:43

coin
Гуру
Зарегистрирован: 15.07.2008
Сообщений: 66

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

И я о том же. А должна YYYY-MM-DD HH:MM:SS, иначе ф-я DATE_FORMAT вас не поймёт или поймёт, но не правильно, поэтому нужно отрезать только часть HH:MM:SS

T=SUBSTR(@d,-8)

Неактивен

 

#15 19.02.2009 00:25:06

Twix
Завсегдатай
Зарегистрирован: 21.02.2008
Сообщений: 88

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

"  SUBSTR(,X,Y)   Извлекает Y символов из строки начиная  с позиции X.   "

Спасибо ОГРОМНОЕ !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

С таким запросом SET T=SUBSTR(@d,-8) -  всё стало на свои места  , только вот поясните почему минус 8 - с какой точки начинаем отсчёт ???

Неактивен

 

#16 19.02.2009 00:32:18

coin
Гуру
Зарегистрирован: 15.07.2008
Сообщений: 66

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

substr

"It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning."

Отрицательный pos - смещение вырезаемой подстроки от конца исходной строки, т.е. SUBSTR(@d,-8) = взять последние 8 символов строки.

Отредактированно coin (19.02.2009 00:39:30)

Неактивен

 

#17 03.06.2009 14:41:14

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Не хотелось бы плодить новую тему, поэтому пишу здесь. Как загрузить в таблицу файл, если разделитель переменной длинны?

Запись типа такой:

Код:

1243856775.865[\s\s]38587[\s]10.2.1.26[\s]TCP_MISS/200[\s]370[\s]GET

где [\s...] - это я так показал группу пробелов, их может быть один или больше. Если использую terminated by ' ', то колонок получается больше чем нужно, естественно.

Неактивен

 

#18 03.06.2009 16:47:36

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Выглядит как форматированный вывод; там точно не табуляции?

Насколько я знаю, у MySQL нет способа засасывать данные с переменным числом колонок wink
Но можно, например, пропустить через какой-нибудь тупой фильтр типа
cat file | perl -ne 's/\s+/ /g; print' > outfile

Неактивен

 

#19 03.06.2009 17:03:53

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Нет, именно пробелы. Жаль, что нет такой фичи. А было бы неплохо, что-то типа регулярки terminate by '\s+' smile

У меня виндовый сервер - есть какие-нибудь аналоги этой команды по винду?

Я щас вообще доисторически делаю - гружу файл в ultraedit и Ctrl+R (заменой) меняю пробелы на один. Но это конечно не выход, да и не уверен я что UE справится с файлом в пару гигабайт (сейчас всего то 300M) smile

Неактивен

 

#20 04.06.2009 04:31:31

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Разобрался. Для потомков сообщаю, что есть набор портированных под win юниксовых утилит, в том числе и cat - http://gnuwin32.sourceforge.net/packages/coreutils.htm . Дальше дело техники.

Паша, спасибо за наводку, ну и вообще smile

Неактивен

 

#21 04.06.2009 20:38:09

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Классно, я думал под винду надо cygwin ставить целиком smile

Неактивен

 

#22 11.10.2016 10:02:53

MQLab
Участник
Зарегистрирован: 11.10.2016
Сообщений: 2

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Twix написал:

Да спасибо , составил вот такой запрос :

LOAD DATA LOCAL INFILE "C:\\DATA_EXPORT\\data.txt"

    INTO TABLE `dataauto`.`data_ticks`
    FIELDS
        TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'


    LINES TERMINATED BY '\n';

Часть обрезал, что для моей задачи не нужно.

Люди добрые подскажите, можно ли разделять строку по столбцам базы, если в разных строках разные разделители?

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

aaa:123
bbb;321
ccc[пробел]231

Неактивен

 

#23 11.10.2016 11:00:39

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

имхо, хорошего способа нет
можно:
1. загнать всё в базу, не разделяя, в доп столец
2. несколько update для каждого разделителя, которые будут резать строку и раскидывать по столбцам
3. удаление доп столбца

Неактивен

 

#24 11.10.2016 14:44:10

MQLab
Участник
Зарегистрирован: 11.10.2016
Сообщений: 2

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

vasya написал:

имхо, хорошего способа нет
можно:
1. загнать всё в базу, не разделяя, в доп столец
2. несколько update для каждого разделителя, которые будут резать строку и раскидывать по столбцам
3. удаление доп столбца

1-й и 3-й пункт понятен, помогите примером как написать update

Есть таблица, в ней 3 столбца

a1, a2 и tmp

в tmp импортировал данные, как данные у которых разделитель ";" разделить между a1 и a2

Неактивен

 

#25 11.10.2016 14:56:12

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Возникла необходимость занести данные из txt файла в таблицу БД MySQL

Посмотрите примеры в этих темах:
http://sqlinfo.ru/forum/viewtopic.php?id=591
http://sqlinfo.ru/forum/viewtopic.php?id=2242

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson