stored proc for csv import

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  June 13, 2007 at 8:00 am

  #62514

  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

• Sachin Samuel-286731

  Right there with Babe

  Points: 778

  More actions

  June 13, 2007 at 8:35 am

  #712632

  Hello there,

  This can easily be done using DTS. Check for DTS in books online. Create a package and execute the dts package using DTSrun.

  Thanks

  Sachin

• Carlos Urbina

  SSCommitted

  Points: 1850

  More actions

  June 14, 2007 at 8:36 am

  #712955

  Another way:

  insert Sheet1A (UW,TYP,D01,D02,D03,D04,D05,D06,D07,D08,D09,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,TOT)

  SELECT *

  FROM  OPENROWSET ( 'MICROSOFT.JET.OLEDB.4.0',

     'EXCEL 8.0;HDR=NO;DATABASE=\\SERVERNAME\Reports\UWCounts\UWCOUNTS200608.xls',

     'SELECT * FROM  [SHEET1$]')