Not sure how to import CSV from inside a query.

[sparkbyte]

I am not sure how to do this but I think it should be possible if using a bit of VBS code inside the script.

What I do now is this.

I have a DTS package (SQL 2k) that imports a CSV and an excel spreadsheet into tables and then I have a SQL script that does a join and select distinct to provide me a list of users and their default printers across our servers.

What I would like to do is eliminate the need for the DTS package and just use the script.

Any ideas?


Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

 

[SQLDenis]

Lookup OPENROWSET in BOL

example

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\SomeFiles\;','select  * from
SomeFile.csv')

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[jrprogr]

Denise,

Does the user who execute the openrowset script should have bulk insert permission also...
If NO how we do specify in the script to read the all files dynamically from the directory instead of one file(somefile) in the select script..

 

[philhege]

Using T-SQL exclusively, you could read the directory contents into a temporary table, then spin through that table and plug the file name into your OPENROWSET statement, then issue the statement in EXEC().

You could do the same in VBScript, but I'm not sure how you've set up the execution. Is it a scheduled job? Does the user have all necessary permissions, etc., etc.?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.