Khwooo’s Weblog

April 11, 2005

Excel to Mysql

Filed under: freebsd — khwooo @ 11:33 pm

有很多問題真的是遇到了才會想辦法解決(廢言)
這兩天做作業,要將資料內容繁雜的EXCEL轉入MYSQL裡,總不能一筆筆用SQL輸進去吧,在完全沒有什麼IDEA之下,於是開始爬文:

from 酷!學園討論區

下載MySQL Connector/ODBC 3.51
http://dev.mysql.com/downloads/connector/odbc/3.51.html
看你是要用windows或是linux來轉

不過上面那個connector應該是給本機轉換使用的,在WIN上用ODBC連結MYSQL,不過我目標不在本機上。

from mysql forums

Simply export data from excel as TXT or CSV. Than use MySQL command
LOAD DATA INFILE ‘path/file.txt’ INTO TABLE your_table;

上面是說直接用EXCEL輸出純文字或CSV(Comma Separated Values )檔案,接著直接用MYSQL指令load data infile直接匯入即可,此動作可以在phpMyAdmin裡面完成。

不過我千年未更新的phpMyAdmin似乎不支援此語法,只剩最後一招,使用網路上能抓到的Excel-to-Mysql來轉,需付費,試用版只能轉五筆,破解也是請教孤狗大神,它可以將TXT或CSV檔案轉成Mysql dump file,接著再丟給Mysql就行了。

終於成功了,淚。

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: