SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: CSV import

  1. #1
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CSV import

    I have a CSV which has 3 columns

    dateadded from times

    when i run

    Code MySQL:

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    could you do a SHOW CREATE TABLE, please

    also, i downloaded your csv, and it looks like this --
    Code:
    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...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    what about the split rows?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry i'm a complete newbie?

    is the table creation wrong?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by txt3rob View Post
    is the table creation wrong?
    no

    it could be improved, but it's not wrong
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    any ideas why it does not import the data cause i am at a total loss with this now.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    perhaps the split rows might have something to do with it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers, now to google it!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by txt3rob View Post
    Cheers, now to google it!
    why?

    i posted the example from your file earlier on

    you just need to fix your csv file
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot txt3rob's Avatar
    Join Date
    Jul 2013
    Location
    Liverpool UK
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by txt3rob View Post
    if i import the file using phpmyadmin then it works fine...
    no, not with the split rows, no it doesn't
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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\n' but only line feed '\n' as line separator):

    Code:
    mysql> LOAD DATA INFILE '/tmp/trains.csv' INTO TABLE Trains FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY  "\\" LINES TERMINATED BY "\n" 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)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •