CODEWITHSUNDEEP
sqlt-sql
jorame
jorame

Reputation: 2207

BULK INSERT adding two spaces at the beginning of each row after the first one

The below script is being use to do a BULK INSERT but some reason when executing the script after the first line it adds two space at the beginning of each row. Any idea why it might be doing that? I have search online but I can't find anything so far. 

 BULK INSERT IMPORT_DATA
 FROM 'C:\Users\jam\Desktop\JamWMS\Inbound\IG00000002.txt'
 WITH
 (
     ROWTERMINATOR = ';',
     CHECK_CONSTRAINTS
 )

Here is some sample data of what is happening:

IG0000002     312344001052      301234     23        2         S         43012342324         1001      5          
  IG0000002     312344001052      301234     23        2         S         43012342324         1002      3          
  IG0000002     312344001052      301234     23        2         S         43012342324         1003      4          
  IG0000002     312344001052      301234     23        2         S         43012342324         1004      2          
  IG0000002     312344001052      301234     23        2         S         43012342324         1005      1

This is how the file IG00000002.txt looks like

IG0000002     312344001052      301234     23        2         S         43012342324         1001      5          ;
IG0000002     312344001052      301234     23        2         S         43012342324         1002      3          ;
IG0000002     312344001052      301234     23        2         S         43012342324         1003      4          ;
IG0000002     312344001052      301234     23        2         S         43012342324         1004      2          ;
IG0000002     312344001052      301234     23        2         S         43012342324         1005      1          ;

Any help will be greatly appreciated.

Upvotes: 0

Views: 901

Answers (1)

Dave Markle
Dave Markle

Reputation: 97701

It's probably because you have a carriage return and linefeed there.

Try changing ROWTERMINATOR to ;\n instead.

Upvotes: 1

Related Questions