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

MS SQL Bulk Insert

Hi,
I am getting multiple truncate error messages with this command, the source data on the specific field can normally fit in the destination file. I wonder how MS SQL count the character in csv file.
Bulk Insert dbo.xtemp From 'c:\test\agtest.csv'
     With ( FirstRow =2, FieldTerminator = ',', RowTerminator = '\n' )
In MySQL, I can handle  this with SET command in my load data  infile command.
Is there any equivalent in MS SQL, that can allow to write something like SET field1=left(trim(field1),50)
0
Omer-Pitou
Asked:
Omer-Pitou
  • 3
  • 2
2 Solutions
 
chaauCommented:
I think you need to use the Format File. You can easily create a format file for your table using the bcp utility:
bcp yourDb.dbo.xtemp format nul -f c:\test\xtemp.Fmt -n -T 

Open in new window

After the file is created you can use it with the Bulk Insert command:
BULK INSERT dbo.xtemp From 'c:\test\agtest.csv'
     With ( FirstRow =2, FieldTerminator = ',', RowTerminator = '\n', FORMATFILE = 'c:\test\xtemp.Fmt' ) 

Open in new window

Also, use the DATAFILETYPE parameter to tell the SQL Server what file format you have:
DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

Open in new window

So, if you have a UNICODE file use :
BULK INSERT dbo.xtemp From 'c:\test\agtest.csv'
     With ( FirstRow =2, FieldTerminator = ',', RowTerminator = '\n', FORMATFILE = 'c:\test\xtemp.Fmt',  DATAFILETYPE = 'widechar') 

Open in new window

0
 
Omer-PitouAuthor Commented:
Trying with bcp, I am getting an error message 'could not find stored procedure bcp
0
 
chaauCommented:
bcp is a Command line utility. You need to run it via cmd.exe.

Anyway, try first with the DATAFILETYPE:
BULK INSERT dbo.xtemp From 'c:\test\agtest.csv'
     With ( FirstRow =2, FieldTerminator = ',', RowTerminator = '\n', DATAFILETYPE = 'widechar') 

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Omer-PitouAuthor Commented:
Giving an error stating data file type was incorrectly specified as widechar
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Is there any equivalent in MS SQL, that can allow to write something like SET field1=left(trim(field1),50)
No. You can only do it after import but then you need to create the field bigger enough to accept all data.
Alternative is to use SSIS where you can create data transformations.
0
 
Omer-PitouAuthor Commented:
That is what I finally did. I set all the fields to varchar(250).
Ssis was't an option as I wanted it handled programmatically.
Just wonder why MS misses these basic stuffs along with others compared to MySql.
Thanks to all.
0
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

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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