Export / Import DATETIME in MySQL

[cmcp]

Hey

I'm in phpmyadmin trying to move some data from one database to the next.

After a good bit of troubleshooting I've managed to move almost all of the data I require across.

I exported the first as CSV for excel and matched up my data. It's all gone in fine except two DATETIME columns. This is causing me problems as it shows no orders (as they've no date attached to them).

When I import the new sheet the DATE columns all show 00/00/00 00:00:00.

I've also tried changing the field to VARCHAR when exporting / importing although that was a wild guess.

Please help UKBF - is this something obvious? How can I get around such a hurdle?

Many thanks.

 

[ozbon]

I think you'll need to use a custom format in Excel for it to show the date -

Basically MySQL uses a different date format to Microsoft/ Excel.
MySQL uses yyyy-mm-dd hh:mm:ss , Excel uses (I think) dd/mm/yyyy

If you're just using phpMyAdmin's export thing, you might have to do some alterations to the CSV file.

However, if you're doing the export using a query, you can use MySQL's DATE_FORMAT to churn out the date in the correct format for Excel.

 

[edmondscommerce]

I would avoid using Excel altogether.

Open office is much better for editing csv files where you need to preserve formatting.

However if you are trying to move data from one DB to another you shoudl be doing SQL dumps, not exporting to CSV.

Just go to the export tab in phpmyadmin and you should figure it out from there.

To import it you would then use the import tab

 

[cmcp]

Thanks for replying guys.

The new database has some columns named differently, so I wanted to keep it all in the one export to save any f-ups. I'm not hugely experienced with sql so didn't want to get into any renaming queries...

I didn't even consider a dump, just because of the renamed columns. Perhaps I could import the simple data, then maybe execute the date import with the customer ID's with a "WHERE cID =" statement?

I'll have a look at open office too. Not used that since I wiped Mandrake off my laptop!

 

[ozbon]

Well your other option would be to run the entire thing using a PHP (or ASP, or whatever your host allows) script.

Set up the new tables on your existing site, then simply do a script that takes each line from [table A] and then inserts that record into [table B], taking into account the renamed columns etc.

Once that's done, all you have to do is export the new tables, and put them wherever you need 'em.

Or have I missed something relevant?

 

[ozbon]

Just as an additional, you'd need to set up two db connections, so you don't overwrite one record set with the next query. I always use the DB class, but it would be somethin like this...

$a = new DB_Sql;
$b = new DB_Sql;

$get_records = "SELECT * from table_a order by row_id ASC";
$a->query($get_records);
while ($a->next_record()) {
$f1 = $a->f("field1");
$f2 = $a->f("field2");
$update_tableb = "INSERT INTO table_b SET row_id=NULL, f1='$f1', f2='$f2' [etc etc.]";
$b->query($update_tableb);
set_time_limit(10); // Stops script from timing out halfway through
}

Job done

 

[cmcp]

I'm going to try the openoffice route first to try and preserve the DATE format.

Next up I'll look at some scripting - I think it'll need a clear head and bit of patience!

Again, thanks for the advice. I'll let you know how I get on.

 

[CrispyUK]

If you're moving data between two databases and the databases are identical aside from some of the column names then you could try exporting the original database as an SQL dump - then open this up in a text editor to adjust the column names to suit the new database, save it then import it.

 

[ozbon]

Drop me a PM if you want a hand with the scripting thing - it's probably the easiest way to go, and definitely wouldn't take me long to do.

 

[cmcp]

open office is rocking my world - thanks!

 

[edmondscommerce]

open office is rocking my world - thanks!

+1 for open source software

 

[cmcp]

Got almost all data moved over whilst maintaining correct date formats. Will have to watch that in the future. Cheers!

 

[edmondscommerce]

no worries