Jump to content

[SOLVED] phpmyadmin table import only importing first line


kristinac

Recommended Posts

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

 

Link to comment
Share on other sites

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,

Link to comment
Share on other sites

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.

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.