Muddy_Funster Posted December 11, 2015 Share Posted December 11, 2015 So I'm trying to import 2104320 lines from a CSV into a table using the following: (some names changed to protect the guilty) LOAD DATA INFILE '/var/.../MYDATA.CSV' REPLACE INTO TABLE mydata FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (@c1,...@c107) SET stcode = @c1, ... tvalue = @c105 Problem is, it's only importing 1052160 lines. When I looked through the table I seen that relative ID fields have only got their odd number values (1,3,5,7...) so as the CSV has a row of headings, it's only loading in every second line. I have run it without the IGNORE 1 LINES and this hasn't made a difference. I have tried changing the escape character in case that was a problem - didn't help. I have also increased buffers and timeouts on the MYSQL server, that didn't help either, I have no control over the CSV as it's auto generated by third party software. As the file is created on a windows platform i'm re-running just now using line termination of '\r\n' but am not confident as I would have thought that if the line terminator was wrong I wouldn't have any lines. Anyone have any other suggestions while I wait (it takes a while as it's actually running over the network using a hardlink) - I will update once it's complete. Quote Link to comment https://forums.phpfreaks.com/topic/299702-trouble-with-load-data-infile/ Share on other sites More sharing options...
Solution Barand Posted December 11, 2015 Solution Share Posted December 11, 2015 I would think the "\r\n" is the cure. Let us know. Quote Link to comment https://forums.phpfreaks.com/topic/299702-trouble-with-load-data-infile/#findComment-1527814 Share on other sites More sharing options...
Muddy_Funster Posted December 11, 2015 Author Share Posted December 11, 2015 Cheers Barand - congrats on Mod by the way, I only just realised. I kinda broke the table :/ (some perpetual locking issue caused the query to hang and then blocked further attempts) so have had to rebuild from a backup template and am re-running now. sadly I have a family matter to attend to this after-noon and this is likely going to have to hang in the air over the weekend. I will get back to you with the results (fingers crossed this will do it). Quote Link to comment https://forums.phpfreaks.com/topic/299702-trouble-with-load-data-infile/#findComment-1527815 Share on other sites More sharing options...
Muddy_Funster Posted December 14, 2015 Author Share Posted December 14, 2015 Yeah, turns out it was the "\r\n" that fixed it. I was a bit sceptical as I would have thought that it wouldn't have returned anything (or at best a single row) with the wrong line termination in there. Just goes to show. Quote Link to comment https://forums.phpfreaks.com/topic/299702-trouble-with-load-data-infile/#findComment-1527899 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.