Import excel to db tables

[Gap30]

Can't get excell files to import to db tables in phpmyadmin!!!! Exported maufatures table and tried to import file making no changes keep getting (error invalid parmeter for csv import: lines terminated by " : tried storing file as excell2000, ms for excell, & as a cvs file still get same error message. changed semicolon to coma. My wife has about 1000 objects to import to products don't want to type every one in and her program list info as cvs file this is why i would like to use excell. If tab delinated what character would you put in to get. Please make answer simple Thanks

 

Greg

[jdvb]

you could enter a <tab> in notepad and then copy paste that.

but judging your error you need to terminate lines differently.

never used phpmyadmin for this though.

 

I have written my own php script for that, it runs on a local server,

generating payment batches for my automatic debeting system.

not sure how phpmyadmin would want that.

in php you generate an eol character with \r\n not sure how to tell phpmyadmin to expect that as end of line.

my phpmyadmin installs don't offer csv imports anyway.

 

if it realy is to much trouble, and you have fixed fields i.e. the same number of fields on all the same places...

you could try and turn your file into an sql file instead...

see an exported sql file example for that

 

example

INSERT INTO `YOUR_TABLE_NAME` (`0COLUMN_ONE`, `COLUMN_TWO`, `COLUMN_THREE`) VALUES ('VALUE_COLUMN_ONE', 'VALUE_COLUMN_TWO', 'VALUE_COLUMN_THREE');

in order to automaticaly generate a file like that you should put the same at the front of each line:

INSERT INTO `YOUR_TABLE_NAME` (`0COLUMN_ONE`, `COLUMN_TWO`, `COLUMN_THREE`) VALUES ('

then between each collumn of your values (that should be escaped properly)

', '

and end your lines with:

');

note: the line should end then, i.e. a valid end of line character should be returned as in the <return> key

 

you can escape all characters in excel using the replace function, search for ' and replace that with \'

 

how to easily add the first and last parst as well as what is in between:

add collums for all seperators, and save the file useing a sepperator you do not use, then open your file in notepad or something alike, search for your not to be used seperator and remove it. (search and replace)

[dunkuk101]

Can't get excell files to import to db tables in phpmyadmin!!!! Exported maufatures table and tried to import file making no changes keep getting (error invalid parmeter for csv import: lines terminated by " : tried storing file as excell2000, ms for excell, & as a cvs file still get same error message. changed semicolon to coma. My wife has about 1000 objects to import to products don't want to type every one in and her program list info as cvs file this is why i would like to use excell. If tab delinated what character would you put in to get. Please make answer simple Thanks

 

Greg

 

Hi Greg,

 

I experienced this same problem myself. I managed to work around it by setting it up the following way which works for me:

It should work if you select CSV mode, and change "Fields terminated by" to a comma and "Lines terminated by" to the following: \n

 

Hope that helps... :)