从CSV读取数据，并投入数据库 [英] Read Data from CSV and Put into Database

问题描述

我有6列的表，我有CSV导出这样，每行6项

I have a table with 6 columns and i have CSV Export like this with 6 entries per Line

me;val1;val2;val3;val4;val5;
me;val1;val2;val3;val4;val5;

我需要阅读这些条目，将其插入SQLITE3 Database.So序解析CSV我使用

I need to read these entries and Insert it into the SQLITE3 Database.So inorder to parse the CSV I use

void readcsv()
{
    FILE* stream = fopen("input.csv", "r");

    char line[1024];
    while (fgets(line, 1024, stream))
    {
        char* tmp = strdup(line);
        printf("Field 3 would be %s\n", getcsvfield(tmp, 3));
        // NOTE strtok clobbers tmp
        free(tmp);
    }
}
//Used for parsing CSV
const char* getcsvfield(char* line, int num)
{
    const char* tok;
    for (tok = strtok(line, ";");
            tok && *tok;
            tok = strtok(NULL, ";\n"))
    {
        if (!--num)
            return tok;
    }
    return NULL;
}

所以我不得不一次次给getcsvfield（TMP，fieldnumber）在插入查询像这样，还是有一个更清洁，高效的方式。

So will i have to call getcsvfield(tmp, fieldnumber) again and again in the Insert Query like this or is there a cleaner and efficient way.

execute("INSERT INTO table(a,b,c,d,e,f) VALUES('%s','%s','%s');",getcsvfield(tmp, 1),getcsvfield(tmp, 2),....... and so on

请指教。

推荐答案

一个更有效的方法将是一次解析线，营造一个指针数组的标记。然后，您可以通过数组项到执行这样的功能。

A more efficient method would be to parse the line once, creating an array of pointers to the tokens. You can then pass the array entries to the execute function like this

execute("INSERT INTO table(a,b,c,d,e,f) VALUES('%s','%s','%s');",token[0],token[1],...

下面是从文件中读取行的例子，打破了行成令牌，然后打印的标记。

Here's an example that reads lines from a file, breaks the line into tokens, and then prints the tokens.

#include <stdio.h>
#include <string.h>

int getTokens( char *line, char *token[], int maxt )
{
    int i;

    char *ptr = strtok(line, ";\n");
    for ( i = 0; i < maxt; i++ )
    {
        if ( ptr == NULL )
            return i;

        token[i] = ptr;
        ptr = strtok( NULL, ";\n" );
    }

    return i;
}

int main( void )
{
    const int maxt = 6;
    char line[1024];
    char *token[maxt];

    FILE *fp;
    if ( (fp = fopen( "input.csv", "r" )) == NULL )
        return 1;

    while ( fgets(line, sizeof line, fp) != NULL )
    {
        int count = getTokens( line, token, maxt );
        for ( int i = 0; i < count; i++ )
            printf( "%d: '%s'\n", i, token[i] );
        printf( "\n" );
    }

    fclose( fp );
}

这篇关于从CSV读取数据，并投入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！