Skip to main content
A
ACS LLC
Inspiring
April 4, 2019
Answered

CSV import data into SQL

  • April 4, 2019
  • 1 reply
  • 5164 views

Could anybody tell me if there are any built in functions within CF2016 that would assist in importing a CSV file into SQL?

At the moment I'm downloading a ZIP file from a data provider, taking the CSV out of it, putting it into Access, importing the Access into SQL, what a pain! It's time to automate it all!

This topic has been closed for replies.
Correct answer ACS LLC

I figured out the order, I see that the order of the fields in the design of the database is how it's placed in, so I just need to reorder them with a database table edit


I managed to get it to work, well almost.

The first row, first column has a value of 0 (literally a 0 in there not empty), if I run with this value I get an error

[Macromedia][SQLServer JDBC Driver][SQLServer]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ipFROM)

if I change the value it works (It's inserting into a BIG INT)

Once I fixed that it imported but I ran into problem number 2, some of the text in the VARCHAR fields have double quotes around them. These are not in the ORIGINAL CSV, I cut and paste the data from the original CSV to a TXT file and imported that, and no problem, so Excel was adding some control characters that did not work well with the import.

*** SOLUTION TO ABOVE FOUND

MS SQL does not work well with CSV's even if the file name is a .CSV, you need to add  FORMAT='CSV', to the BULK QUERY

1 reply

EddieLotter
EddieLotter
Inspiring
April 4, 2019

SQL is a language, not a database engine, so you can't import something into SQL.

The method of importing external data into a database depends on the database engine you're using.

If you're using Microsoft SQL Server, then this article may answer your question: Import CSV file into SQL Server using T-SQL query

    A
    ACS LLCAuthor
    Inspiring
    April 5, 2019

    I've been sucessfully manually importing the data from an Access database (manually created from the CSV source)

    I'm using ColdFusion 2016 and MS SQL SERVER 2017. The CSV has roughly 400,000 records.

    What I'm trying to figure out is, once I have the CSV file on the server, what's the best way to problematically read all of those records and insert them into the SQL database.

    Of course I could set up a datasource to the CSV, read all of the records and then loop over them with 400,000 inserts, but I'm thinking that would probably be the worst way possible of doing it, there must be a better way?

    EddieLotter
    EddieLotter
    Inspiring
    April 5, 2019

    https://forums.adobe.com/people/ACS+LLC  wrote

    What I'm trying to figure out is, once I have the CSV file on the server, what's the best way to problematically read all of those records and insert them into the SQL database.

    The link I gave you shows you how. Look at the "bulk insert" example query. You can run that query from your ColdFusion code, you do not need to "read all those records" at all.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices