2

Is there a way to pass the result from OPENROWSET BULK into a variable so i can use STRING_SPLIT to divide it into rows?

Something like:

    DECLARE @MyString = FROM OPENROWSET (BULK 'C:\Temp\File.csv', SINGLE_CLOB) MyFile

SELECT * FROM STRING_SPLIT ( @MyString , '\n' )

1 Answer 1

2

Here is one solution.

I used REPLACE to replace the CR/LF with a single character I knew would not be in my file (tilde) and then used that in STRING_SPLIT

DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10)
DECLARE @BulkColumn VARCHAR(max)

SELECT @BulkColumn = replace(BulkColumn, @CrLf, '~')
FROM OPENROWSET(BULK 'C:\Temp\z.txt', SINGLE_CLOB) MyFile

SELECT *
FROM string_split(@BulkColumn, '~')

| value |
|-------|
| one   |
| two   |
| three |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.