OPENROWSET returns just one row

Question

Im trying to import values from a .csv file which has only one column. I keep getting all the values in just one row.

SELECT BulkColumn  
FROM OPENROWSET (BULK 'C:\Temp\myfile.csv', SINGLE_CLOB) MyFile

Here is a sample of the first rows of the files content

Nummer
072XXXXXX63
072XXXXXX76
07XXXXXX66
072XXXXXX4

Answer 1

The OPENROWSET documentation describes the SINGLE_CLOB option as:

returns the contents as a single-row, single-column rowset of type varchar(max)

So you will either need to split the string into multiple columns using the STRING_SPLIT function (or a similar method), use a format file to specify a row terminator for OPENROWSET, or use BULK INSERT, which allows you to specify a row terminator without the need to create a format file.

You could adapt the following code by customizing the FIELDTERMINATOR and ROWTERMINATOR as necessary for your input file. For example, the following code was tested on your provided sample file:

CREATE TABLE #bulkInsert (col1 NVARCHAR(MAX) NULL)

BULK INSERT #bulkInsert
FROM 'C:\Temp\myfile.csv'
WITH (FIELDTERMINATOR = N','
    , ROWTERMINATOR = N'0x0a' /* See https://stackoverflow.com/a/26758288/1582862 */
    , BATCHSIZE = 50000
    , TABLOCK
);

-- The sample rows were correctly bulk loaded, including the header row,
-- which you can remove from your file or filter out after bulk loading
SELECT *
FROM #bulkInsert
--Nummer
--072XXXXXX63
--072XXXXXX76
--07XXXXXX66
--072XXXXXX4