7

I am getting error when I am trying to do bulk insert:

BULK INSERT #tbl_InterCompanyUploadDetail_Staging 
FROM '\\SVRP03546008461D\QA\UploadTemplatewithvalidation.xlsx'
 WITH (FIRSTROW = 6, FIELDTERMINATOR ='\t', ROWTERMINATOR ='\\n' )

Error that I am getting is :

Bulk load data conversion error (truncation) for row 6, column 2 (Oracle Company Code).

The column in Excel has data as 470 and in database column is varchar (10). So what could be the reason for the error.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Anirudh Agarwal
  • 655
  • 2
  • 7
  • 29
  • 2
    As the error message says, truncation is the reason for the error. Presumably the column in the data has more than 10 characters. – Gordon Linoff May 03 '18 at 10:40
  • No, I have only ten rows in it and every row has 3 character. That is why I am confused. – Anirudh Agarwal May 03 '18 at 10:52
  • I didn't know that `BULK INSERT` could insert `.xlsx` files. Unless this is a new feature.... I don't think that's possible. What does this file look like if you open it in notepad? – Nick.Mc May 03 '18 at 11:46
  • it open in encrypted format – Anirudh Agarwal May 03 '18 at 11:47
  • I saved in txt format it open fine. ok So any idea how can we figure out FIELDTERMINATOR for excel – Anirudh Agarwal May 03 '18 at 11:52
  • 1
    The documentation explains it very well: [Import data from Excel to SQL Server](https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017). `BULK INSERT` supports only plain text files, it can't read from Excel file. – Vladimir Baranov May 08 '18 at 07:30
  • 5
    To spell it out: you first need to export your xlsx file as a text file (say CSV) then import the text file. – Nick.Mc May 08 '18 at 12:24

4 Answers4

4

The Issue

BULK INSERT may not work with xlsx files, try converting the .xlsx file to .csv file to achieve this (using BULK INSERT)

1st Solution - using OPENROWSET

Try using OPENROWSET with Microsoft.ACE.OLEDB.12.0 provider:

Insert into <rawdatatable> 
select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                         'Excel 12.0;Database=D:\SSIS\FileToLoad.xlsx;HDR=YES', 
                         'SELECT * FROM [Sheet1$]')

OR

SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=D:\Desktop\Data.xlsx', [Sheet1$]);

2nd Solution - using OPENDATASOURCE

SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\Desktop\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];

References

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

Replace "\n" with "0x0a" as ROWTERMINATOR and try again.

Or also

 ROWTERMINATOR = '''+cast (0x0000 as char(1))+'''

Let me know if it works.

Check also this.

Liquid Core
  • 1
  • 6
  • 27
  • 52
0

I doubt using XLSX file with BULK INSERT. If XLSX file supported, then FIELDTERMINATOR and ROWTERMINATOR are not needed.

XLSX is zip file so I guess (but not sure) XLSX not supported and you are getting truncation error because it is reading it as pure text file and BULK INSERT getting long text up to FIELDTERMINATOR.

To confirm, you try increasing length of column up to some thousand character and run BULK INSERT, if you get garbage character then it is reading it as pure text file. may be garbage character could be same as you open same xlsx file in notepad or notepad++.

par
  • 1,061
  • 1
  • 11
  • 29
0

You can't bulk load XLSX into SQL Server. You CAN convert the XLSX to a tab delimited text file and bulk load.

If this is a one-off operation I would recommend converting to text first (but beware how Excel exports certain types like dates and large numbers). Or you can use the Import/Export wizard (https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql)

If this is a process you need to repeat I would create an SSIS script.

Jeremiah Cooper
  • 117
  • 6