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

Question

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 ?

@Drew Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'LOCAL'. — T2Admin, Sep 05 '16 at 11:45
@Drew The default was without LOCAL and it was not giving me any error. Now i added LOCAL and it gives me error, so I guess it's not working either. The slashes are correct. — T2Admin, Sep 05 '16 at 11:49
How many databases do you have? You say the rows are not being inserted into mySql and you show us a sql server error message. — Dan Bracuk, Sep 05 '16 at 11:49
@DanBracuk I have only 1 DB with many tables, but I double checked, so the tabels and db is set correctly. Beside, if the table would be spelled wrong, then the ColdFusion would give me error that it can't connect to DB. — T2Admin, Sep 05 '16 at 11:52
Try using UNC path. (Even if local or using a mapped drive, I found this to work better.) To determine if a row is giving you a problem, use the "errorfile" property. http://stackoverflow.com/a/15243801/693068 — James Moberg, Sep 05 '16 at 11:55
@JamesMoberg I tried that and it's now working. It doesn't give me any error, but the errorfile is not existing at all. — T2Admin, Sep 05 '16 at 12:12
How is the datasource app.dns configured? From the error (some comments above) it seems configured to use jdbc driver for sqlserver. — Aris2World, Sep 05 '16 at 12:40
@Aris2World Yes, I use Microsoft JDBC Driver 4.0 for SQL Server, the how's and why's can be found here: https://www.hass.de/content/coldfusion-10-how-configure-data-sources-microsoft-jdbc-driver-40-sql-server — T2Admin, Sep 05 '16 at 13:35
@Drew yes, I made mistake before, I did a research and I am using Azure SQL. Apologize for misinformation — T2Admin, Sep 05 '16 at 13:41
Maybe no rows are imported for an invalid data or format. I suggest you to create an error file as explained here http://stackoverflow.com/questions/15242757/import-csv-file-into-sql-server — Aris2World, Sep 05 '16 at 14:16

score -1 · Accepted Answer · answered Sep 11 '16 at 08:07

-1

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>

answered Sep 11 '16 at 08:07

T2Admin

147
2
14

-1. This does not answer the original question, which is about bulk loading tools. Plus the question itself is very unclear. The text says the database used is MySQL, yet some of the syntax, question tags and error message are for SQL Server. – Leigh Sep 11 '16 at 19:00

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

1 Answers1