Reading file into mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Reading file into mysql

Post by Skittlewidth »

I've just been handed a Word file which contains a two column table that is 9 pages long which I now need to put into an equivalent mysql table.

No way do I want to retype it all to get it into the table! I've heard a bit about CSV files but don't know much about them. I'm just looking into mysqlimport but I don't know how to format the text file to get it to read the information correctly!

Can anyone give me a nudge in the right direction?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

In csv fields are separated by commas, records are separated by newlines.

Select entire table in Word, then choose 'Table'->'Convert'->'Table to text'. In 'Separate text with' radio-groop choose 'Other' and enter comma into the editbox. Click Ok. Voila, you have your table in csv format. Just save it as Plain text and feed it to mysqlimport utility.

(it's for MSOffice 2003, actual menu and option names may differ in other versions).
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post by Skittlewidth »

Cheers Weirdan, just one more question, will mysqlimport take the first row to be the column headings? I know the file name will determine the table name.

Thanks
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

No, the first row will be taken as data, not as columns names. If the order of data in file mismatch the order of columns in table you can use the option --columns=<column_list>. Please refer to the manual for more info and example: http://dev.mysql.com/doc/mysql/en/mysqlimport.html

Regards,
Scorphus.
Post Reply