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'
)
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'
)