Import a CSV with a Header Row using BCP–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Here are some hints to get started.

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.

BCP Basics

I have a simple file that looks like this:

Time,System Production (Wh)
08/01/2022,"58875"
08/02/2022,"61260"
08/03/2022,"60866"
08/04/2022,"66395"

I have a basic table of this structure:

CREATE TABLE [dbo].[Stage](
     [ProdTime] [varchar](20) NULL,
     [ProdValue] [varchar](100) NULL
) ON [PRIMARY]
GO

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 Aristotle\SQL2017 -d way0utwest -T -t "," –c

This runs easily, as you see below:

2022-08-17 12_56_58-D__Downloads

However, this is my data:

2022-08-17 12_56_51-solarloading.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (69))_ - Micros

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 Aristotle\SQL2017 -d way0utwest -T -t "," -c -F 2

I see these results:

2022-08-17 13_02_04-solarloading.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (69))_ - Micros

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.

SQLNewBlogger

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.