or just a couple of rows as appeared in the previous posting:
For two of the main open source databases I use, some graphical tools are listed here:
- mysql-admin for mysql with source available direct from mysql.com
- pgadmin3 for postgres with source availability mentioned in news.
If you like a screenshotted introduction to the gui tools then here is a quick idea of the look and feel:
The version 6 release of mysql (maybe 2010) will include
load xml infile
Commercial releases of mysql might show slightly different versions but in keeping with the title of this blog I tend to focus on gpl or free and open source flavours of software.
If you have the data in a variety of formats (usually the case if it is your data) then csv format and the formats mysqlimport has supported for the last 10 years should do fine to get the job done.
All major databases will likely soon offer xml import (if not already) which should help data interchange between retailers and suppliers providing xml standards are strictly adhered to. Newer xml proposed standards have public pages for further research at w3.org
If you are itching for bringing in xml data directly to mysql today then you might want to browse the following:
- xml inside a mysql column and later retrieval
- xml importing using python (2003ish)
- xml importing using perl libraries (2003ish)
- xml importing using a stored procedure xmldump_load
And now back to the data which I give in mysql own .sql format to aid anyone wanting to just load it directly rather than getting hung up on xml.
what comes next which is all Postgres.
Before leaving mysql entirely we should export the data using the --compatible flag, so as the sample data and postgres postings can discuss what (if anything) must be done to the sql output from --compatible to make it totally postgres.
Here is what mysql produces from --compatible=postgresql:
- [ data only (no create statement) ]
- [ data+create (less compact form) ]
- [ data+create (compact form) ]
Despite how it sounds, this won't generate PostgreSQL compatible code, but it will get you halfway there.Halfway sounds pretty good if you do not want to go down the 'csv out csv in' route. I explore both ways in the postgres postings.