SQL server Excel export truncation problem

Question

Hi,

every time i run following query in SQL Server

insert into test.dbo.Nenmeet
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Documents and Settings\nenmeet.singh\Desktop\PD_data.xlsx;
HDR=yes','Select * from [Collated$]')

the data in table is truncated to 510 and sql server generates the following error for most of the fields:

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server '(null)' returned truncated data for column '[Microsoft.ACE.OLEDB.12.0].IPC - Current'. The actual data length is 600 and truncated data length is 510

Answer 1

Hello,

Please, could you give more informations about your problem ?

- where are you executing your query ? in SQL Server Management Studio, in an application , in a SSIS package?

- version (2000,2005,2008,2008 R2 ) , edition (MSDE,Express,Standard,Entreprise...) of your SQL Server

- version of your Excel ( i suppose 2007 )

As i have problems with the english language ( it is not my mother tongue ), i am not sure to have understood your length problem.Please, could you tell us what is the length of your column in the Excel file and the length of the related column in your SQL Server table ( for SQL Server , for example nvarchar(480) or varchar(70) or nchar(650) ). what are the values of 600 or 510 ? in the Excel column or in the SQL Server table ?

We are waiting for your feedback to try to help you more efficiently

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

1. where are you executing your query ?

SQL Server Management Studio

2. version (2000,2005,2008,2008 R2) ?

2008 R2 Express

3. version of your Excel ?

2007

4. length in excel cell ?

between 600 to 10,000

5. column in SQL Server table ?

all the columns are VARCHAR(MAX)

Answer 3

Are you using English data in Excel spreadsheet? Have you tried to use nvarchar(max)?

 

Thanks,
Ming.
WDAC Team, Microsoft.

 

---

Pak-Ming Cheung - MSFT

Answer 4

1. Are you using English data?

yes

2. Have you tried to use nvarchar(max)?

yes, but giving same errors

Answer 5

Hi,

See the last paragaraph in this article - http://www.databasejournal.com/features/mssql/article.php/3875421/SSIS-2008-Import-and-Export-Wizard-and-Excel-based-Data.htm.

If it is supposed to be a scheduled task to import data into a sql server table I would create a SSIS package and configure column length there.

---

Sergei

Answer 6

I know this is an old post, but I just found a great solution, and wanted to share:

http://colinmackay.co.uk/blog/2011/08/12/running-queries-on-excel-spreadsheets-using-sql-server-2008/

Follow the instructions, step by step.  It works perfect!!!

Answer 7

hi the below lnk may help you out.

http://stackoverflow.com/questions/1039385/truncation-errors-importing-to-sql-server-2005-from-excel