t-sql parse comma delimited file to create table using column names found in csv file

maqskywalker
maqskywalker used Ask the Experts™
on
I ‘m using SQL Server 2016 and SQL Server Management Studio.

I have a script that looks like this.

-- ********** Script 1 - Start of Script ********** --

---------- Step 1 - if table exists drop it ----------
DROP TABLE IF EXISTS [dbo].[LooneyEmployees]
---------- Step 1 - if table exists drop it ----------

---------- Step 2 - Create table to insert data ----------
CREATE TABLE dbo.LooneyEmployees
(
    EmployeeID INT,
    LastName NVARCHAR(40),
    FirstName NVARCHAR(40)
)
---------- Step 2 - Create table to insert data ----------

---------- Step 3 - Builk insert the data from csv file ----------
BULK INSERT dbo.LooneyEmployees
FROM 'C:\test\LooneyEmployees.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)
---------- Step 3 - Builk insert the data from csv file ----------

---------- Step 4 - Check to see if the data go inserted into the table ----------
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
FROM [TestDatabase].[dbo].[LooneyEmployees]
---------- Step 5 - Check to see if the data go inserted into the table ----------

-- ********** Script 1 - End of Script ********** --

Open in new window


When I run this script, it exports the data from a comma delimited file and then creates a table and populates the table using BULK INSERT.
My comma delimited file I’m using for my script is called LooneyEmployees.csv I’m attaching it to this post.

If you look at LooneyEmployees.csv in a text editor like notepad it looks like this:

LooneyEmployees.csv
Notice how the column names are in the first row.

So when I run my script I get this. The script works fine.

 when executing my script
In my script, in Step 2 I create the table and declare the table columns.

Instead of declaring the columns like I am doing in Step 2, Is there a way to look at Row 1 of the CSV file and then creating the table using the column names from the column names found in Row 1 of the CSV file?

See sometimes the LooneyEmployees.csv file will look like this:

 LooneyEmployee.csv (initially)
Then after week 2 the LooneyEmployees.csv file will look like this.

 LooneyEmployee.csv (after 2 weeks)
The csv will always have these fields and they will always be of this datatypes:
EmployeeID INT, LastName NVARCHAR(40), FirstName NVARCHAR(40) so I can just hard code these columns.

The only field that will change are the Hours columns and those will always be Integer columns.
LooneyEmployees.csv
LooneyEmployeesAfter2Weeks.csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
see https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15

You should add FIELDQUOTE='"' to cover data enclosed in quotes in a field in the event there are commas that are not part of data separators.
You shoukd also include first row=2 to skip the csv header row.

Not sure if you have to do it regularly, why not use a powershell with bcp.exe

Author

Commented:
Thanks for your assistance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial