(1)編譯
treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ gcc t19.c `mysql_config --cflags --libs` -o t19
(2)goods.csv資料檔
treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ cat goods.csv gdsno,gdsname,cost,unit,stock_qty,fac_no t101,砂糖桔,140,斤,500,099 t102,香桔士,50,斤,400,077
(3)執行
treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ ./t19 goods.csv new_goods貨品基本資料表轉入程式 0[t001] 1[砂糖桔] 2[140] 3[斤] 4[500] 5[099] Duplicate entry 't001' for key 'PRIMARY' treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ vi goods.csv treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ ./t19 goods.csvnew_goods貨品基本資料表轉入程式 0[t101] 1[砂糖桔] 2[140] 3[斤] 4[500] 5[099] SQL指令: [insert into new_goods (gdsno,gdsname,cost,unit,stock_qty,fac_no) values ('t101','砂糖桔',140.00,'斤', 500, '099')] 執行成功!
0[t102] 1[香桔士] 2[50] 3[斤] 4[400] 5[077] SQL指令: [insert into new_goods (gdsno,gdsname,cost,unit,stock_qty,fac_no) values ('t102','香桔士',50.00,'斤', 400, '077')] 執行成功!
(4)t19.c程式碼 treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ cat t19.c #include <my_global.h> #include <mysql.h> #define HOST "localhost" #define USER "treehrt" #define PWD "abc123abc123" #define DB "ilvssell" typedef struct GOODSREC { char gdsno[20]; char gdsname[30]; // char spec[10]; double cost; // double price_in; // double price_special; // double price_sell; char unit[4]; int stock_qty; char fac_no[20]; // char mem[80]; } goodsRec ;
void finish_with_error(MYSQL *con) { fprintf(stderr, "%s\n", mysql_error(con)); mysql_close(con); exit(1); } char tokens[100][60]; //最多100個存放token int tokensc; //tokens計數 void insert_new_goods_rec(goodsRec gR){ // printf("待寫入的資料:gR.gdsno=%s,gR.gdsname=%s gR.unit=%s gR.cost=%.2f gR.stcok_qty=%d gR.fac_no=%s\n",gR.gdsno,gR.gdsname,gR.unit,gR.cost,gR.stock_qty,gR.fac_no); char st[500]; sprintf(st,"insert into new_goods (gdsno,gdsname,cost,unit,stock_qty,fac_no) values ('%s','%s',%.2f,'%s', %d, '%s')",gR.gdsno,gR.gdsname,gR.cost,gR.unit,gR.stock_qty,gR.fac_no) ; MYSQL *con = mysql_init(NULL); if (con == NULL) finish_with_error(con); if (mysql_real_connect(con, HOST , USER, PWD, DB, 0, NULL, 0) == NULL) finish_with_error(con); mysql_query(con, "set names utf8"); if (mysql_query(con, st)) finish_with_error(con); printf("SQL指令: [%s] 執行成功! \n\n", st); }
void parsecsvline(char *st ,char deli){ char tk[80]; tokensc=0; strcpy(tk,""); for (int i=0;i<strlen(st);i++) { if((st[i]==deli) || (st[i]==0x0a)) { strcpy(tokens[tokensc],tk); tokensc++; strcpy(tk,""); } else sprintf(tk,"%s%c",tk,st[i]); } for (int i=0;i<tokensc;i++) { printf("%d[%s] ",i,tokens[i]); } printf("\n"); }
int main(int argc, char **argv) { char st[100]; FILE *fp; goodsRec gR; printf("new_goods貨品基本資料表轉入程式\n"); fp = fopen(argv[1] , "r"); //argv[1] use for csv filename ,ex cust.csv. if(fp == NULL) { perror("Error opening file"); return(-1); } fgets(st,100,fp); while (1){ if( fgets(st, 100, fp)!=NULL ) { parsecsvline(st,','); strcpy(gR.gdsno,tokens[0]); strcpy(gR.gdsname,tokens[1]); gR.cost=atof(tokens[2]); strcpy(gR.unit,tokens[3]); gR.stock_qty=atoi(tokens[4]); strcpy(gR.fac_no,tokens[5]); insert_new_goods_rec(gR); } else break; } fclose(fp);
exit(0); }
|