Excel to SQL Server: carriage return problems...

nebrown1 · June 13th, 2003, 09:25 AM

I have a sheet in an Excel workbook that I would like to import to a table in SQL Server. Three of my Excel columns are cells filled with text. Some of the cells have hard carriage returns in them, which show up in Excel as little rectangles. When I import them in SQL Server, they remain little rectangles and are not converted into what they really are: Chr(13) carriage returns.

PS. I know they really are Chr(13) because I examined them in VBA and sure enough, they are real carriage returns.

How to solve this?

chrislepingwell · June 17th, 2003, 09:05 AM

What are you importing them in to, text fields, char, nvarchar etc?

pgtips · June 18th, 2003, 07:11 AM

Are you sure its not just a display problem in whatever you're viewing the SQL Server data with.

It seems curious to me that you're happy that they show up as 'little rectangles' in Excel, but you're not happy when it gets displayed the same way from SQL Server.

Wilf_101 · June 7th, 2005, 09:54 AM

once imported into sql, do this :

update <tablename> set <fieldname> = replace(<fieldname>, char(10), char(13) + char(10))

job will then be a goodun.