I was demoing something recently and needed to show someone how to grab some data from a CSV text file. Since this was a task the person needed to do regularly, but with different files, they wanted to ensure this was programmatic from a command line call outside of SQL Server. They knew the basics of bcp, but weren’t sure how to deal with a header row.
This is actually fairly simple as you will see.
I have a simple file that looks like this:
Time,System Production (Wh)
I have a basic table of this structure:
CREATE TABLE [dbo].[Stage](
[ProdTime] [varchar](20) NULL,
[ProdValue] [varchar](100) NULL
) ON [PRIMARY]
This is just a demo import from this sample file. If I run a basic bcp command, I’d typically run this:
bcp dbo.stage in export.csv -S AristotleSQL2017 -d way0utwest -T -t "," –c
This runs easily, as you see below:
However, this is my data:
That’s not right. The first row is a header row, and while I can quickly and easily fix this, it’s better not to have to process this. Easier to fix this on the import.
To do this, I need to look at the bcp documentation and include a flag. The –F flag is for the first row, which I want to set to 2. If I truncate the table and run this command:
bcp dbo.stage in export.csv -S AristotleSQL2017 -d way0utwest -T -t "," -c -F 2
I see these results:
No header row. There are still issues with this import, but this solves one problem, which is what the SQL New Blogger post is for.
This is a quick example of a post that is part of my daily work. I was showing a customer this, and I had to mock something up, so I grabbed a little sample data and did that.
I spent about 15 minutes around other work getting this post written and screenshots taken. You could do this as well and show how you import data in a cleaner fashion.