Adding a *.csv to MYSQL server

This is a discussion on Adding a *.csv to MYSQL server within the C Programming forums, part of the General Programming Boards category; How do you read in a comma separated file and insert it into a mysql server database?...

  1. #1
    Registered User
    Join Date
    Dec 2007
    Posts
    10

    Adding a *.csv to MYSQL server

    How do you read in a comma separated file and insert it into a mysql server database?

  2. #2
    Frequently Quite Prolix dwks's Avatar
    Join Date
    Apr 2005
    Location
    Canada
    Posts
    8,048
    Well, this problem has two steps. (Assuming you code it yourself from scratch, without using any libraries that might smooth your way.)

    1. Read and parse the CSV file.
    2. 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.

  3. #3
    Registered User
    Join Date
    Dec 2007
    Posts
    10
    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

  4. #4
    Frequently Quite Prolix dwks's Avatar
    Join Date
    Apr 2005
    Location
    Canada
    Posts
    8,048
    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.

  5. #5
    Registered User
    Join Date
    Dec 2007
    Posts
    10

    I think I have the parsing done

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

  6. #6
    Registered User
    Join Date
    Mar 2005
    Location
    Mountaintop, Pa
    Posts
    1,059
    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 08:50 PM. Reason: typo

  7. #7
    Captain Crash brewbuck's Avatar
    Join Date
    Mar 2007
    Location
    Portland, OR
    Posts
    7,249
    Quote Originally Posted by Beckster View Post
    How do you read in a comma separated file and insert it into a mysql server database?
    Is there some reason that the "LOAD DATA INFILE" command is not sufficient? I'm aghast that you are writing a program to do this.

  8. #8
    Kernel hacker
    Join Date
    Jul 2007
    Location
    Farncombe, Surrey, England
    Posts
    15,677
    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.

  9. #9
    Registered User
    Join Date
    Dec 2007
    Posts
    10
    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);

  10. #10
    Registered User
    Join Date
    Dec 2007
    Posts
    10
    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.

  11. #11
    Registered User
    Join Date
    Dec 2007
    Posts
    10

    Not working

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

  12. #12
    and the hat of wrongness Salem's Avatar
    Join Date
    Aug 2001
    Location
    The edge of the known universe
    Posts
    32,671
    > "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

    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 );
    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:
    /* 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 );
    }
    The line was already full of commas, all you did was remove them just to put them back in again.

    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.
    I support http://www.ukip.org/ as the first necessary step to a free Europe.

  13. #13
    Registered User
    Join Date
    Dec 2007
    Posts
    10
    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?

  14. #14
    Registered User
    Join Date
    Dec 2007
    Posts
    10
    How are you parsing out the "," or is it automatically doing that when you do the INSERT. Also the macTS might have different lengths

  15. #15
    Registered User
    Join Date
    Dec 2007
    Posts
    10
    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)

Page 1 of 2 12 LastLast
Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Server Architecture
    By coder8137 in forum Networking/Device Communication
    Replies: 2
    Last Post: 01-29-2008, 10:21 PM
  2. Where's the EPIPE signal?
    By marc.andrysco in forum Networking/Device Communication
    Replies: 0
    Last Post: 12-23-2006, 07:04 PM
  3. Connecting to a mysql server and querying problem
    By Diod in forum C++ Programming
    Replies: 8
    Last Post: 02-13-2006, 09:33 AM
  4. Connect to server and get info from mysql db.
    By Sam Granger in forum C++ Programming
    Replies: 2
    Last Post: 01-26-2006, 05:11 AM
  5. socket question
    By Unregistered in forum C Programming
    Replies: 3
    Last Post: 07-19-2002, 01:54 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21