CODEWITHSUNDEEP
sqlsql-serverexcel
tempidope
tempidope

Reputation: 863

Exporting SQL Server table containing a large text column

I have to export a table from a SQL Server, the table contains a column that has a large text content with the maximum length of the text going up to 100,000 characters.

When I use Excel as an export destination, I find out that the length of this text is capped and truncated to 32,765.

Is there an export format that preserves the length?

Note:

  1. I will eventually be importing this data into another SQL Server
  2. The destination SQL Server is in another network, so linked servers and other local options are not feasible
  3. I don't have access to the actual server, so generating back up is difficult

Upvotes: 1

Views: 3401

Answers (2)

Jérémy Gachon
Jérémy Gachon

Reputation: 252

The .sql is the best format for sql table. Is the native format for sql table, with that, you haven't to concert the export.

Upvotes: -1

Thom A
Thom A

Reputation: 95564

As is documented in the Excel specifications and limits the maximum characters that can be stored in a single Excel cell is 32,767 characters; hence why your data is being truncated.

You might be better off exporting to a CSV, however, note that Quote Identified CSV files aren't supported within bcp/BULK INSERT until SQL Server 2019 (currently in preview). You can use a characters like || to denote a field delimited, however, if you have any line breaks you'll need to choose a different row delimitor too. SSIS, and other ETL tools, however, do support quote identified CSV files; so you can use something like that.

Otherwise, if you need to export such long values and want to use Excel as much as you can (which I actually personally don't recommend due to those awful ACE drivers), I would suggest exporting the (n)varchar(MAX) values to something else, like a text file, and naming each file with the value of your Primary Key included. Then, when you import the data back you can retrieve the (n)varchar(MAX) value again from each individual file.

Upvotes: 3

Related Questions