How do you read in a comma separated file and insert it into a mysql server database?
How do you read in a comma separated file and insert it into a mysql server database?
Well, this problem has two steps. (Assuming you code it yourself from scratch, without using any libraries that might smooth your way.)
- Read and parse the CSV file.
- Write the data to the database.
Reading and parsing the CSV file can be done, broadly, by opening the file, reading in each line, and splitting up each line -- perhaps with strtok(). *shudders*
Then you need to write the data out. I'm not sure how to do this exactly, but you could probably write the commands to a file and then have some program read that information to write to the database, or execute a command to insert each element into the database -- like with system(). *shudders again*
Have you tried it? Do you know how to read in files? If not, check out the tutorials. cprogramming.com/tutorial.html
dwk
Seek and ye shall find. quaere et invenies.
"Simplicity does not precede complexity, but follows it." -- Alan Perlis
"Testing can only prove the presence of bugs, not their absence." -- Edsger Dijkstra
"The only real mistake is the one from which we learn nothing." -- John Powell
Other boards: DaniWeb, TPS
Unofficial Wiki FAQ: cpwiki.sf.net
My website: http://dwks.theprogrammingsite.com/
Projects: codeform, xuni, atlantis, nort, etc.
I can read it in, not sure on how to use "," as the demlimiter and the INSERT command can be used on MYSQL but from there I'm stuck
Well, read up on strtok(). http://www.cppreference.com/stdstring/strtok.html
Or better yet, try it yourself. Just parse the string for a "," or beginning of the string, and record the position of the next "," or end of the string, and take the contents of the string between those two points.
"The INSERT command can be used on MYSQL." Does this mean that you can run MYSQL or whatever from the command line, and use a file for its input -- a file which might contain a lot of INSERT commands?
dwk
Seek and ye shall find. quaere et invenies.
"Simplicity does not precede complexity, but follows it." -- Alan Perlis
"Testing can only prove the presence of bugs, not their absence." -- Edsger Dijkstra
"The only real mistake is the one from which we learn nothing." -- John Powell
Other boards: DaniWeb, TPS
Unofficial Wiki FAQ: cpwiki.sf.net
My website: http://dwks.theprogrammingsite.com/
Projects: codeform, xuni, atlantis, nort, etc.
Code:#include <stdio.h> #include <stdlib.h> #include "csvParse.h"; int parseLine(char line[],csvLine_t *data) { char *ptr; int fieldNum=0; int len; ptr=strtok(line,","); while (ptr != NULL) { fieldNum++; //printf("Line=%s\n",line); switch (fieldNum) { case 1: data->epoch = atol(ptr); break; case 2: data->macTS=strtoull(ptr, (char **)NULL,10); break; case 3: len=strlen(ptr); strncpy(data->macAddr,ptr+1,len-2); data->macAddr[17]=0; break; case 4: data->seqNum=atoi(ptr); break; case 5: data->sigStr=atoi(ptr); break; case 6: data->noise=atoi(ptr); break; case 7: data->attna=atoi(ptr); break; case 8: data->pcktLen=atoi(ptr); break; default: printf("error: fieldNum=%d",fieldNum); return -1; } ptr=strtok(NULL,","); } return 0; } int main(void) { FILE *inf; char line[200]; csvLine_t csvData; char *ret; int err; inf=fopen("packets.csv","r"); ret=fgets(line,200,inf); while ((!feof(inf)) && (ret != NULL)) { ret=fgets(line,200,inf); //printf("line=%s\n",line); if (ret != NULL) { err=parseLine(line,&csvData); if (err != 0) { printf("Bad error from parseLine()"); return -1; } printf("Epoc=%lu,MTs=%llu,MAC=%s,SN=%d,sig=%d,ns=%d,AT=%d,pklen=%d\n", csvData.epoch, csvData.macTS, csvData.macAddr, csvData.seqNum, csvData.sigStr, csvData.noise, csvData.attna, csvData.pcktLen); } } return 0; }
If your parser works, then you can use the following sample code as a template to load your database.
Code:// Database layout // Database name: Test // Table name: Table1 // Fields: // id INT // data1 VARCHAR(100) // data2 VARCHAR(100) // data3 VARCHAR(100) #include <stdio.h> #include <string.h> #include <winsock2.h> #include <mysql.h> #define CONNECT_HOST "localhost" #define CONNECT_USER "root" #define CONNECT_PSW "****" #define CONNECT_DB "test" #define CONNECT_TABLE "table1" #define BUFFERSIZE 4096 MYSQL *con; MYSQL_ROW row; MYSQL_RES *res; MYSQL *init; #define E_NOMEM 4 #define E_NOCONN 5 #define E_NOPERM 6 #define E_QUERYFAIL 7 #define E_STOREFAIL 8 #define E_OK 0 #define E_OPENDB -1 #define E_SENDSQL -2 int open_db_connection (void) { int rv; init = mysql_init (NULL); if (init == NULL) return E_NOMEM; con = mysql_real_connect (init, CONNECT_HOST, CONNECT_USER, CONNECT_PSW, CONNECT_DB, 0, NULL, 0); if (con == NULL) return E_NOCONN; rv = mysql_select_db (con, CONNECT_DB); if (rv != 0) return E_NOPERM; return E_OK; } void close_db_connection (void) { mysql_close (con); } int main (void) { int err, i = 0; char insert_string[BUFFERSIZE]; for (i = 0; i < BUFFERSIZE; i++) insert_string[i] = '\0'; if ((err = open_db_connection ()) != E_OK) { printf ("open_db_connection () fails with %d!\n", err); return E_OPENDB; } sprintf(insert_string, "INSERT INTO Table1(id, data1, data2, data3) VALUES ('%d', '%s','%s', '%s')", 1, "d1", "d2", "d3"); if ( mysql_real_query(init,insert_string,strlen(insert_string)) != 0 ) { printf("Insert failed\n"); return -1; } else printf("Insert successful\n"); mysql_free_result (res); close_db_connection (); return E_OK; }
Last edited by BobS0327; 12-05-2007 at 09:50 PM. Reason: typo
Or the mysql admin PHP script, which, along with about 1 million other things, can take a number of different input file types and add the content to the table of your choice. [or create a new database from such a file]
This of course assumes that ALL of the content in the CSV file is wanted in the database table - but if that's not what you want, it's probably easier to create a new temporary table with all of the content from your csv, and then write a little bit of sql-code to move the data from your temporary table to the actual table you wan it in.
--
Mats
Compilers can produce warnings - make the compiler programmers happy: Use them!
Please don't PM me for help - and no, I don't do help over instant messengers.
I agree that it shouldn't be an entire program but my partner says else wise.
Sample code I need to modify
Code:/* Connect to database */ if(!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)){ fprintf(stderr, "%s\n", mysql_error(conn)); exit(1); } /* send to SQL query */ if (mysql_query(conn, "show tables")){ fprintf(stderr, "%s\n", mysql_error(conn)); exit(1); } res = mysql_use_result(conn); /* output table name */ printf("MySQL tables in mysql database:\n"); while ((row-mysql_fetch_row(res)) != NULL) printf("%s\n", row[0]); /* close connection */ mysql_free_result(res); mysql_close(conn);
It is important that there are separate fields, also. Its not just passing a string to one field, I need the string separated (which it is by commas) and then put into a fields which I have already created in the table.
The SQL insert commend doesn't work. The code compiles alright but I'm left with a blinking cursor when I execute the code....
Code:#include <stdio.h> #include <stdlib.h> #include <mysql/mysql.h> #include <string.h> #include "csvparse.h" main () { /* Declaration of variables */ MYSQL *conn; MYSQL_RES *res; MYSQL_ROW row; char *server = "localhost"; char *user = "root"; char *password = "new-password"; char *database = "wireless"; FILE *ifp; conn = mysql_init(NULL); char *ret; char line[200]; int err; int max = 17; csvLine_t csvData; /* Connect to database */ if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) { fprintf(stderr, "%s\n", mysql_error(conn)); exit(1); } /* Open file and read a line to string */ ifp=fopen("packets.csv","r"); if (ifp==NULL) { printf("Can't open packets.csv\n"); exit(1); } fgets(line,200,ifp); while (!feof(ifp)) { parseLine(line,&csvData); /* Insert structure into table */ mysql_query(conn, "USE wireless;\ INSERT INTO master (epoch,macTS,macAddr,seqNum,sigStr,noise,attna,pcktLen)\ VALUES(csvData.epoch, csvData.macTS, csvData.macAddr,\ csvData.seqNum,csvData.sigStr,csvData.noise,csvData.attna,csvData.pcktLen)"); } /* send SQL squery */ if (mysql_query(conn, "SELECT * from master")){ fprintf(stderr, "%s\n", mysql_error(conn)); exit(1); } res = mysql_use_result(conn); /* output table name */ printf("MySQL fields in mysql tables:\n"); while ((row = mysql_fetch_row(res)) != NULL) printf("%s \n", row[0]); /* close connection */ mysql_free_result(res); mysql_close(conn); }
> "USE wireless;\
> INSERT INTO master (epoch,macTS,macAddr,seqNum,sigStr,noise,attna,pck tLen)\
> VALUES(csvData.epoch, csvData.macTS, csvData.macAddr,\
> csvData.seqNum,csvData.sigStr,csvData.noise,csvDat a.attna,csvData.pcktLen)");
Well it's not going to know anything about your variable names is it?
You need to store the substituted values in a string, like
In fact, if these are in the same order that they were read from the file, then you've wasted your time (as already noted). All you needed to do was strip the newline off the end of the line when you read the file with fgets, and do this:Code:sprintf( buff, "VALUES(%s,%s,%s,%s,%s,%s,%s,%s)", csvData.epoch, csvData.macTS, csvData.macAddr, csvData.seqNum, csvData.sigStr, csvData.noise, csvData.attna, csvData.pcktLen );
The line was already full of commas, all you did was remove them just to put them back in again.Code:/* use return of fgets to control the loop, not feof() */ while ( fgets( line, sizeof line, ifp ) != NULL ) { char buff[BUFSIZ]; char *p = strchr( line, '\n' ); if ( p ) { *p = '\0'; /* remove newline */ } sprintf( buff, "USE wireless;" "INSERT INTO master (epoch,macTS,macAddr,seqNum,sigStr,noise,attna,pcktLen)" "VALUES(%s)", line ); /* Insert structure into table */ mysql_query(conn, buff ); }
Also note the way long "strings" are folded, ie, not by using \
If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
If at first you don't succeed, try writing your phone number on the exam paper.
1196741136,1.01466E+19,00:0F:F7:BE:FF:80,2307,-74,-96,1,177 - .csv file
So I only need one %s, or multiple?
How are you parsing out the "," or is it automatically doing that when you do the INSERT. Also the macTS might have different lengths
Do I need to even tell it the name of the columns?
Just add the VALUES attribute and then (%s, %s, %s, %s, %s, %s)