Search code examples
sql-servercsvcoldfusionazure-sql-databasecoldfusion-11

Import CSV file into database(sql) via BULK INSERT or LOAD DATA

Want to insert some data into SQL. I am using cfquery tag to manipulate SQL queries.

This is my first try:

<body>
    <cfquery NAME="Insert" DATASOURCE="#app.dsn#">
        LOAD DATA INFILE "F:\app\new.csv"
        INTO TABLE FROM CSVIMP
        FIELDS TERMINATED BY ',' 
        LINES TERMINATED BY '\r\n';
    </cfquery> 
    <output>
        <CFQUERY NAME="Show" DATASOURCE="#app.dsn#">
            SELECT * FROM CSVIMP
        </CFQUERY>
        #Show#
    </output>
    </body>

And because this was not working I give it a shoot with BULK INSERT.
My second try:

<body>
<cfquery NAME="Insert" DATASOURCE="#app.dsn#">
    BULK INSERT CSVIMP FROM ‘F:\app\new.csv’
    WITH (FIRSTROW = 2, FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’)
</cfquery> 
<output>
    <CFQUERY NAME="Show" DATASOURCE="#app.dsn#">
        SELECT * FROM CSVIMP
    </CFQUERY>
    #Show#
</output>
</body>

But even this is not working. It's not giving me any kind of error, just the values from csv is not inserted in MySQL.

The CSV is like this:
new.csv:

Test,Test2
Test3,Test4

What I'm I doing wrong ?

Solution

  • I just needed to put it in a loop.

    <cfloop query="csv_query">
                <cfquery datasource="#app.dsn#">
                    INSERT INTO CSVIMP (
                        Test1,
                        Test2
                    ) VALUES (
                        '#csv_query.column_1#',
                        '#csv_query.column_2#'
                    )
                </cfquery>
        </cfloop>