February 16, 2022 at 3:42 pm
I have a stored procedure that imports a group of csv files. The first field is called Initial and it always has 4 characters but is set to VarChar (50) because there are some blank lines at the end of the file and it will fail the import with a truncate error if I dont do that. I have tried VarChar(4) and nchar(4) but they give the import error.
How can I get this to import without the blank space at the beginning? I will post the entire stored procedure for your review.
Begin
-- these 2 should always be set in ALL sp's
-- set nocount on; - do not output rowcounts
-- set xact_abort on; --- if it fails on one step it does not continue executing
--
--This will clear the FinalTracingImportSP table so that the access append query won't get more than one day
Truncate table FinalTracingImportSp
set xact_abort on;
SET DATEFORMAT DMY;
Declare @Query varchar(1000), @moveProcessed varchar (1000)
Create TABLE #x(name varchar(200))
set @Query = 'master.dbo.xp_cmdshell "dir '+@filepath+@pattern+' /b"' -- possibly change this so that the path+pattern are within doublequotes - in case in future the path contains spaces
insert #x exec(@query)
Delete from #x where name is Null
Select identity(int,1,1) as ID, name into #y from #x
drop table #x
if object_id('tempdb..#staging') is not null
drop table #staging
create table #staging
(
record varchar(8000) -- change as required to be a bit over what max can be
)
Declare @max1 int, @count1 int, @filename varchar (200), @validrows int
set @max1 = (select max(ID) from #y)
set @count1 = 0
While @count1 <@max1
BEGIN
SET @count1 = @count1 +1
Set @filename = (select name from #y where [id] = @count1)
print 'processing filename ' + @filename
-- truncate staging table and load file on to it so we can count non empty lines
-- note that this will still fail if there is an empty line on the middle of the file
truncate table #staging
set @query = 'BULK INSERT #staging FROM "'+@filepath+@filename+'" WITH (FIRSTROW=1, ROWTERMINATOR=''\r'')'
Exec (@query)
set @validrows = 0
select @validrows = count(*)
from #staging
where record <> '' AND record <> 0x0A
print 'processing filename ' + @filename + ' row count = ' + convert(varchar(20), coalesce(@validrows, -1))
if @validrows > 1 -- only load onto main table if there are valid records on the file
begin
set @query = 'BULK INSERT '+@tablename+' FROM "'+@filepath+@filename+'" WITH (FIELDTERMINATOR = '','',FIRSTROW=2,LASTROW=' + convert(varchar(20), @validrows) + ',ROWTERMINATOR=''\r'')'
Exec (@query)
end
insert into Tracingfiles_logtable(filename) select @filename
--set @moveProcessed = 'Move "'+@filepath+@filename+'" D:\TestTracingImport\Archive\"'+SUBSTRING(@filename, 1, LEN(@filename) - 4)+'_'+cast(Format(getdate(), 'yyyyMMddHHmmss') as varchar)+'.csv"'
set @moveProcessed = 'Move "'+@filepath+@filename+'" D:\TestTracingImport\Archive\"'+cast(Format(getdate(), 'yyyyMMdd') as varchar)+'_'++SUBSTRING(@filename, 1, LEN(@filename) - 4)+'.csv"'
Exec master..xp_cmdshell @moveProcessed
End
Delete from TestTracingImportSP where State is Null
--Alter table TestTracingImportSP ADD [ImportDate] DATETIME NOT NULL DEFAULT GETDATE()
Insert INTO FinalTracingImportSP
Select Initial, Number, [Location City], State, Month, Day, Time, [L or E], Event, [Train Id], [Destination City], DState, [Reporting RR], GetDate() as ImportDate
FROM ImportWithoutDate
--This will clear the TestTracingImportSp table so that the access append query won't get more than one day
Truncate table TestTracingImportSp
End
February 17, 2022 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply