Return to Answer

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.

For example, 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

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

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.

For example, you could adapt the following code by customizing the FIELDTERMINATOR and ROWTERMINATOR as necessary for your input 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 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.

For example, 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

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.

For example, you could adapt the following code by customizing the FIELDTERMINATOR and ROWTERMINATOR as necessary for your input 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 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.

For example, you could adapt the following code by customizing the FIELDTERMINATOR and ROWTERMINATOR as necessary for your input 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
);