Bulk Insert Fixed Width Text File Not Loading Properly

83 Views Asked by At

While trying to bulk insert a fixed width text file into a table in MS SQL Server, the first character in each field is getting pushed by one character in each row. How do I fix this?

The data is in this format -

M1DR3QQ3XE954K76EC5VQ302017-06-172021-07-24            
M1DR3QQ3XE954K76EC5VQ302021-07-242021-07-24            
M1E14UR7TK502G16C89CK302017-06-172020-11-24            
M1E50UN7PH498CA8WJ1UN692017-06-172018-07-09            

There are 700 such rows

This is the table that I have created

CREATE TABLE [TABLENAME(
    ROW1 [char](1) NULL,
    ROW2 [varchar](15) NULL,
    ROW3 [varchar](15) NULL,
    ROW4 [varchar](50) NULL,
    ROW5 [varchar](50) NULL,
    ROW6 [varchar](50) NULL
)

This is the bulk insert code that I used

BULK INSERT TABLENAME
FROM 'DATA FILE SOURCE'
WITH (
FIELDTERMINATOR = '',
ROWTERMINATOR = '\n' ,
FORMATFILE = 'FORMAT FILE SOURCE'
);

This is my format file

13.0
6
1       SQLCHAR       0       1       ""          1       Column name ""
2       SQLCHAR       0       11     ""          2       Column name ""
3       SQLCHAR       0       11     ""          3       Column Name ""
4       SQLCHAR       0       10     ""          4       Column Name ""
5       SQLCHAR       0       10     ""          5       Column Name""
6       SQLCHAR       0       12      ""          6       Column Name ""

I am loading all the data as varchar right now and will convert the data type later once the data is properly loaded to avoid complications during BULK INSERT.

This is the result that I am getting

M   1AW9F20MH32 2EW4FU4FV82 2017-06-17  2018-07-09              
    M1C56YV4MN6 44KC5FE3MU9 42018-11-0  32019-11-1  3           
     M1CC5H01WD 822DF4GX8GM 722017-06-  172018-07-  09          
      M1CY2XQ9P W154U69YG3Y N452017-06  -172018-07  -09         
       M1D12M47 DA293MU7NK4 QU692017-1  0-182018-0  7-09        
        M1DF6U9 1UM628FU3JF 7RM122017-  06-172018-  07-09       
         M1DH5G 89HM836N94J V7FU732017  -10-252018  -07-09      
          M1DK2 PX7EQ195W55 T40NG69201  7-06-17201  8-07-09     
           M1DR 3QQ3XE951DR 3QQ3XE9520  21-07-2220  21-07-24    
            M1D R3QQ3XE954K 76EC5VQ302  017-06-172  021-07-24   
             M1 DR3QQ3XE954 K76EC5VQ30  2021-07-24  2021-07-24  
              M 1E14UR7TK50 2G16C89CK3  02017-06-1  72020-11-24 

Instead of this

M   1AW9F20MH32 2EW4FU4FV82 2017-06-17     2018-07-09
M       1C56YV4MN64     4KC5FE3MU9      2018-11-03     2019-11-13
M       1CC5H01WD82     2DF4GX8GM72     2017-06-17     2018-07-09

Note that the 6th column that I have is completely empty.. I am just adding it into the table and the format file to maintain integrity and I will be dropping the column after my data is loaded into SQL. Is this happening as the 6th column is completely empty?

How do I fix this and load the data properly?

0

There are 0 best solutions below