Stored Proc for csv import - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Stored Proc for csv import

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-06-13 : 09:59:09

GUys,

I have the following stored proc which I use to export the data to excel spreadsheet.

I need to develop a stored proc to import from 'csv' file, can anyone help me to do so.

CREATE procedure spExport (@date1 varchar(30), @date2 varchar(30)) as
set nocount on
begin
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\ai-dc1\Project Management I\ADC Customer Central\Cook County IL COK\Excel Export Files\Output.xls;',
'SELECT alpha_doc_num, index_code_id, recorded_date, executiondate, amount FROM [DocumentDetails$]')
select alpha_doc_num, index_code_id, recorded_date, executiondate, amount
from vdocumentsource where recorded_date between @date1 and @date2
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\ai-dc1\Project Management I\ADC Customer Central\Cook County IL COK\Excel Export Files\Output.xls;',
'SELECT alpha_doc_num, name_last, party_type FROM [GrantorGrantees$]')
select alpha_doc_num, name_last, party_type
from ventitysource where seq_key in (select seq_key from vdocumentsource where recorded_date between @date1 and @date2)
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\ai-dc1\Project Management I\ADC Customer Central\Cook County IL COK\Excel Export Files\Output.xls;',
'SELECT alpha_doc_num, prop_id FROM [LegalDescription$]') select alpha_doc_num, prop_id
from vlegalsource where seq_key in (select seq_key from vdocumentsource where recorded_date between @date1 and @date2)
end
set nocount off
GO

Also the folder structure has many csv files, I want to be able to import all the csv files. Is there any way to write to a log within the stored proc.

Any suggestions/inputs would help.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-13 : 10:05:29

Why do you want to do this from Stored Proc? This can be better handled by creating DTS package.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-13 : 10:31:31

Have a look at
http://www.nigelrivett.net/SQLTsql/s_ProcessAllFilesInDir.html

It will call an sp for each file in a folder passing the filename and path and using a file mask.
You should move the file to an archive folder after processing.

This one does it for text files and calls an SP to just process the imported data
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

You could probably use bits of each.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources