如何从在线CSV文件向SQL Server数据库插入数据？

Question

我需要每天从在线csv文件（例如http://www.supplier.com/products.csv）执行数据加载。将csv转储到sql表格后，我可以进行所需的处理，然后需要更新/插入等操作。问题是我不知道如何自动化数据加载。

我希望能够使用SQL作业/任务，安排在每天06:00运行，给它一个URI，然后它就可以访问csv中的数据...

我该怎么做？

- Simon Martin

Answer 1

您可以安排一个SQL代理作业来下载文件到本地，并使用BULK INSERT：

CREATE TABLE StagingCSV
(
   col1 VARCHAR(60),
   col2 VARCHAR(60),
   col3 VARCHAR(60),
   col4 VARCHAR(60),
    -- ...
)
GO

（错误行将被忽略）

BULK
INSERT StagingCSV
FROM 'c:\mycsvfile.txt'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)
GO

其他方法：

关于批量导入和批量导出操作

使用BULK INSERT或OPENROWSET导入大量数据

您可以使用Powershell下载文件：

$clnt = new-object System.Net.WebClient
$url = "http://www.supplier.com/products.csv "
$file = "c:\temp\Mycsv.txt"
$clnt.DownloadFile($url, $file)

- Mitch Wheat

---

这将在虚拟专用服务器上进行，不确定它将拥有哪个版本的 SQL，但 Powershell 方法很有趣 - 我以前使用过 WSH，这看起来类似。我会试一下并告诉你我的进展。 - Simon Martin

太棒了 - Powershell脚本正好符合我的需求。它很可爱，而且轻便，我在托管服务器上安排它并进行调度不会有任何问题。我甚至可能会更进一步地使用它来调用实际的数据加载过程！ - Simon Martin

Answer 2

另一个简单（虽然不是免费的，但仍然相当便宜）的解决方案是使用SQL#库，它可以让您只用几行T-SQL代码就完成此操作。这将使通过SQL Agent作业自动化变得非常容易。

您可以使用单个命令模拟Powershell方法（由Mitch建议），以抓取CSV文件，然后使用另一个命令将其读入表中：

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

或者，您可以通过直接将CSV文件读入本地变量并在回车符上拆分成临时表，然后将其拆分为您的表，从而绕过访问文件系统的步骤：

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

您可以在以下网址找到SQL#：http://www.SQLsharp.com/ 我是SQL#库的作者，但这似乎是问题的有效解决方案。

- Solomon Rutzky

---

SQL#库看起来非常有趣 - 能够从SQL运行所有内容肯定对我有优势，这样我就不必依赖于下载文件。设置此项是否需要特殊权限？这将在托管服务器上进行，因此我可能没有SA帐户，我担心我无法“安装”它。 - Simon Martin

@Simon：对于任何SQLCLR项目，您需要在服务器级别启用CLR。我认为托管公司应该已经完成了这项工作，如果没有完成，可以提出请求。对于查看数据库之外的CLR功能（例如Internet和文件系统功能），需要将DB设置为可信任模式（好吧，还有另一种方式，但我正在努力研究中，暂不支持）。这需要dbo访问权限，您应该拥有此权限。如果您有任何其他问题或想要测试此功能，您可以访问SQLsharp.com网站并直接与我联系。 - Solomon Rutzky

Answer 3

我还没有见过可以直接从 URL 批量插入的例子。

因此，剩下的部分使用 SQL 作业和批量插入。

批量插入变得更容易：http://www.mssqltips.com/tip.asp?tip=1207

以下是一个快速摘录：

BULK INSERT dbo.ImportTest FROM 'C:\ImportData.txt' WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

- Phil C

Answer 4

您还可以使用集成服务任务执行文件下载： http://www.sqlis.com/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

- pcofre

---

我假设SSIS需要完整版本的SQL？我在我的本地SQL Express中找不到它 - 或者在Fasthosts（我将在其上托管此解决方案）的提及。 - Simon Martin