We help IT Professionals succeed at work.
Get Started

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

133 Views
Last Modified: 2019-12-12
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
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE