樹心幽徑

« 20181110new_goods貨品基本資料編修程式(t18.c) | Main | 20181120將上下左右四塊圖磚合併在一起的工具程式(mtile.c) »

20181111將貨品資料csv檔轉入ilvssell資料庫的new_goods資料表(程式t19.c)
2018/11/11,22:14

(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);
}

 

迴響

 
Accessible and Valid XHTML 1.0 Strict and CSS Powered by LifeType