• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Bulk Insert SQL 2005 txt file


I have a text file, with data as follow


BULK INSERT dbo.table1 FROM 'C:\file.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FORMATFILE='C:\table1.fmt' )

Open in new window

Here is the format file:

1       SQLINT        0       4       ""         1     id1          ""
2       SQLCHAR       0       35      "\",\""    2     ID           SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       50      "\r\r\n"   3     Name         SQL_Latin1_General_CP1_CI_AS

Open in new window

When I use the above Bulk Insert, the data is being imported as follow:

id1      ID                    Name
1           ,"name" "1       Smith"

Identity Specification is set to Yes for id1.

I have also tried FIELDTERMINATOR = '","'

What would be the best way to import the data without having to remove " from the file?

1 Solution
Eugene ZCommented:
please try this way
How to remove unwanted Quotation Marks while importing a data file
ezzadinAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now