CODEWITHSUNDEEP
sqlasp.netsql-serverexcelntext
user2829785
user2829785

Reputation: 1

Remove unnecessary control character when exporting record to Excel to SQL

I am currently doing system decom for a ASP.NET system and plan to have all the system data stored in Excel file. The challenge I face is that there is a column of ntext data type and the data is split up into mutiple row when exporting to Excel. I have tried by removing all control characters as per below. But it is still split up into multiple row. Can anyone help? The system is currently using SQL Server 2012.

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    (cast(p.bp_comment as nvarchar(max)), CHAR(9), ''), CHAR(10), ''), CHAR(11), '') , CHAR(12), '') , CHAR(13), '') 
    , CHAR(14), '') , CHAR(15), '') , CHAR(16), '') , CHAR(17), '') , CHAR(18), '') , CHAR(19), '') , CHAR(20), '') 
    , CHAR(21), '') , CHAR(22), '') , CHAR(23), '') , CHAR(24), '') , CHAR(25), '') , CHAR(26), '') , CHAR(27), '') 
    , CHAR(28), '') , CHAR(29), '') , CHAR(30), '') , CHAR(31), '') 
    , CHAR(1), '') , CHAR(2), '') , CHAR(3), '') , CHAR(4), '') , CHAR(5), '') , CHAR(6), '') , CHAR(7), '') 
    , CHAR(8), '') , '\n', ''), '\r', '') as [Comment],

To remove all control character especially newline and carriage return via SQL using SQL Server DBMS.

Upvotes: 0

Views: 115

Answers (1)

user2829785
user2829785

Reputation: 1

Thanks Sterner,AlwaysLearning and siggemannen. I have found the root cause for this.It is due to maximum character in a cell for Excel 365 is 32,767.The comment fields that exceeds this length will be split up to multiple row.Attahced is the link for the documentation of Excel 365 Excel specifications and limits

Upvotes: 0

Related Questions