带有多个fieldterminator的Sql批量插入 [英] Sql bulk insert with multiple fieldterminator

问题描述

大家好，我需要你的帮助我有这个文本文件（记事本）包含这个：



465649（一个标签）001（ine tab）迈克（一个） tab）01-may-2018（两个标签）lkjhlh（空格）sdfsd（多个空格）sss（tab;）dd





我想将此txt文件中的数据导出到sql表，并确保使用此代码使用批量插入：

Hello guys please I need your help I have this text file (notepad) contain this:

465649 (one tab) 001 (ine tab) mike (one tab) 01-may-2018(two tabs) lkjhlh (space)sdfsd (many spaces) sss (tab;)dd


I want to export the data from this txt file to sql table and sure I use bulk insert using this code:

Bulk insert table1
from 'C:\Users\user\Desktop\file.txt'
with
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
) 

这里的问题是我跑的时候有0行效果是代码，这是因为txt文件中的单词之间的字段终止符，有时是一个标签，有时是2个标签，一次是空格和其他时间使用这个（;）



那么解决方案是什么？我怎样才能放置一个以上的FIELDTERMINATOR，或者如果我能自动看到那些终结器？

----------------------- ------------

2-请同样我对同一个例子有其他疑问：如何使用自动增量提交ID，因为在sql中我放了id字段在这里它给我错误的信息，因为他开始从第一列导入数据。我可以制作任何东西，所以批量插入从数据库的第二个字段开始，并留下第一个，因为它的ID？



非常感谢你



我尝试过：

The problem here is I got 0 row effect when I run this code and this is because the field terminator between the words in the txt file, some times it's one tab and sometimes 2 tabs and one time is space and other time using this (;)

so what is the solution? how can I put more than one FIELDTERMINATOR or if I can make it automatic see those terminators ?
-----------------------------------
2- please also I have other question on the same example: how can I make ID filed with auto increment, because in sql when I put the id field here it give me wrong info because he start importing the data from first column. can I make any thing so the bulk insert start from the second field on the data base and leave the first one because its for the id ?

Thanks you very much

What I have tried:

Bulk insert table1
from 'C:\Users\user\Desktop\file.txt'
with
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

推荐答案

Quote：

带有多个fieldterminator的SQL批量插入

Sql bulk insert with multiple fieldterminator

从未想过在批量插入之前预处理文件并规范化分隔符？

Never thought about preprocessing the file and normalize separators before bulk insert ?

这篇关于带有多个fieldterminator的Sql批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！