使用 C++ 将 .csv 上传到 SQL 服务器 [英] Upload .csv to SQL server using C++

问题描述

我在 Windows 7 下使用 Visual Studio 2012 Express 版本.另一方面，SQL 服务器是SQL Server 2008 R2".

I am working with Visual Studio 2012 Express version under Windows 7. On the other hand, the SQL server is "SQL Server 2008 R2".

我尝试过以下 C++ 代码:

I've tried following C++ codes:

#include <iostream>
#include <windows.h>
#include <string>
#include <sql.h>
#include <sqltypes.h>
#include <sqlext.h>
using namespace std;

void main()
{
    clock_t start = clock();

    SQLHANDLE sqlevent, sqlconnection, sqlstatement;

    if(SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlevent))       
    {
        cout<<"The sqlevent has failed to be created."<<endl;
        system("pause");
        return;
    }


    if(SQL_SUCCESS != SQLSetEnvAttr(sqlevent, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0))
    {
        cout<<"The sqlevent has failed to be initialized."<<endl;
        system("pause");
       return;
    }

    if(SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlevent, &sqlconnection))
    {
        cout<<"The sqlconnection has failed to be created."<<endl;
        system("pause");
        return;
    }

    SQLCHAR retstring[10000];
    SQLDriverConnect(sqlconnection, NULL, (SQLCHAR*)("DRIVER={SQL Server Native Client 10.0};SERVER=DATASERVER;DATABASE=DATABASE;UID=CrystalReports;PWD=PASSWORD"), SQL_NTS, retstring, 10000, NULL, SQL_DRIVER_NOPROMPT);  

    if(SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, sqlconnection, &sqlstatement))
    {
        cout<<"The sqlstatement has failed to be created."<<endl;
        system("pause");
        return;
    }

    string commandline;

    commandline = "CREATE TABLE NEW_TABLE ( ID VARCHAR(10), AGE FLOAT) GO ";

    if(SQL_SUCCESS != SQLExecDirect(sqlstatement, (SQLCHAR*)(commandline.c_str()), SQL_NTS))
    {
        cout<<"The create table sql command has failed to excute."<<endl;
        system("pause");
        return;
    }

    commandline = "BULK INSERT NEW_TABLE FROM 'C:/temp/datafile.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n') GO";

    if(SQL_SUCCESS != SQLExecDirect(sqlstatement, (SQLCHAR*)(commandline.c_str()), SQL_NTS))
    {
        cout<<"The import sql command has failed to excute."<<endl;
        system("pause");
        return;
    }

    clock_t end = clock();

    cout<<"Import from .csv file to SQL Server costs "<<((end-start)/double(CLOCKS_PER_SEC))<<" seconds."<<endl;

    system("pause");

    return;

}

当我运行这段代码时，程序总是在创建表处停止.

When I run this code, the program always stops at create table.

不知道是不是没有建表的权限，所以手动建了个表.并弹出警告:

I wonder if I don't have the permission to create a table, thus I mannually created a table. And there is a warning pops out:

我可以知道上图中显示的警告是我的代码失败的原因，还是我只是在代码中犯了错误?

May I know if the warning shown in the above image is the reason my code fail, or I simply made mistakes in the code?

非常感谢.

推荐答案

您可能想要检查该帐户已被授予哪些权限.

You may want to check what permissions that account has been GRANTED.

您将需要 INSERT 权限以及 BULKADMIN 服务器角色.

You will need permissions to INSERT as well as to BULKADMIN server role.

但是正如您在 这篇文章 根据文件所在的位置，它可能会变得毛茸茸的.你可以在

But as you can see in this post it can get hairy depending on where the file is located. You can at

这篇关于使用 C++ 将 .csv 上传到 SQL 服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！