CSV import data into SQL

[ACS LLC]

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!

[EddieLotter]

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

[ACS LLC]

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]

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.

[ACS LLC]

ahh, I see now, it's the bulk insert

BULK INSERT dbo.SampleCSVTable

FROM 'C:\Sample CSV File.txt'

WITH

(

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '\n'

)

GO

How would I run this using CF? I add it as a stored procedure and then execute it using CF to trigger the stored procedure? (Never done that before). Would you have any points on how to get from this to the CF part?

Thanks!

[EddieLotter]

A very basic example:

<cfquery datasource="#variables.MyDS#">
  BULK INSERT #variables.TableName#
  FROM '#variables.CsvPathAndFile#'
  WITH
  (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
  )
</cfquery>

[ACS LLC]

I'm almost there I was actually looking at creating a stored procedure, even better if I can execute from CF.

Question, how do I control which field in the CSV goes into the appropriate field in the database table?

Thanks

[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

[ACS LLC · Accepted solution]

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