troubleshooting Question

Need help getting LOAD Inflie to work for a table with the PK auto_incremented

Avatar of chonabraham
chonabraham asked on
MySQL Server
1 Comment1 Solution104 ViewsLast Modified:
I can not get the LOAD infile statement to work to populate track which has the first column as the PK auto_increment.  The file has only 3 columns and I want to allow MySQL to create the trkid then have the LOAD infile populate columns 2,3, and 4 columns. When I run this the LOAD infile is populating columns 1,2, and 3.   How do I alter the LOAD infile to make it populate properly.

CREATE TABLE track (    
    trkid   INTEGER auto_increment,  
      trknum     INTEGER,    
      trktitle    VARCHAR(50),      
      trklength   DECIMAL(4,2),        
         PRIMARY KEY (trkid));
                     
LOAD DATA LOCAL INFILE       /*this file does not include trkid and starts with trknum bc trkid is auto_incremented*/
'/Users/chon.abraham/CD_data.txt'  
INTO TABLE track
FIELDS TERMINATED BY ','
ENCLOSED BY ''''
LINES TERMINATED BY '\r\n' ;

The file looks like

(1, 'Giant Steps', 4.72),
(2, 'Cousin Mary', 5.75),
(3, 'Countdown', 2.35),
(4, 'Spiral', 5.93),
(5, 'Syeeda''s song flute', 7),
(6, 'Naima', 4.35),(7, 'Mr. P.C.', 6.95),
(8, 'Giant Steps', 3.67),(9, 'Naima', 4.45),
(10, 'Cousin Mary', 5.9),
(11, 'Countdown', 4.55),
(12, 'Syeeda''s song flute', 7.03),
(1, 'Stomp of King Porter', 3.2),
(2, 'Sing a Study in Brown', 2.85),
(3, 'Sing Moten''s Swing', 3.6),
(4, 'A-tisket, A-tasket', 2.95),
(5, 'I Know Why', 3.57),
(6, 'Sing You Sinners', 2.75),
(7, 'Java Jive', 2.85),
(8, 'Down South Camp Meetin''', 3.25),
(9, 'Topsy', 3.23),
(10, 'Clouds', 7.2),
(11, 'Skyliner', 3.18),
(12, 'It''s Good Enough to Keep', 3.18),
(13, 'Choo Choo Ch'' Boogie', 3)
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros