Click here to Skip to main content
15,881,967 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

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



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'
)
Posted
Updated 11-Jul-18 12:58pm
Comments
RedDk 11-Jul-18 18:14pm    
You might want to clarify that this two-lined internet browser representation of a one-lined string (there is no linefeed or carriage return) is the object about which the posted disconnect is going to occur ...

1 solution

Quote:
Sql bulk insert with multiple fieldterminator

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900