MYSQL loading

[JohnC]

Tweet

i have mysql loading problem hope someone here can help as its being a pain.

Basically the sql query is as follows.

mysql> LOAD DATA LOCAL INFILE '/home/public_html/update/test' INTO TABLE `products` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (`advertiser` , `language` , `masterCategoryID` , `masterCategory` , `productID` , `refNumber` , `name` , `brand` , `promotionalText` , `description` , `deepLink` , `imageURL` , `delivery` , `validFrom` , `validTo` , `currency` , `displayPrice` , `searchPrice`)
    -> \g
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql>

However it onyl seams to insert one line of my test file.

However there are around 2000.

The format of the file is

"e2save","",265000,"Mobile Phones","0732820001107644468422","","Sony Eric. T630","Sony Eric.","","The T630 is a cute, sophisticated little phone with a built-in digital camera, multimedia messaging abilities and polyphonic ringtones.You can create your own ringtone with the built in music DJ or play any of the interesting embedded games on the high quality screen plus you can download from the web making it a great gaming phone!Bluetooth™ technology means it can interact with other Bluetooth™ devices. Treat yourself or someone special with this compact phone.","http://www.awin1.com/awclick.php?gid=5679&mid=422&awinaffid=46759&p=http://www.e2save.com/?aw_vodafone_T630","http://www.e2save.com/images/smallpic/VODT630.gif","FREE next day d","0000-00-00","0000-00-00","GBP","GBP0.00",0.00
"e2save","",265000,"Mobile Phones","0733327001107644468422","","Nokia 6820","Nokia","","Small, Sleek and Desirable. The Nokia 6820 features a full QWERTY keyboard for smarter messaging right at your fingertips. The phone also features a digital camera with video recording and playback that allows you to take a picture, type a message, add a sound file and even a voice recording. With it's unique flip keyboard action, this phone has all of the latest features to make calling, messaging and gaming even more enjoyable.","http://www.awin1.com/awclick.php?gid=5679&mid=422&awinaffid=46759&p=http://www.e2save.com/?aw_vodafone_6820","http://www.e2save.com/images/smallpic/VOD6820.gif","FREE next day d","0000-00-00","0000-00-00","GBP","GBP0.00",0.00
"e2save","",265000,"Mobile Phones","0733585001107644468422","","Nokia 6230","Nokia","","This Bluetooth™ phone is for more than making calls and is equipped with the latest technology to keep you entertained.It has a VGA (Video Graphics Adaptor), digital camera, video recorder and player so you can re-live the moment again and again. You are able to share special moments by sending (and receiving) messages containing text, a sound clip, and an image or a video clip to other compatible devices.Watch and listen to pre-recorded clips with video streaming. Plus the music player for MP3 files means you can download your favourite tunes or just listen to FM radio. Please Note: Colour may vary depending on stock availability.","http://www.awin1.com/awclick.php?gid=5679&mid=422&awinaffid=46759&p=http://www.e2save.com/?aw_vodafone_6230N","http://www.e2save.com/images/smallpic/VOD6230N.gif","FREE next day d","0000-00-00","0000-00-00","GBP","GBP0.00",0.00
"e2save","",263500,"Communication & Mobile Phones","0734018001107644468422","","Nokia 1100 (Virgin)","Nokia","","The Nokia 1100 enables you to communicate with friends and colleagues in confidence and style, whether at home or on the move.  With the enhanced long-life battery and durable design this is a handset that will help you talk even smarter.Please Note: Colour may vary depending on availability.","http://www.awin1.com/awclick.php?gid=5679&mid=422&awinaffid=46759&p=http://www.e2save.com/?aw_payg_1100VI","http://www.e2save.com/images/smallpic/PAY1100VI.gif","FREE next day d","0000-00-00","0000-00-00","GBP","GBP59.99",59.99
"e2save","",263500,"Communication & Mobile Phones","0734222001107644468422","","LG C1200 (T-Mobile)","LG","","The LG C1200 mobile phone is a compact, great value handset that is stylish and elegant. Equipped with a 65K colour screen, GPRS data and a WAP browser. In addition the C1200 also supports Java™ technology for games and polyphonic ringtones.  Equipped with GPRS giving you fast and easy access to the latest news or sports results in t-zones. The compact clamshell design features an external display to allow you see who is calling before you answer the phone.","http://www.awin1.com/awclick.php?gid=5679&mid=422&awinaffid=46759&p=http://www.e2save.com/?aw_payg_C1200T","http://www.e2save.com/images/smallpic/PAYC1200T.gif","FREE next day d","0000-00-00","0000-00-00","GBP","GBP99.99",99.99

now i can't see why it only inserts one record, not sure if i am missing an argument or something.

any help would be great

[Rich]

Try changing
LINES TERMINATED BY '\r\n'
to
LINES TERMINATED BY '\n'
and it should work.