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  

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

  • Jeff Moden has a plausible solution for this. Jul 2, 2018 at 14:26
  • 1
    Why not use BULK INSERT where you can control all sorts of things, including specifying a row terminator? Jul 2, 2018 at 15:35
  • @AaronBertrand because i do not want to add the values to a table. I just need them for a query. But ive figured that BULK INSERT into a temporary table is probably the best way to go. I dont really get temp tables though. They are created and exist just like normal tables.
    – Daarwin
    Jul 2, 2018 at 15:37
  • 1
    There are a lot of differences. A #temp table is created in tempdb, is only visible to your session, and so two users can create #foo at the same time and not interfere with each other. Jul 2, 2018 at 15:38

1 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:


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

-- The sample rows were correctly bulk loaded, including the header row,
-- which you can remove from your file or filter out after bulk loading
FROM #bulkInsert
  • After executing i ran SELECT * FROM #bulkInsert and got only empty rows. Millions of empty rows.
    – Daarwin
    Jul 2, 2018 at 14:51
  • 1
    @Lautaro I added a test case using your provided sample file and updating the field terminator to match the file. I'm seeing the correct rows, so if it's not working for you it would be useful to link to the exact sample file that is not working as you expect and include the exact T-SQL you are using to process that sample file. Jul 2, 2018 at 15:22
  • I can only get it to work if i change it to "FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' " But still the temp table gets more than a million rows when the file only has 2500.
    – Daarwin
    Jul 2, 2018 at 15:27
  • I appologize! It turns out that the file actually contains a million empty rows after the values. Thank you for your help!
    – Daarwin
    Jul 2, 2018 at 15:33

Your Answer

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

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