Need to import data from a CSV file into SQL2005 the easiest way.

Question

Post reply

Need to import data from a CSV file into SQL2005 the easiest way.

Berl

SSC Veteran

Points: 297
More actions
September 30, 2009 at 8:43 am

#135451

I am getting very frustrated trying to resolve all of the "issues" with Openrowset, Bulk Insert and MDSASQL etc. All I need to do is to import all of the information in a CSV that resides on the C: of the SQL2005 Server in question into a database.table on the same SQL2005 server. At this point I just need a concise solution.

Viewing 6 posts - 1 through 5 (of 5 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

The easiest way is to right click on the database then click on Tasks.. then Import data.. and then follow the wizard..

Berl SSC Veteran Points: 297 More actions · Answer 2

Please excuse me, but I meant to mention that I am trying to do this from a stored procedure. This is to be part of web application where the user won't have any access to the servers.

GSquared SSC Guru Points: 260824 More actions · Answer 3

In my experience, OpenRowset is the easiest way to do that.

What issues are you having with that?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Berl SSC Veteran Points: 297 More actions · Answer 4

Ironically, I have continued working on the issue and made a couple of tweeks and got it to work. But to answer you question my original code was:

INSERT INTO ExistingTableName

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}','SELECT * FROM C:\Temp\DummyCSVFile.csv')

The result from this was an error because the Linked server was null.

However the new code, which at this point in testing is working perfectly is:

SELECT * INTO NewTableName FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}','SELECT * FROM C:\Temp\DummyCSVFile.csv')

I will continue working away until I am sure everything works. Thanks to everyone being available.:-)

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

Berl -384058 (9/30/2009)
Ironically, I have continued working on the issue and made a couple of tweeks and got it to work. But to answer you question my original code was:
INSERT INTO ExistingTableName
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}','SELECT * FROM C:\Temp\DummyCSVFile.csv')
The result from this was an error because the Linked server was null.
However the new code, which at this point in testing is working perfectly is:
SELECT * INTO NewTableName FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}','SELECT * FROM C:\Temp\DummyCSVFile.csv')
I will continue working away until I am sure everything works. Thanks to everyone being available.:-)

What do you meant the "Linked server was null"? I see no difference between the two OPENROWSET commands and neither makes reference to a linked server.

--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)