本文介绍了在SQL中批量插入.txt文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试将.txt文件导入高级查询工具(我使用的SQL客户端)。到目前为止,我有:

I'm trying to import a .txt file into Advanced Query Tool (the SQL client I use). So far, I have:

CREATE TABLE #tb_test
(
id INTEGER,
name varchar(10),
dob date,
city char(20),
state char(20),
zip integer
);

insert into #tb_test
values
(1,'TEST','2015-01-01','TEST','TEST',11111)
;

bulk insert #tb_test
from 'h:\tbdata.txt'
    with
    (
    fieldterminator = '\t',
    rowterminator = '\n'
    );

我收到一条错误消息,说第1行存在语法错误。我是否缺少数据库, #tb_test来了(例如db。#tb_test)?

I receive an error message saying there's a syntax error on line 1. Am I missing a database from which #tb_test comes (like db.#tb_test)?

这是tbdata.txt文件中的一行:

Here's a line from the tbdata.txt file:

2,'TEST2','2012-01-01','TEST','TEST',21111


推荐答案

我对这个问题感到好奇,发现了以下解决方案:

I was curious with this question and I found the following solution:

您的数据以逗号分隔,但您尝试按TAB
进行拆分,这有两个选择:将文件数据更改为TAB分隔更改 fieldterminator ='\t ' fieldterminator =','

Your data is comma separated but you are trying to split by TABtwo options: change the file data to be TAB separated or change the fieldterminator = '\t' to fieldterminator = ','

直接加载时DATE格式有问题从文件中,我最好的解决方案是将临时字段 dob 更改为VARCHAR(20),然后在传递到最终显示/数据存储时将其转换为DATE。

The DATE format has issues when loading directly from a file, my best solution is to change the temp field dob to type VARCHAR(20) and then, when passing to the final display/data storage convert to DATE.

以下是正确的代码:

CREATE TABLE #tb_test
(
id INTEGER,
name varchar(10),
dob varchar(20),
city char(20),
state char(20),
zip integer
);

insert into #tb_test
values
(1,'TEST','2015-01-01','TEST','TEST',11111)
;

bulk insert #tb_test
from 'h:\tbdata.txt'
    with
    (
    fieldterminator = ',',
    rowterminator = '\n'
    );

这篇关于在SQL中批量插入.txt文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-13 21:17