Create a table and insert data from csv in SQL Server

Question

Can I tell a bulk insert command to create a table with the first row as columns somehow from a .csv file?

BULK INSERT DataExtraction
FROM '\\server03\c$\wwwroot\Filename.csv'
WITH
    (FIRSTROW = 2,
     FIELDTERMINATOR = ',',  --CSV field delimiter
     ROWTERMINATOR = '\n',   --Use to shift the control to next row
     TABLOCK
    )

Is this an ad hoc type of thing you need to do occasionally or something that needs to be repeated? Could you use the SQL Server Import and Export Wizard which is actually creating an SSIS package behind the scenes? msdn.microsoft.com/en-us/library/ms141209(v=sql.105).aspx — Isaac
– Isaac, Commented Feb 8, 2017 at 13:55
It needs to be repeated, I saw the wizard does it so I assumed there must be a way. — Patrick
– Patrick, Commented Feb 8, 2017 at 14:33

bitch_cakes · Accepted Answer · 2017-02-08 21:23:04Z

I've had some luck selecting the top 1 row of the CSV into a TEMP table and using dynamic SQL to build the table.

--import the headers from the CSV. Make sure your XML is right
SELECT  TOP 1 * INTO #temp
FROM    OPENROWSET(BULK 'c:\temp\csv.csv', FORMATFILE= 'c:\temp\csv.xml') AS a

--define and set your variables, one to each column of the CSV
DECLARE @var1 VARCHAR(50)
DECLARE @var2 VARCHAR(50) 

SET @var1 = (SELECT TOP 1 col0 FROM #temp) 
SET @var2 = (SELECT TOP 1 col1 FROM #temp) 

--build your dynamic query to create Table1. Table name can also be dynamic 
declare @sql varchar(MAX) 
SET @sql = 'create table Table1 ( ' + @var1 +' varchar(50), ' +@var2+ ' varchar(50) )'

--EXECUTE your sql
EXEC (@sql)

And your XML may look like (Thanks to Jiri Hubacek):

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="510" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="510" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="510" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="col0" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="col1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="col2" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Collectives™ on Stack Overflow

Create a table and insert data from csv in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related