樹心幽徑

« 20181107new_cust顧客基本資料編修程式(t16.c) | Main | 20181110new_goods貨品基本資料編修程式(t18.c) »

20181109顧客csv資料轉入new_cust程式t17.c
2018/11/09,21:05

 

(1)編譯

 treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ gcc t17.c `mysql_config --cflags --libs` -o t17

 

(2)轉入用資料檔cust.csv:

treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ cat cust.csv
cardid,uname,remain
922001,鼠,6000
922002,牛,5000
922003,虎,4000
922004,兔,3000
922005,龍,2000
922006,蛇,1000

 

(3)執行

treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ ./t17 cust.csv
0[922001] 1[鼠] 2[6000]
SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922001','鼠' ,'xxxx',6000.00)] 執行成功!

0[922002] 1[牛] 2[5000]
SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922002','牛' ,'xxxx',5000.00)] 執行成功!

0[922003] 1[虎] 2[4000]
SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922003','虎' ,'xxxx',4000.00)] 執行成功!

0[922004] 1[兔] 2[3000]
SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922004','兔' ,'xxxx',3000.00)] 執行成功!

0[922005] 1[龍] 2[2000]
SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922005','龍' ,'xxxx',2000.00)] 執行成功!

0[922006] 1[蛇] 2[1000]
SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922006','蛇' ,'xxxx',1000.00)] 執行成功!
(4)程式碼

treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ cat t17.c
#include <my_global.h>
#include <mysql.h>
#define HOST "localhost"
#define USER "treehrt"
#define PWD "abc123abc123"
#define DB "ilvssell"

typedef struct CUSTREC
{
  //char uid[10];
  double remain;
  //char inyear[5];
  //char dept[10];
  //char grade[10];
  //char class[10];
  char cardid[10];
  char uname[10];
  //char pid[10];
  //char addr[80];
  //char tel[15];
  //char oldclass[10];
  //char sno[2];
  //int lostc;
  //char gen_code;
  //char in_use;
  //char cid[4];

} custRec ;
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_cust_rec(custRec cR){
//        printf("待寫入的資料:cR.cardid=%s,cR.uname=%s cR.remain=%.2f\n",cR.cardid,cR.uname,cR.remain);
        char st[500];
        sprintf(st,"insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'%s','%s' ,'xxxx',%.2f)",cR.cardid,cR.uname,cR.remain) ;
    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;
  custRec cR;

  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(cR.cardid,tokens[0]);
        strcpy(cR.uname,tokens[1]);
        cR.remain=atof(tokens[2]);
        insert_new_cust_rec(cR);
      }
     else break;
    }
  fclose(fp);

  exit(0);
}

迴響

 
Accessible and Valid XHTML 1.0 Strict and CSS Powered by LifeType