Post reply

Import Excel Data into SQL Table - Fails because of CR/LF

  • April 9, 2014 at 9:49 am

    #284463

    I need to import several Excel Worksheets into SQL tables for processing, after which I will need to export to CSV files to load back into the Excel Worksheets. All of the Worksheets import sucessfully except for one that fails because two of the cells in this Worksheet have CR/LF in the cell data.

    This import fails with "Text was truncated or one or more characters hs no match in the target code page."

    Does anyone know how to get the cells with CR/LF to import?

    Thanks,

    Tom

  • April 9, 2014 at 4:23 pm

    #1704712

    Have you tried setting the data type on those fields to nvarchar(max)?

  • April 9, 2014 at 5:13 pm

    #1704717

    Thomas Ashby (4/9/2014)

    I need to import several Excel Worksheets into SQL tables for processing, after which I will need to export to CSV files to load back into the Excel Worksheets. All of the Worksheets import sucessfully except for one that fails because two of the cells in this Worksheet have CR/LF in the cell data.

    This import fails with "Text was truncated or one or more characters hs no match in the target code page."

    Does anyone know how to get the cells with CR/LF to import?

    Thanks,

    Tom

    What are you using to import the spreadsheets? ACE drivers or ??? Can you post the code please?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 9, 2014 at 5:28 pm

    #1704719

    Personally I use SQL server integration services. If you're using the import/export wizard for SQL that is ssis. When you map the fields in your spreadsheet to you column in the SQL table you can change the default data type in the mapping table which is usually nvarchar(255) to a larger one on the source (excel) and destination (SQL table). For excel I would try nvarchar(2000) and work your way up until it's large enough.

  • April 10, 2014 at 1:51 am

    #1704769

    WillyWonka (4/9/2014)

    Personally I use SQL server integration services. If you're using the import/export wizard for SQL that is ssis. When you map the fields in your spreadsheet to you column in the SQL table you can change the default data type in the mapping table which is usually nvarchar(255) to a larger one on the source (excel) and destination (SQL table). For excel I would try nvarchar(2000) and work your way up until it's large enough.

    The problem is probably due to the CR/LF, which makes SSIS thinks a new line has started and it might add stuff to columns (other than the one holding the CR/LF) that doesn't belong there and suddenly you have an overflow.

    Normally you'd solve this by using text delimiters, but I'm not sure how to do this in Excel. Maybe you need to explicitly mark the column as Text?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • April 11, 2014 at 8:15 am

    #1705318

    I'm used the SQL Server 2008 Import Wizard and saved it as an SSIS / Integration Services package. When I look at the connection properties, I see:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\...\ExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=YES";

    Note: I just tried the following code, which worked but only captured the first 256 characters of one of the problem Excel cells ( the target field is nvarchar(2048).

  • April 11, 2014 at 8:16 am

    #1705320

    Also, it seemed to have dropped the CR LF's.

  • December 2, 2014 at 8:52 am

    #1762123

    Hi all,

    I have a small tool Import Excel, it from http://softwaredanang.wordpress.com[/url]

    Regards.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply