I need to perform a dataload every day from a csv available online e.g. http://www.supplier.com/products.csv Once I've dumped the csv into a sql table I can do the processing I then need to update / insert etc. The problem is that I don't know how to automate the dataload.
I was hoping I could use a SQL job / task, scheduled to run each day at 06:00, give it a uri and that it could then access the data in the csv...
How can I do that?
You can schedule a SQL Agent job to download the file locally and use BULK INSERT:
CREATE TABLE StagingCSV
(
col1 VARCHAR(60),
col2 VARCHAR(60),
col3 VARCHAR(60),
col4 VARCHAR(60),
-- ...
)
GO
(error rows will be ignored)
BULK
INSERT StagingCSV
FROM 'c:\mycsvfile.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
Other methods:
About Bulk Import and Bulk Export Operations
Importing Bulk Data by Using BULK INSERT or OPENROWSET
You can use Powershell to download a file:
$clnt = new-object System.Net.WebClient
$url = "http://www.supplier.com/products.csv "
$file = "c:\temp\Mycsv.txt"
$clnt.DownloadFile($url, $file)
Another simple (although not free, but still rather cheap) solution is to use the SQL# library which would allow you to do this in just a few lines of T-SQL. This would make it really easy to automate via a SQL Agent Job.
You could emulate the Powershell method (suggested by Mitch) with a single command to grab the CSV file and then read it into the table with another command:
DECLARE @Dummy VARBINARY(1)
SELECT @Dummy = SQL#.INET_DownloadFile('http://www.location.tld/file.csv',
'C:\file.csv')
INSERT INTO dbo.RealTable (Column1, Column2, ...)
EXEC SQL#.File_SplitIntoFields 'C:\file.csv', ',', 0, NULL, NULL
OR, you could bypass going to the file system by reading the CSV file straight into a local variable, splitting that on the carriage-returns into a Temp Table, and then split that into your table:
CREATE TABLE #CSVRows (CSV VARCHAR(MAX))
DECLARE @Contents VARBINARY(MAX)
SELECT @Contents = SQL#.INET_DownloadFile('http://www.location.tld/file.csv',
NULL)
INSERT INTO #CSVRows (CSV)
SELECT SplitVal
FROM SQL#.String_Split(CONVERT(VARCHAR(MAX), @Contents),
CHAR(13) + CHAR(10), 1)
INSERT INTO dbo.RealTable (Column1, Column2, ...)
EXEC SQL#.String_SplitIntoFields 'SELECT CSV FROM #CSVRows', ',', NULL
You can find SQL# at: http://www.SQLsharp.com/
I am the author of the SQL# library, but this seems like a valid solution to the question.
I have not seen an example where you can bulk insert directly from a url.
So, for the remainder, use a sql job and bulk insert.
Bulk inserts made easy: http://www.mssqltips.com/tip.asp?tip=1207
Here's a quick excerpt:
BULK INSERT dbo.ImportTest FROM 'C:\ImportData.txt' WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )
You can also perform the file download by using an Integration Services Task: http://www.sqlis.com/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx
