import multiple text files & create single table

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

Import/Export (DTS) and Replication (2000)

import multiple text files & create single table

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-03-06 : 10:29:17

My question concerns the very end of this post, the select statement...

-- insert data to production table
insert Stock_Quote_Data
(
Symbol ,
[Date] ,
[Open] ,
High ,
[Low] ,
[Close] ,
Volume
)
select
Symbol = substring(s,1,3),
[Date] = convert(datetime,substring(s,4,8)),
[Open] = convert(money,substring(s,12,4)),
High = convert(money,substring(s,16,4)),
[Low] = convert(money,substring(s,20,4)),
[Close] = convert(money,substring(s,24,4)),
Volume = convert(int,substring(s,32,8))

from ##Import

go

I'm trying to insert stock quote data from numerous csv text files having various names into a single sql table via the steps outlined below.

This procedure was originally written to insert data that was not csv, so I've tried to convert it for csv.

How would the last select statement at the bottom of this post have to be written so that the data is inserted properly?

Also, I'm not sure about how to hadle the file names...they will vary from 1 to 5 characters, eg. A.txt to XRLNX.txt
Will *.txt work ok?

Thanks

PS Is there a way similar to this procedure that appends data to this sql table from the same (multiple) csv files or is it just easier to import the entire csv file each day that the csv files are updated?

/*

STEP 1)----------------------------------------------------

Create the directories D:\My Investment Databases\Stock_Quotes\Quote_Data\Process\Archive

STEP 2)----------------------------------------------------

Create these text files in D:\My Investment Databases\Stock_Quotes\Quote_Data\Process
*.txt
AA,02/03/1989,63.88,64.12,62.5,63.5,471300
AA,02/06/1989,63.5,63.75,62.63,62.88,199700
AA,02/07/1989,62.88,63.88,62.63,63.88,404200

*.txt
AA,02/03/1989,63.88,64.12,62.5,63.5,471300
AA,02/06/1989,63.5,63.75,62.63,62.88,199700
AA,02/07/1989,62.88,63.88,62.63,63.88,404200

STEP 3)----------------------------------------------------

Create the table
create table Stock_Quote_Data
(
Symbol varchar(10),
[Date] datetime,
[Open] money,
High money,
[Low] money,
[Close] money,
Volume int
)

STEP 4)----------------------------------------------------

Now run the import
exec ImportFiles 'D:\My Investment Databases\Stock_Quotes\Quote_Data\Process\' , 'D:\My Investment Databases\Stock_Quotes\Quote_Data\Process\Archive\' , '*.txt', 'MergeStockQuoteData'

=========================================================================================================

You can now move the files back from the archive directory to the transfer directory and import again.
If this SP call is scheduled then it will import and archive any files that arrive in the transfer directory with the corect file mask.

Enhancements
The import should be logged to a table in ImportFiles
The filename should have the datetime appended to it when archived if you wish to be able to import files with the same name
The MergeData SP should log the number of records imported

*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ImportFiles]
GO

create procedure ImportFiles
@FilePath varchar(1000) = 'D:\My Investment Databases\Stock_Quotes\Quote_Data\Process\' ,
@ArchivePath varchar(1000) = 'D:\My Investment Databases\Stock_Quotes\Quote_Data\Process\Archive\' ,
@FileNameMask varchar(1000) = '*.txt' ,
@MergeProc varchar(128) = 'MergeStockQuoteData'

AS

set nocount on

declare @ImportDate datetime
select @ImportDate = getdate()

declare @FileName varchar(1000) ,
@File varchar(1000)

declare @cmd varchar(2000)

create table ##Import (s varchar(8000))
create table #Dir (s varchar(8000))

/*****************************************************************/
-- Import file
/*****************************************************************/
select @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'
while exists (select * from #Dir)
begin
select @FileName = min(s) from #Dir
select @File = @FilePath + @FileName

select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (FIELDTERMINATOR='','''
select @cmd = @cmd + ',ROWTERMINATOR = ''' + char(10) + ''')'

truncate table ##Import

-- import the data
exec (@cmd)

-- remove filename just imported
delete #Dir where s = @FileName

exec @MergeProc

-- Archive the file
select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileName
exec master..xp_cmdshell @cmd
end

drop table ##Import
drop table #Dir
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MergeStockQuoteData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MergeStockQuoteData]
GO

create procedure MergeStockQuoteData

AS
set nocount on

-- insert data to production table
insert Stock_Quote_Data
(
Symbol ,
[Date] ,
[Open] ,
High ,
[Low] ,
[Close] ,
Volume
)
select
Symbol = substring(s,1,3),
[Date] = convert(datetime,substring(s,4,8)),
[Open] = convert(money,substring(s,12,4)),
High = convert(money,substring(s,16,4)),
[Low] = convert(money,substring(s,20,4)),
[Close] = convert(money,substring(s,24,4)),
Volume = convert(int,substring(s,32,8))

from ##Import

go

...something like this maybe??? ###########################

create procedure MergeStockQuoteData

AS
set nocount on

-- insert data to production table
insert Stock_Quote_Data
(
Symbol ,
Date ,
[Open] ,
High ,
[Low] ,
[Close] ,
Volume
)
select
Symbol = SUBSTRING(Symbol,2,DATALENGTH(Symbol)-1)
[Date] = convert(datetime,SUBSTRING(Date,2,DATALENGTH(Date)-1)),
[Open] = convert(money,SUBSTRING([Open],2,DATALENGTH([Open])-1)),
High = convert(money,SUBSTRING(High,2,DATALENGTH(High)-1)),
[Low] = convert(money,SUBSTRING([Low],2,DATALENGTH([Low])-1)),
[Close] = convert(money,SUBSTRING([Close],2,DATALENGTH([Close])-1)),
Volume = convert(int,SUBSTRING(Volume,2,DATALENGTH(Volume)-1))

from ##Import

go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources