Link to home
Start Free TrialLog in
Avatar of sjterrell
sjterrellFlag for United States of America

asked on

ColdFusion MS SQL CSV Impport

I have about 80 CSV files (same format) that I want to import into a master table each month.  Some of the files are rather large (600k rows).  What would be the best way to do this with ColdFusion and SQL 2014?

Should i read each file, a row at a time and insert, or is there a bulk script or stored procedure that I could use.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Jason clark
Jason clark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of _agx_
Edit: Oops, took too long typing and didn't see the previous response ..

I'd recommend BULK INSERT. It requires a one time setup of special permissions (ie INSERT and ADMINISTER BULK OPERATIONS ), but it's MUCH faster than looping and is easy to use from cfquery.


Sample Table:
CREATE TABLE yourtable (Name varchar(100), [Address] varchar(100))

Open in new window


Sample CSV
Name,Address
Bob,123 Anyplace Drive
Mike,78 Nowhere Lane
Jack,221B Baker Street

Open in new window

       
Query (update dsn and file path) :
<cfquery datasource="#yourDSN#" result="bulkResult">            
BULK INSERT YourTable
FROM 'C:\path\to\theSavedFile.csv'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n',
    <!--- skip headers in first row --->   
   FIRSTROW = 2 
)   
</cfquery> 
<!--- DEMO: show results --->
<cfdump var="#bulkResult#">

Open in new window


> import into a master table each month

That said, personally, I'm a big fan of using "staging tables" for imports rather than inserting data directly into a critical table. It is a little more work, but offers a lot more control.  Basically data gets imported into the staging table first. There you scrub and validate it.  Once's it's validated, transfer it into the master table and discard the staging table data.
Avatar of sjterrell

ASKER

Using the bulk insert, can you limit what columns get imported.  For example, each file has columns A,B,C,D,E,F and some may have A,B,C,D,E,F,G,H,I
EDIT:

AFAIK no, but you could create a VIEW that contains only the columns in a specific file.

       CREATE VIEW YourView
       AS
       SELECT a, c
       FROM   YourTable

 Then bulk insert into the VIEW instead of the TABLE:

         BULK INSERT YourView FROM 'C:\path\to\theSavedFile.csv' ..... etc....
Do you know if bulk insert could also work for XLS files?
Edit:  

Bulk insert only handles text files.   If you need XLS,  you'll have to look at other options like linked servers or an ODBC DSN (a little more quirky and less desirable IMO).

BTW, I forget about format files...  Haven't used them in a while, but you might be able to use them to skip columns with bulk insert. Worth a look anyway.
Heading out. Will check back in the AM
sjterrell, do you still need help with this question?
Thanks for the recommendation.
You're welcome.