CSV import

Databases
1

I have a CSV which has 3 columns

dateadded from times

when i run

LOAD DATA INFILE '/tmp/trains.csv' REPLACE INTO TABLE Trains FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY  '\\\\' LINES TERMINATED BY '\\\\r\\\
'IGNORE 1 LINES;

i get 0 rows inserted. ( Query took 0.0041 sec )

although the file does have data in there that i require to input.

any one able to assist?

CSV file can be found here

2

could you do a SHOW CREATE TABLE, please

also, i downloaded your csv, and it looks like this –

dateadded,From,Time
1372425854.78,"From West Kirby
Terminates","14:27a
On time"
1372425854.84,"West Kirby  (circular route)
Starts here","14:36
On time"

looks like each row is spread across three lines of csv…

3 
Table	Create Table
Trains	CREATE TABLE `Trains` (
 `dateadded` varchar(50) NOT NULL,
 `from` varchar(100) NOT NULL,
 `times` text NOT NULL,
 PRIMARY KEY (`dateadded`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
4

what about the split rows?

5

sorry i’m a complete newbie?

is the table creation wrong?

6

no

it could be improved, but it’s not wrong

7

any ideas why it does not import the data cause i am at a total loss with this now.

8

perhaps the split rows might have something to do with it

9

Cheers, now to google it! :slight_smile:

10

why?

i posted the example from your file earlier on

you just need to fix your csv file

11

the problem there is the CSV file comes from scrapewiki so i would have to work out how to correct it via python before it’s told to import the data.

my project is a simple train time ticker using a raspberry pi and a 16x2 LCD that will show when the next train is from my local station.
if i import the file using phpmyadmin then it works fine but i need to work a way to automate it so that it can be told to run every few hours to download the new data.

12

no, not with the split rows, no it doesn’t

13

Are you sure that the lines are terminated by carriage return line feed? I downloaded this and tested it on Linux and it worked (I did not have carriage return line feed '\r
’ but only line feed ’
’ as line separator):


mysql> LOAD DATA INFILE '/tmp/trains.csv' INTO TABLE Trains FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY  "\\\\" LINES TERMINATED BY "\
" IGNORE 1 LINES;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from Trains;
+---------------+------------------------------------------+----------------+
| dateadded     | from                                     | times          |
+---------------+------------------------------------------+----------------+
| 1372425854.78 | From West Kirby
Terminates               | 14:27a
On time | 
| 1372425854.84 | West Kirby  (circular route)
Starts here | 14:36
On time  | 
+---------------+------------------------------------------+----------------+
2 rows in set (0.00 sec)