使用SQL Server读取文本文件 [英] Reading a text file with SQL Server

问题描述

我正在尝试从SQL查询(SQL Server 2005)中读取文本文件，但是一点都没有运气.我已经使用EXEC和xp_cmdshell尝试了各种方法，但是都无法正常工作.这是我尝试解决此问题的一般方法:

I am trying to read in a text file from an SQL query (SQL Server 2005) but am not having any luck at all. I've tried various things with EXEC and xp_cmdshell, but all aren't working. This is the general way I've tried to approach this:

CREATE TABLE temp (data varchar(2000));
INSERT temp EXEC master.dbo.xp_cmdshell 'type file.txt';

然后我尝试从临时表中选择数据.我搜索了很多东西，但我不知道自己出了什么问题.帮助吗?

I then try to select data from the temp table. I've searched around a lot and I can't tell what I'm getting wrong. Help?

推荐答案

您的文本文件是什么样的?每行一条记录吗?

What does your text file look like?? Each line a record?

您必须检出批量插入语句-应该类似于:

You'll have to check out the BULK INSERT statement - that should look something like:

BULK INSERT dbo.YourTableName
FROM 'D:\directory\YourFileName.csv'
WITH
(
  CODEPAGE = '1252',
  FIELDTERMINATOR = ';',
  CHECK_CONSTRAINTS
) 

就我而言，我正在导入CSV文件-但您也应该能够导入文本文件.

Here, in my case, I'm importing a CSV file - but you should be able to import a text file just as well.

从MSDN文档开始-这是一个示例，该示例有望用于每行一个字段的文本文件:

From the MSDN docs - here's a sample that hopefully works for a text file with one field per row:

BULK INSERT dbo.temp 
   FROM 'c:\temp\file.txt'
   WITH 
      (
         ROWTERMINATOR ='\n'
      )

似乎可以在我的测试环境中正常工作:-)

Seems to work just fine in my test environment :-)

这篇关于使用SQL Server读取文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！