在进行数据库操作时,经常需要从CSV文件中导入数据。本文将分享一个实际案例,介绍如何使用SQLServer将CSV文件数据导入到数据库表中,并解决在导入过程中遇到的一些问题。
在一次数据迁移任务中,需要将一个CSV文件中的数据导入到SQLServer数据库的表中。CSV文件中的数据格式如下:
HOU009,Windows Phone 7,Will Martinez,
11/10/2011,Houston; TX,999,2
HOU010,WPF for Business Applications,Will Martinez,
11/15/2011,Houston; TX,695,1
这些数据需要填充到目标表的相应列中。
目标表的结构如下:
CREATE TABLE [dbo].[Courses](
[id] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
[code] [varchar](50) NOT NULL,
[description] [varchar](200) NULL,
[instructor] [varchar](50) NULL,
[date] [date] NULL,
[venue] [varchar](50) NULL,
[price] [money] NULL,
[duration] [int] NULL,
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED ([id] ASC, [code] ASC)
)
在这个表中,有两个字段可能会带来挑战:一个是主键字段,其类型为uniqueidentifier,另一个是日期字段。
尝试使用以下SQL语句从CSV文件中导入数据:
BULK INSERT [dbo].[Courses]
FROM 'C:\Courses.csv'
WITH (fieldterminator = ',', rowterminator = '\n')
但是,第一次尝试并没有成功。遇到了以下错误:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\Courses.csv" could not be opened. Operating system error code 5 (Access is denied.).
将文件移动到了公共目录,并确保权限设置为“Everyone”。尽管如此,仍然没有成功,这次没有出现“访问被拒绝”的错误,但是插入操作返回了0行受影响。
猜测可能存在以下几个问题:
为了解决这些问题,决定创建一个新的临时表,这个表没有主键,并且使用datetime数据字段。临时表的结构如下:
CREATE TABLE [dbo].[CoursesTemp](
[code] [varchar](50) NOT NULL,
[description] [varchar](200) NULL,
[instructor] [varchar](50) NULL,
[date] [datetime] NULL,
[venue] [varchar](50) NULL,
[price] [money] NULL,
[duration] [int] NULL
)
然后再次运行插入语句:
BULK INSERT [dbo].[CoursesTemp]
FROM 'C:\Users\Public\Downloads\Courses.csv'
WITH (fieldterminator = ',', rowterminator = '\n')
这次成功了!终于看到了期待的结果...(20行受影响)。
现在,可以利用刚刚插入到临时表中的数据,并将其插入到“实时”表中。
INSERT [dbo].[Courses]
(code, description, instructor, date, venue, duration)
SELECT code, description, instructor, CAST(date AS date), venue, duration
FROM [dbo].[CoursesTemp]
注意,没有列出Id列,因为它有一个uniqueidentifier,默认为NEWSEQUENTIALID,它会自动为每条记录生成一个GUID。
为了处理日期字段的问题,注意到有一个CAST语句可以将数据转换为适当的数据类型。