Automatically import csv content into SQL Server

Question

Automatically import csv content into SQL Server

Deries

Right there with Babe

Points: 780
More actions
January 27, 2014 at 12:01 pm

#287738

I have a CSV & xls file and I'd like import the data from CSV into SQL server existing table efficiently.
This process should be done automatically on a daily basis (schedule task). I dont have rights to use SSIS/ Import Export Wizard.
Appreciate any automated stored procedure and support from you all.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 1

Deries (1/27/2014)
I have a CSV & xls file and I'd like import the data from CSV into SQL server existing table efficiently.
This process should be done automatically on a daily basis (schedule task). I dont have rights to use SSIS/ Import Export Wizard.
Appreciate any automated stored procedure and support from you all.

BULK INSERT in T-SQL would be the way to go for the CSV file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

And there's a recently published article by Jeff on BULK INSERT that will help you as an introduction to your process.

http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Deries Right there with Babe Points: 780 More actions · Answer 3

Thank you Jeff, Could you or anyone give me a sample code to test it out.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Deries (1/27/2014)
Thank you Jeff, Could you or anyone give me a sample code to test it out.

Read what I posted 😛

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 5

Deries (1/27/2014)
Thank you Jeff, Could you or anyone give me a sample code to test it out.

The link that Luis posted has such sample code in the article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Deries Right there with Babe Points: 780 More actions · Answer 6

Hi Luis,

I read your post and tried that for testing. I being a developer it didnt allow me to do insert bulk load because of the rights issue. Thats why i asked for some help to upload the csv file to the existing table.

i am trying to upload the csv file from my local drive to the server.

Msg 4834, Level 16, State 4, Line 12

You do not have permission to use the bulk load statement.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

You have a problem here. You need to do something but you don't have the permissions to do it.

I'd say that you need to talk to your manager, DBA or sysadmin to get the permissions or define that you're not able to do it because you don't have enough permissions. Either you will get the permissions or you'll need to ask someone else to do it (an ETL developer).

Good luck with that, I feel your pain.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 8

Deries (1/28/2014)
Hi Luis,
I read your post and tried that for testing. I being a developer it didnt allow me to do insert bulk load because of the rights issue. Thats why i asked for some help to upload the csv file to the existing table.
i am trying to upload the csv file from my local drive to the server.
Msg 4834, Level 16, State 4, Line 12
You do not have permission to use the bulk load statement.

What privs do YOU have on the SQL Server itself?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)