Rows of CSVs in SQL into table of columns

Question

Let's say I have a table with 3 rows -- each row contains a CSV and I can guarantee that there will only ever be 1 comma

xxx,111
yyy,222
zzz,333

How could I select the table so that the resulting table has 2 columns as:

xxx    111
yyy    222
zzz    333

I've tried SELECT split(item,',') FROM A

But obviously, this won't work. Is there a functional way to do this without using cursors?

I'm using SQL Server 2008

cctan · Accepted Answer · 2012-01-16 09:30:58Z

3

There are no easy functional way like split() though. There is a way without using cursors:

SELECT
SUBSTRING(item, 1,
        CHARINDEX(',', item) - 1
    ) AS Field1,
SUBSTRING(item, CHARINDEX(',', item) + 1,
        LEN(item) - CHARINDEX(',', item)
    ) AS Field2
FROM A

edited Jan 16, 2012 at 9:30

answered Jan 16, 2012 at 5:56

cctan

2,0233 gold badges21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Renjith Kumar Over a year ago

In the above query, the first column (Field1) will have a comma appended to it. Plz replace the first part as SUBSTRING(item, 1, CHARINDEX(',', item) -1) AS Field1.

Andreas Rohde · Accepted Answer · 2012-01-16 07:41:30Z

1

Look at this post Split Function in Sql Server to break Comma-Separated Strings into Table. I think this will solve your Problem.

answered Jan 16, 2012 at 7:41

Andreas Rohde

6095 silver badges15 bronze badges

1 Comment

Andriy M Over a year ago

That's a different problem. The OP has already got a table with a single column and wants to make it a table with two columns instead (each value to be split into two). The post you are linking to is about splitting a single value into a column of values, which is not an uncommon problem, too, only it's entirely different.

Las Ten · Accepted Answer · 2012-01-16 21:09:44Z

I'm using this stored procedure to load .csv files into existing tables, it may help you. It will load all csv files in a folder matching a pattern into a table. You have to pass it

the folder name you have your .csv files in (like c:\temp),
a filename pattern (*_daily_export.csv),
the name of the table you want to load the files into (like MyTable)
a flag indicating whether you want to mode processed files into the "done" subfolder of the folder (1)
the delimiter character (, or ;)
the first data row (2 if you have headers)
the format file (see http://msdn.microsoft.com/en-us/library/ms178129.aspx)

It's not 100% percent foolproof (e.g. avoid too looong file names or spaces in them) but you can change it as you want.

-- ============================================================
-- Author:      László Tenki
-- Create date: 2011-06-20
-- Description: Data integration script for loading Excel files
--              using the standard MS data connectivity pack
--              Microsoft.ACE.OLEDB.12.0
-- ============================================================
ALTER procedure [di].[genericCSVImporter]
    -- parameters for the stored procedure
    @folder varchar(500), @fileNamePattern varchar(100), 
    @table varchar(128), @moveProcessed bit, 
    @fieldDelimiter varchar(1), @firstRow int, @formatFile nvarchar(25)
as
begin
    declare @query varchar(1000)
    declare @filename varchar(200)
    declare @formatFilePath varchar(200)

    declare @dir table ([name] varchar(200))
    set @query ='master.dbo.xp_cmdshell "dir ' + @folder + '\' + @fileNamePattern + ' /b"'
    set @query = replace(@query, '\\', '\')
    insert into @dir exec (@query)
    delete from @dir where name is NULL

    declare myc cursor for select [name] from @dir where [name]<> 'File Not Found' order by [name]
    open    myc 
    fetch next from myc into @filename

    while @@fetch_status = 0
        begin
        set @filename = @folder + '\' + @filename
        set @filename = replace(@filename, '\\', '\')
        set @formatFilePath = replace(@folder + '\' + @formatFile, '\\', '\')

        set @query ='BULK INSERT ' + @table + ' FROM ''' + @filename + 
            ''' WITH (FIELDTERMINATOR = ''' + @fieldDelimiter + ''', ROWTERMINATOR = ''\n'', ' + 
            'FIRSTROW = ' + cast(@firstRow as nvarchar(10)) + ', CODEPAGE = ''ACP'', ' + 
            'FORMATFILE=''' + @formatFilePath + ''')'
        print @query
        exec (@query)

        if @moveProcessed=1 
            begin
            set @query = 'master.dbo.xp_cmdshell "copy /Y ' + @filename + ' ' + @folder +'\done"'
            exec (@query)
            set @query = 'master.dbo.xp_cmdshell "del /Q ' + @filename + '"'
            exec (@query)
            end

        fetch next from myc into @filename
        end 

    close myc
    deallocate myc
end

Collectives™ on Stack Overflow

Rows of CSVs in SQL into table of columns

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related