import from excel

Question

Good day,

I have the below column data in excel, and I wish to import to SQL table, but I get an error

Please assist

Thanks

Answer 1

Hi @ZimisoMoyo-5501,

Thanks for your update.

Instead of exporting Excel data to SQL Server database, please try to firstly export your data to Access database. Then export Access database to SQL Server database.

Reference:Resolved: Truncation error on SQL Server Import and Export Wizard

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Text was truncated or one or more characters had no match in the target code page.

I guess it's the first one, you try to import a larger text then allowed by the target or flow definition.
What data type & size has the target column?

Answer 3

Hi @ZimisoMoyo-5501,

You bumped into a well known limitation of the SQL Server Import and Export Data Wizard.
The same limitation exists in the SSIS Excel Source Adapter.
They both accept 255 chars maximum per column from a MS Excel file.

A solution that works well for such scenario is to use Microsoft ACE OLEDB Provider and OPENROWSET() directly in SSMS. Along the following:

SQL

-- 1. as rectangular
SELECT *
 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
   'Excel 12.0 Xml; HDR=NO;
    Database=c:\Users\Documents\dada.xlsx',
    [Sheet1$]);

SELECT *
   , F1 AS col1, F2 AS col2
 FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0',
   'Excel 12.0 Xml; HDR=NO;
    Database=c:\Users\Documents\dada.xlsx',
    [Sheet1$]);

SELECT *
 FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
   'Excel 12.0 Xml; HDR=NO;
    Database=c:\Users\Documents\dada.xlsx',
    [Sheet1$]);

-- 2. as rectangular range
SELECT *
 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
   'Excel 12.0 Xml;
    Database=c:\Users\Documents\dada.xlsx',
    'SELECT * FROM [Sheet1$A1:D2]');