1

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
  • Jeff Moden has a plausible solution for this. – Shaulinator Jul 2 '18 at 14:26
  • 1
    Why not use BULK INSERT where you can control all sorts of things, including specifying a row terminator? – Aaron Bertrand Jul 2 '18 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 '18 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. – Aaron Bertrand Jul 2 '18 at 15:38
3

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
| improve this answer | |
  • After executing i ran SELECT * FROM #bulkInsert and got only empty rows. Millions of empty rows. – Daarwin Jul 2 '18 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. – Geoff Patterson Jul 2 '18 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 '18 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 '18 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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