|
Subject:
|
Importing CSV file into SQL from Web
|
|
Posted By:
|
daniel
|
Post Date:
|
11/14/2006 11:39:44 AM
|
Hi..
Source File: http://www.webname.com/files/log.csv
Destination: SQL Server to process the rows
I am trying to get the above file into SQL server to query the data. The above file takes a username and password.
Do I need to create a linked server?
The only way i can think of at present is to copy the file onto the server(by FTP) and query it something like this.
select * from OpenRowset('MSDASQL'
,'driver={Microsoft Text Driver (*.txt; *.csv)}
;DefaultDir=C:\;'
,'select * from log.csv')
Any help or pointers appreciated
Dan
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/14/2006 8:29:46 PM
|
Copy the file to a place where SQL Server can see it... then, do a BULK INSERT. See Books OnLine for details.
--Jeff Moden
|
|
Reply By:
|
daniel
|
Reply Date:
|
11/16/2006 4:41:26 AM
|
thanks for the reply
regarding the below I would like to declare a variable of the location, namely have the path passed in at runtime.
select * from OpenRowset('MSDASQL'
,'driver={Microsoft Text Driver (*.txt; *.csv)}
;DefaultDir=C:\;', 'select * from log.csv')
/--------------This is what I am trying
declare @file varchar(100)
set @file = 'C:\'
select * from OpenRowset('MSDASQL'
,'driver={Microsoft Text Driver (*.txt; *.csv)}
;DefaultDir=@file;'
,'select * from log.csv')
this does not seem to work, any help appreciated
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/20/2006 12:23:54 AM
|
Sorry... I don't use OpenRowSet... it's too slow for me... I just copy the file to an import staging area and do a nasty fast bulk insert. What the heck... disk space is cheap and if I need to reload for some reason... it's already local.
--Jeff Moden
|
|
Reply By:
|
robprell
|
Reply Date:
|
12/14/2006 3:46:24 PM
|
Check where the parameter "c:" is. Are you thinking this is your local client c: drive or the server c: drive. Your command is executed relative to the server you established a connected to. Make sure your on the right "C:" drive/server. You may be pointing to the wrong place and thus not working.
|
|
Reply By:
|
daniel
|
Reply Date:
|
12/14/2006 6:25:14 PM
|
thanks
good point, will have a look
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
12/15/2006 8:48:36 AM
|
Bulk Insert! http://p2p.wrox.com/topic.asp?TOPIC_ID=53130
--Jeff Moden
|
