CODEWITHSUNDEEP
sql-servert-sql
Simon Martin
Simon Martin

Reputation: 4231

How can I insert into to a SQL Server database data from an online CSV?

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?

Upvotes: 3

Views: 5057

Answers (4)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

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.

Upvotes: 2

pcofre
pcofre

Reputation: 4076

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

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300749

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)

Upvotes: 6

Phil Helix
Phil Helix

Reputation: 3733

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 )

Upvotes: 0

Related Questions