Importing Table Data via phpMyAdmin and Excel CSV

[webomat]

Howdy,

Hoping someone here has an answer to solve a problem I'm having:

Using phpMyAdmin to insert data into a table in Zen, everything works EXCEPT it adds an empty record at the end of the table.

Testing this on a local Apache Install, Win XP Pro, PHP 4.3.10, phpMyAdmin 2.6.0. Using Excel 2003.

Initially this showed up when using the Excel CSV file "as is", and using the Sql Insert \r\n flag for windows-based files.

Converted the file in EditPadPro to Unix-based \n only, thinking that might help -- and then adjusted the info in phpMyAdmin Sql Insert accordingly, but nope. In fact, that added 2 empty records!

I know I can simply go in and delete the empty record, just wondering if anyone knows what I'm doing wrong? (I also know I can script it, but before I do that I like to do things manually a time or two so I am clear on what I want to accomplish.)

Thanks in advance for any help!

Becky "-)

[birdoasis]

What was the source of the data? I'm assuming that it wasn't a mysql database?

Knowing the source may help.

[webomat]

What was the source of the data? I'm assuming that it wasn't a mysql database?

Knowing the source may help.

Good question!

What I did was dump the table to CSV to verify field format, then created my own record as a test. New record was actually created in EditPadPro and saved as *.CSV, opened in Excel to format date field properly and then resaved.

Thanks!

Becky

[schoolboy]

Even though PHPMYADMIN offers it, exporting to CSV should be done when you don't intend to use the csv dump later to re-populate the dbase. The main reason why CSV is offered is to allow a quick formatting for other uses of the data - such as putting it into a spreadsheet.

I favour export as SQL if I intend to re-populate with that dump later.

It's more reliable without any possible formatting contamination... even if it is a little more cumbersome to edit !

[birdoasis]

Schoolboy I want to steal your signature.

Webomat, It would be better to export as mysql. with the following checked.

Drop table if exist
Insert
Save as file

Then to import it, you go simply paste it into the SQL query box in phpmyadmin It's one of the tabs at the top of the screen.

That should take care of it.

Can you export as mysql?

[schoolboy]

Sorry John...


I never let my forum buddies interfere with my signature

[webomat]

Hi Gents!

Thanks for the replies, and it's good to know.

I may not have been as clear as I thought I was: I wasn't dumping the records in the DB as CSV to reupload them, I was using that as a lazy person's method of grabbing the field names and conducting a visual look at what it would want a new CSV file being uploaded to look like. With over 100 records to upload, I wasn't in the mood to do the SQL thing, even using REGEX it was going to be a pain to change the source data (an Excel file from my client) into the SQL format for insert.

Anyway, interestingly enough the version of phpMyAdmin on the server (V 2.9.x) has a different interface for import/insert and worked exactly as expected with this file! No extra row added at the end.

Go figure! But I appreciate your comments and have learned from them. :-) Thanks again for your help!

Cheers,

Becky