[SOLVED] phpmyadmin table import only importing first line

[kristinac]

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

 

[MadTechie]

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)

[kristinac]

no dice.

 

yes, i am OS X

[WolfRage]

What is your operating system?

[MadTechie]

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

[kristinac]

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?

[WolfRage]

But is the error the same?

[kristinac]

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.

[WolfRage]

Can you post the CSV file?

[kristinac]

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,

[kristinac]

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.