sjterrell
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.c sv' ..... etc....
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.c
ASKER
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.
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?
ASKER
Thanks for the recommendation.
You're welcome.
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:
Open in new window
Sample CSV
Open in new window
Query (update dsn and file path) :
Open in new window
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.