Thread: Adding a *.csv to MYSQL server

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

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?

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

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

> "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 \