Storing a text file in a VARCHAR(MAX) column

Question

Post reply

Storing a text file in a VARCHAR(MAX) column

Mick Opalak

SSCommitted

Points: 1631
More actions
February 1, 2023 at 9:33 pm

#4144424

I'm using OPENROWSET to put the contents of a text file into a VARCHAR(MAX) column. However, it is stripping the carriage returns and line feeds out, so when I put the contents of the column back into Notepad I get one long line instead of the original text file. Is there a way to prevent this?

Viewing 6 posts - 1 through 5 (of 5 total)

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

ZZartin

SSC-Dedicated

Points: 30920

February 1, 2023 at 9:39 pm

#4144426

What command are you using to insert the data?

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

We never intended for SQL to be used for text management. You ought to be using the proper tool to store this data. As part Lane put it in one of his humorous essays, "the pumpkin is largely a failure is a shade tree."

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Mick Opalak wrote:

I'm using OPENROWSET to put the contents of a text file into a VARCHAR(MAX) column. However, it is stripping the carriage returns and line feeds out, so when I put the contents of the column back into Notepad I get one long line instead of the original text file. Is there a way to prevent this?

If you're manually copying "from the grid" to NotePad, it's the grid that's removing the CrLf characters. Try it with the text output instead of the grid.

If that doesn't work, then post back and we'll show you some code to check what's stored to see if the CrLfs are actually being stripped by the input process or not.

--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)

frederico_fonseca SSCoach Points: 16155 More actions · Answer 4

if copying from SSMS you can also go to tools->options->Query Results->SQL Server->Results to Grid and tick option "Retail CR/LF on copy or save"

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

jcelko212 32090 wrote:

We never intended for SQL to be used for text management. You ought to be using the proper tool to store this data. As part Lane put it in one of his humorous essays, "the pumpkin is largely a failure is a shade tree."

Please share what you consider to be "the proper tool".

--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)