Jump to content

Trouble with LOAD DATA INFILE


Muddy_Funster
Go to solution Solved by Barand,

Recommended Posts

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.