Highlighted

CSV import data into SQL

Enthusiast ,
Apr 04, 2019

Copy link to clipboard

Copied

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!

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

TOPICS
Database access

Views

2.3K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CSV import data into SQL

Enthusiast ,
Apr 04, 2019

Copy link to clipboard

Copied

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!

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

TOPICS
Database access

Views

2.3K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 04, 2019 0
Advocate ,
Apr 04, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 04, 2019 1
Enthusiast ,
Apr 05, 2019

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 05, 2019 0
Advocate ,
Apr 05, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 05, 2019 0
Enthusiast ,
Apr 05, 2019

Copy link to clipboard

Copied

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 05, 2019 0
Advocate ,
Apr 05, 2019

Copy link to clipboard

Copied

A very basic example:

<cfquery datasource="#variables.MyDS#">

  BULK INSERT #variables.TableName#

  FROM '#variables.CsvPathAndFile#'

  WITH

  (

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '\n'

  )

</cfquery>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 05, 2019 0
Enthusiast ,
Apr 05, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 05, 2019 0
Enthusiast ,
Apr 05, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 05, 2019 0
Enthusiast ,
Apr 05, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 05, 2019 0