/ 中存储网

Excel数据如何导入MySQL数据库

2014-07-13 15:55:55 来源:中存储网

windows方法有2种。
1: MYSQL 语句
  load   data   local   infile   'd:\data.txt'   into   table   test.poi_query  fields   terminated   by   ','   enclosed   by   '"' LINES TERMINATED BY 'n';
遵照一定的格式,这样就把文件加载入了数据库中,txt可换为xls文件,对应的txt文件为:      

"1","a string","100.20"

"2","a string containing a , comma","102.20"

注意:(1) test数据库中的poi_query 表
          (2) 如果指定了 LOCAL 关键字,它将对连接的客户端做出解释:

            如果指定了 LOCAL,则是MySQL服务器从远程客户机上读取文件。

            如果没有指定 LOCAL,文件是位于服务器的主机上,并且直接被服务器读取。

          (3) 如果用户没有指定一个 FIELDS 子句,缺省时如同使用下列语句(字段tab分割,每个字段没有包裹):

FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '\'

如果用户没有指定一个 LINES 子句,缺省时如同使用下列语句:

LINES TERMINATED BY 'n' STARTING BY ''


2:ODBC驱动xls,在写入MySQL
      这种方法,速度比较慢点,但是更灵活。
      步骤:
(1) 检查获取xls驱动 
       // 获取已安装驱动的名称(涵数在odbcinst.h里)
    if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
        return "";
    // 检索已安装的驱动是否有Excel...
    do
    {
        if (strstr(pszBuf, "Excel") != 0)
        {
//发现 !
sDriver = CString(pszBuf);  // sDriver  =" Microsoft Excel Driver (*.xls) "
break;
        }
        pszBuf = strchr(pszBuf, '') + 1;
    }
(2) 获取xls文件中表的列表保存于CStringArray
SQLAllocStmt(database.m_hdbc,&hStmt);  
 ret   =   SQLTables(hStmt, NULL,SQL_NTS, NULL,SQL_NTS,   NULL,SQL_NTS, (unsigned   char   *)type.GetBuffer(0),SQL_NTS);  
    //type=" 'table' , 'view' "  ;不知道为什么   HSTMT hStmt;
 while(1){   
  ret   =   SQLFetch(hStmt);   
  ret   =   SQLGetData(hStmt,   3,   SQL_C_CHAR,   szName,   256,   &cbName);   
  names.Format("%s",szName);   //交通设施服务 住宿服务 体育休闲服务 公共设施 公司企业 医疗 商务住宅 摩托车服务 政府机构
  //汽车服务 汽车维修 汽车销售 生活服务 科教文化服务 购物服务 道路设施服务 金融保险服务 风景名胜  餐饮服务   //地名地址信息
  m_cmbTables.Add(names);   //保存xls中表名字的集合
 }   
 SQLFreeStmt(hStmt,SQL_CLOSE);  
(3) 初始化excel
// 打开数据库(既Excel文件)
  CDatabase database.Open(NULL, false, false, "ODBC;DRIVER={Microsoft Excel Driver (*.xls)};DSN='';DBQ=“d:\poi.xls”"); 
(4) 初始化MySQL 利用MySQL自带的函数
  MySQL myData = mysql_init((MYSQL*) 0)) && mysql_real_connect(myData,“127.0.0.1”,"root","123", "test", 3360, NULL, 0 ) ; //test为数据库名
(5)读取xls数据并写入MySQL, 只操作xls中一个表为例。
CRecordset recset(&database);
sSql = "SELECT *   FROM  " +dlg->m_cmbTables[0] ;  /* +" order by NAME";     */           
   // 执行查询语句
   recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);
while (!recset.IsEOF()) 
  {
//注意:上面设置了CRecordset::forwardOnly,所以recset只能MoveNext,不能向前移动。并且
//excel中字段的顺序也只能向前移动不能向后移动。例如这里excel里字段从左到右必须为name,type
    recset.GetFieldValue("NAME", sItem1); //读取Excel内部数值
    recset.GetFieldValue("TYPE", sItem3);
    szSqlText = "insert into poi_query set name='"+sItem1+"',type='"+sItem3+"' ";
    mysql_query(dlg->myData,szSqlText.GetBuffer(600)); //notice
     recset.MoveNext();
}
(6) 关闭
 recset.Close(); //都是操作xls的!!
   database.Close(); // 关闭excel数据库
   mysql_close( myData ) ;   //最后关闭mysql数据库


注意:如果ID设置为127.0.0.1 则程序中可以正常访问MySQL,但是如果设置为本机ip,例如192.168.1.100则不能访问,或者访问外网服务器IP也不能访问。
这是权限问题,必须设置管理员用户su的权限为外网用户可以访问,那么我们才能通过Ip来控制MySQL,在MySQL中有一个数据库叫做mysql,它里面包含一个user表,里面存有所有帐号及它们的权限及特征。对应我的user表中找到User项为root的,发现有两项,其Host项分别为localhost和localhost.localdomain。我们只需要把'localhost'改为'%'就可以让所有远程机器以root帐号登陆mysql服务器。也可以将'localhost'改为某一个ip地址,这样就可以在该地址的机器上登陆mysql服务器。这些都没有问题,只是网上被广为传颂的一个更改Host项的方法是错误的。他的方法是用update语句将指定User项的Host项改过来,如果想添加一个新的用于远程访问的帐号,就用insert语句添加。事实是这根本不行,我按照作了,表是变了,但还是连不上。后来经过询问才知道,要想改,结合我的情况,必须用下面的这条语句: grant all privileges on mydns.* to 'root'@'%'; 来改变root帐号的权限。如果添加新帐号也是如此。想必这个表比较特殊,用普通的SQL语句不能操作。如果帐号有密码,后面再加上identified by '***'(*为你帐号的密码)就可以了。