shacky
1. now i'm using bulk insert command to read sales data form text file into posmaster table in databases. the problem is this bulk command only read from single text file. what command should i use if i want to read sales data from multiple text file. this is the sample of bulk insert command for single text file.
BULK INSERT posmaster
FROM 'c:\SALE\010407_S13101.txt'
WITH
(
FIRSTROW =1,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
2. what command should i use if i want the directory for each text file generate automatically when the sales text file transfer into SALE folder so that the user doesn't need to specify the directory manually. this is the sample of directory for text file.
c:\SALE\010407_S13101.txt
c:\SALE\010407_S13102.txt
c:\SALE\010407_S13103.txt
i want the program can call the directory first and then insert the sales data from text files into posmaster table in databases. this is the sample of sales text file :
010407_S13101.txt
1511950000008395A,10-04-200711:30:08.000,CLOSED,-0000000000060,00.00,+0000000000.00,00.00,+0000000000.00,-0000000000060
1511950000008395A,10-04-200711:30:09.000,CLOSED,+0000000000050,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000050
1511950000008395A,10/04/200711:30:10.000,CLOSED,+0000000000010,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000010
1511950000008396A,10-04-200711:30:35.000,CLOSED,+0000000000020,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000020
1511950000008396A,10-04-200711:30:35.000,CLOSED,+0000000000090,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000090
1511950000008396A,10-04-200711:30:35.000,CLOSED,+0000000000025,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000025
1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000010,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000010
1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000060,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000060
1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000090,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000090
1511950000008397A,10-04-200711:31:10.000,CLOSED,+0000000000025,00.00,+0000000000.00,00.00,+0000000000.00,+0000000000025
1511950000008397A,10-04-200711:31:10.000,CLOSED,-0000000000020,00.00,+0000000000.00,00.00,+0000000000.00,-0000000000020
010407_S13102.txt
1311010000012598A002,2007-04-01:13:36.000,CLOSED,-0000000005.00,00.00,+0000000000.00,00.00,+0000000000.00,-0000000005.00
after each text file sucessfully insert into posmaster table in databases, i need the file that hold the directory deleted.
Re: SQL Problems
Arnie Rowland
Here is the skeleton of how, I'll let you flesh it out...
Code Snippet
CREATE TABLE #FileListTable -- List of sql Files to run
(
RowID int IDENTITY
, FileName varchar(100)
, PassCount int DEFAULT 0
)
DECLARE
@FileToRun varchar(100),
@SQLCmd varchar(1000),
@bcpCmd varchar(1000),
@CurrentRow int,
@TotalRows int
SELECT
@FileToRun = '',
@SQLCmd = 'dir c:\Sale\*.* /b ',
@CurrentRow = 1
-- Use BULK INSERT to load the FileList
INSERT INTO #FileListTable
EXECUTE master.dbo.xp_cmdshell @SQLCmd
DELETE
FROM #FileListTable
WHERE ( FolderName IS NULL
OR FolderName = 'Output'
)
SELECT @TotalRows = count(1)
FROM #FileListTable
WHILE ( @CurrentRow <= @TotalRows )
BEGIN
SELECT @FileToRun = FileName
FROM #FileListTable
WHERE RowID = @CurrentRow
SELECT @BCPCmd = 'BULK INSERT PosMaster FROM c:\Sale\' + @FileToRun +
'WITH ( FirstRow = 1, FieldTerminator = '','', + RowTerminator = ''\n'')'
EXECUTE master.dbo.xp_cmdshell @BCPCmd
SET @CurrentRow = ( @CurrentRow + 1 )
END
Re: SQL Problems
shacky
here the error...
Msg 213, Level 16, State 7, Procedure xp_cmdshell, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
this is how i create the posmaster table:
If exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[posmaster]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[posmaster]
GO
CREATE TABLE [dbo].[posmaster] (
[sale_id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[sale_datetime] [datetime] NOT NULL ,
[sale_status] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sub_total] [money] NULL ,
[discount_percent] [float] NULL ,
[discount_amount] [money] NULL ,
[tax_percent] [float] NULL ,
[tax_amount] [money] NULL ,
[grand_total] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[posmaster] ADD
CONSTRAINT [PK_posmaster] PRIMARY KEY CLUSTERED
(
[sale_id]
) ON [PRIMARY]
GO
here the error...
Msg 213, Level 16, State 7, Procedure xp_cmdshell, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
this is how i create the posmaster table:
If exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[posmaster]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[posmaster]
GO
CREATE TABLE [dbo].[posmaster] (
[sale_id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[sale_datetime] [datetime] NOT NULL ,
[sale_status] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sub_total] [money] NULL ,
[discount_percent] [float] NULL ,
[discount_amount] [money] NULL ,
[tax_percent] [float] NULL ,
[tax_amount] [money] NULL ,
[grand_total] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[posmaster] ADD
CONSTRAINT [PK_posmaster] PRIMARY KEY CLUSTERED
(
[sale_id]
) ON [PRIMARY]
GO
Re: SQL Problems
Arnie Rowland
The error indicates that your table, PosMaster, does not have the same number of columns as the data file, or the datatypes do not 'match' for one or more columns.
Carefully check your datafile.
Re: SQL Problems
shacky
I am having following error while execute the command given, can you pls assist Thank you.
'BULK' is not recognized as an internal or external command, operable program or batch file.
I am having following error while execute the command given, can you pls assist Thank you.
'BULK' is not recognized as an internal or external command, operable program or batch file.
Re: SQL Problems
shacky
the list of text file directory/path successfully inserted into FileList table but the sales data in text file can't be insert into posmaster table. i think there are error with bulk insert command. here the coding :
CREATE TABLE FileList -- List of sql Files to run
(
RowID int IDENTITY,
FileName varchar(100)
)
DECLARE
@FileToRun varchar(100),
@SQLCmd varchar(1000),
@bcpCmd varchar(1000),
@CurrentRow int,
@TotalRows int
SELECT
@FileToRun = '',
@SQLCmd = 'dir c:\SALE\*.txt /b ',
@CurrentRow = 1
-- Use BULK INSERT to load the FileList
INSERT INTO FileList
EXECUTE master.dbo.xp_cmdshell @SQLCmd
DELETE
FROM FileList
WHERE ( FileName IS NULL
OR FileName = 'output'
)
SELECT @TotalRows = count(1)
FROM FileList
WHILE ( @CurrentRow <= @TotalRows )
BEGIN
SELECT @FileToRun = FileName
FROM FileList
WHERE RowID = @CurrentRow
SELECT @bcpCmd = 'BULK INSERT posmaster FROM c:\SALE\' + @FileToRun +
' WITH ( FirstRow = 1, FieldTerminator = '','', + RowTerminator = ''\n'')'
PRINT @bcpCmd
EXECUTE master.dbo.xp_cmdshell @bcpCmd
SET @CurrentRow = ( @CurrentRow + 1 )
END
-- Empty temporary table
TRUNCATE TABLE FileList
GO
the list of text file directory/path successfully inserted into FileList table but the sales data in text file can't be insert into posmaster table. i think there are error with bulk insert command. here the coding :
CREATE TABLE FileList -- List of sql Files to run
(
RowID int IDENTITY,
FileName varchar(100)
)
DECLARE
@FileToRun varchar(100),
@SQLCmd varchar(1000),
@bcpCmd varchar(1000),
@CurrentRow int,
@TotalRows int
SELECT
@FileToRun = '',
@SQLCmd = 'dir c:\SALE\*.txt /b ',
@CurrentRow = 1
-- Use BULK INSERT to load the FileList
INSERT INTO FileList
EXECUTE master.dbo.xp_cmdshell @SQLCmd
DELETE
FROM FileList
WHERE ( FileName IS NULL
OR FileName = 'output'
)
SELECT @TotalRows = count(1)
FROM FileList
WHILE ( @CurrentRow <= @TotalRows )
BEGIN
SELECT @FileToRun = FileName
FROM FileList
WHERE RowID = @CurrentRow
SELECT @bcpCmd = 'BULK INSERT posmaster FROM c:\SALE\' + @FileToRun +
' WITH ( FirstRow = 1, FieldTerminator = '','', + RowTerminator = ''\n'')'
PRINT @bcpCmd
EXECUTE master.dbo.xp_cmdshell @bcpCmd
SET @CurrentRow = ( @CurrentRow + 1 )
END
-- Empty temporary table
TRUNCATE TABLE FileList
GO
Re: SQL Problems
shacky
here the correct solution...
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) = 'c:\SALE\' ,
@FileNameMask varchar(1000) = '*.txt'
AS
set nocount on
--declare @ImportDate datetime
-- select @ImportDate = getdate()
declare @FileName varchar(1000) ,
@File varchar(1000)
declare @cmd varchar(2000)
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 + ' posmaster'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (FIELDTERMINATOR='','''
select @cmd = @cmd + ',ROWTERMINATOR = ''\n'')'
-- import the data
exec (@cmd)
-- remove filename just imported
delete #Dir where s = @FileName
exec master..xp_cmdshell @cmd
end
--drop table ##Import
drop table #Dir
go
--exec ImportFiles 'c:\SALE\' , 'c:\SALE\Archive\' , '*.txt', 'MergeBCPData'
exec ImportFiles
here the correct solution...
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) = 'c:\SALE\' ,
@FileNameMask varchar(1000) = '*.txt'
AS
set nocount on
--declare @ImportDate datetime
-- select @ImportDate = getdate()
declare @FileName varchar(1000) ,
@File varchar(1000)
declare @cmd varchar(2000)
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 + ' posmaster'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (FIELDTERMINATOR='','''
select @cmd = @cmd + ',ROWTERMINATOR = ''\n'')'
-- import the data
exec (@cmd)
-- remove filename just imported
delete #Dir where s = @FileName
exec master..xp_cmdshell @cmd
end
--drop table ##Import
drop table #Dir
go
--exec ImportFiles 'c:\SALE\' , 'c:\SALE\Archive\' , '*.txt', 'MergeBCPData'
exec ImportFiles
