BULK INSERT into SQL Server - using FIELDTERMINATOR

Question

I have this query:

BULK INSERT Employee
FROM 'E:\\file.txt' --location with filename
WITH
(
   FIELDTERMINATOR = ' ',
   ROWTERMINATOR = '\n'
)
GO

And in the my file, the data looks like this:

43266200 6827           43295200 1393/05/23 14:26:26     18      1

I want insert this data into my table, but in file in the my fieldterminator have a error and SQL Server complains about space or tab between fields error. How can I solve this?
can i use this?

BULK INSERT Employee
    FROM 'E:\\file.txt' --location with filename
    WITH
    (
       FIELDTERMINATOR = ' ' or '  ' or '        ',
       ROWTERMINATOR = '\n'
    )
    GO

or how can i import this txt file into sql server?

FROM 'E:\\file.txt' Is this the actual SQL you're running or are you trying to extract it from your C# source code, sans "? Because it looks like the latter. In which case, pay attention to the actual SQL statement being run. — ta.speot.is
– ta.speot.is, Commented Nov 1, 2014 at 6:27
i think between the field in my file has a tab space and simple space for example 43266200space6827\t43295200 — behzad razzaqi
– behzad razzaqi, Commented Nov 1, 2014 at 6:30

marc_s · Accepted Answer · 2014-11-01 07:27:38Z

4

If you have a tab between your fields in the data file, then use this:

BULK INSERT Employee
FROM 'E:\\file.txt' --location with filename
WITH
(
   FIELDTERMINATOR = '\t',
   ROWTERMINATOR = '\n'
)
GO

The \t denotes a tab - that should tell SQL Server to interpret a tab as a separator between two fields.

answered Nov 1, 2014 at 7:27

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

behzad razzaqi Over a year ago

thanks for attention to my problem,but my friend in the my file for example between the 43266200 and 6827 field have a 3 space and between 6827 and 43295200 have a \t

behzad razzaqi Over a year ago

and when your query run in my sql server:

behzad razzaqi Over a year ago

Msg 4832, Level 16, State 1, Line 1 Bulk load: An unexpected end of file was encountered in the data file. Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". error

marc_s Over a year ago

You need to clean your file before importing - you cannot have three spaces in one place, and a tab in another. This has to be consistent throughout the file!

behzad razzaqi Over a year ago

but my file has 27000000 record! how can i clean record in big txt file?

|

Musakkhir Sayyed · Accepted Answer · 2016-08-26 09:47:06Z

0

Code:

BULK INSERT #ProspectiveFile
                  FROM ''C:\Matt\' + @DataFileName + '.txt''
                  WITH (ROWTERMINATOR = ''\n''
                       ,FIELDTERMINATOR = ''    '')

edited Aug 26, 2016 at 9:47

Musakkhir Sayyed

7,21813 gold badges44 silver badges67 bronze badges

answered Aug 26, 2016 at 8:40

user6760623

1

Collectives™ on Stack Overflow

BULK INSERT into SQL Server - using FIELDTERMINATOR

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related