kristinac Posted March 18, 2009 Share Posted March 18, 2009 i have a csv file i am trying to import into my 'item' table, which works as long as i import one line at a time. at 250+ lines, this will be a real hassle. here's what i'm doing: 1. save excel doc as csv 2. in phpmyadmin, click the appropriate table 3. select "import" 4. browse for my file "item.csv" 5. click radio button "CSV using LOAD DATA" 6. click "Replace table data with file" 7. change feilds to read as follows: Fields terminated by , Fields enclosed by [blank] Fields escaped by [blank] Lines terminated by auto Column names [blank] 8. leave "us local keyword" checked 9. click "go" then it imports, but it only does the first line of my spreadsheet. it outputs this confirmation message: Import has been successfully finished, 1 queries executed. LOAD DATA LOCAL INFILE '/private/var/tmp/phpU3elSj' REPLACE INTO TABLE `item` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'# 1 row(s) affected. i have opened my csv file in TextEdit to make sure the deliminators are correct. here is a sample (first two lines) ItemID,Name,Replaced_by,Replacement_for,Intro,Thumbnail,Photo,Active,style,top,back,sides,tuner,finish,electronics,series,s_hole,binding,f_board,f_inlay,bridge,bracing,shell,resonator,tone_ring,hardware,neck,Headbinding,HeadOverlay,Pickguard,Width,Scale,XLPhoto,StyleID,SeriesID,pegs,Type,leftAvail,binID,revID,p_fullname 115,4280 Minstrel Banjo,,,NULL,4280_small.gif,4280.gif,0,Banjo,NULL,NULL,,Planetary,NULL,None,Alvarez Traditional Series,NULL,NULL,Rosewood,NULL,NULL,NULL,Multi-ply Maple,Mahogany,Plated Brass,Chrome,NULL,NULL,NULL,NULL,NULL,NULL,NULL,5,,,,,901,0, these are the things i have tried: 1. removing the header line in my csv file 2. auto_inc the ItemID field (with nothing entered in the csv's ItemID column) 3. disabling auto_inc and manually entering the csv's ItemID field 4. using CSV import instead of CSV LOAD DATA 5. manually entering a glyph as the line break character and specifying it in the "Lines terminated by" field 6. unchecking "local keyword" 7. removing all "NULL" instances from the exported "item" table i don't know what else to do. i'm beating my head against the wall and google hasn't been much help. thanks in advance, k Quote Link to comment Share on other sites More sharing options...
MadTechie Posted March 18, 2009 Share Posted March 18, 2009 You say you open them in "TextEdit" is that the Mac OS X editor ? if so, your line delimitor would be CR (/r) Mac = CR (/r) *nix = LF (/n) Win = CRLF (/r/n) Quote Link to comment Share on other sites More sharing options...
kristinac Posted March 18, 2009 Author Share Posted March 18, 2009 no dice. yes, i am OS X Quote Link to comment Share on other sites More sharing options...
WolfRage Posted March 18, 2009 Share Posted March 18, 2009 What is your operating system? Quote Link to comment Share on other sites More sharing options...
MadTechie Posted March 18, 2009 Share Posted March 18, 2009 So are you on a Mac ? if so i think theirs an encoding option (sorry my home Mac Keyboard got coffee soaked) try "Windows Comma Separated & MS-DOS Comma" or something like that! EDIT: also try the 3 types of delimtors \r \n \r\n Quote Link to comment Share on other sites More sharing options...
kristinac Posted March 18, 2009 Author Share Posted March 18, 2009 i tried exporting from excel in CSV Windows, CSV Mac & CSV, each time trying all three of the line deliminators. nothing works. what the hell? Quote Link to comment Share on other sites More sharing options...
WolfRage Posted March 18, 2009 Share Posted March 18, 2009 But is the error the same? Quote Link to comment Share on other sites More sharing options...
kristinac Posted March 18, 2009 Author Share Posted March 18, 2009 the system response is the same, yes, but it's not an error. it gives a little green check icon saying the import is complete, but it's only imported one line out of 254. Quote Link to comment Share on other sites More sharing options...
WolfRage Posted March 18, 2009 Share Posted March 18, 2009 Can you post the CSV file? Quote Link to comment Share on other sites More sharing options...
kristinac Posted March 18, 2009 Author Share Posted March 18, 2009 i already posted two sample lines in the original post. i'll add a carrige return here on the same two lines and add a third for good measure since it's a little long and convoluted: ItemID,Name,Replaced_by,Replacement_for,Intro,Thumbnail,Photo,Active,style,top,back,sides,tuner,finish,electronics,series,s_hole,binding,f_board,f_inlay,bridge,bracing,shell,resonator,tone_ring,hardware,neck,Headbinding,HeadOverlay,Pickguard,Width,Scale,XLPhoto,StyleID,SeriesID,pegs,Type,leftAvail,binID,revID,p_fullname 115,4280 Minstrel Banjo,,,NULL,4280_small.gif,4280.gif,0,Banjo,NULL,NULL,,Planetary,NULL,None,Alvarez Traditional Series,NULL,NULL,Rosewood,NULL,NULL,NULL,Multi-ply Maple,Mahogany,Plated Brass,Chrome,NULL,NULL,NULL,NULL,NULL,NULL,NULL,5,,,,,901,0, 114,4294 Silver Belle Banjo,,,NULL,4294_small.gif,4294.gif,0,Banjo,NULL,NULL,,Planetary,NULL,None,Alvarez Traditional Series,NULL,NULL,Rosewood,NULL,NULL,NULL,Multi-ply Maple,Mahogany,Plated Brass,Chrome,NULL,NULL,NULL,NULL,NULL,NULL,,5,,,,,901,0, Quote Link to comment Share on other sites More sharing options...
kristinac Posted March 18, 2009 Author Share Posted March 18, 2009 i seem to have gotten it to work by exporting as CSV Windows and setting the import fields like this: Fields terminated by , Fields enclosed by " Fields escaped by \ Lines terminated by auto Column names [blank] i think the mac CSV export didn't match up. even though when i open the CSV file in TextEdit it doesn't show the " enclosing cell data. they must be hidden characters that TextEdit won't display. wow, that was frustrating and a colossal waste of time! i hope this helps someone else out there. Quote Link to comment 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.