February 16, 2022 at 3:43 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 16, 2022 at 7:21 pm
The thread is a duplicate. Regarding just the first 4 lines of the procedure
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
Line 1: BEGIN/END is optional so it could be removed
Line 2: False comment (see Line 4 below)
Line 3: Good comment however not implemented in the code.
Line 4: False comment. SET XACT_ABORT ON does not interrupt the flow of control. What it does is tell the DB Engine to automatically rollback uncommittable transactions all the way to the BEGIN TRANSACTION statement. So if there are multiple DML statements inside of an explicit transaction then SET XACT_ABORT ON essentially automatically "cleans up" good and bad data manipulation across multiple INSERT/UPDATE/DELETE statements. There is a working example in the Docs (cut/paste'ed below) which demonstrate the behavior. Because your procedure doesn't declare any explicit transaction SET XACT_ABORT ON doesn't do anything
Not my code (pasted from MS Docs)
IF OBJECT_ID(N't2', N'U') IS NOT NULL
DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
DROP TABLE t1;
GO
CREATE TABLE t1
(a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
(a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
-- SELECT shows only keys 1 and 3 added.
-- Key 2 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.
-- Key 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
SELECT *
FROM t2;
GO
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 16, 2022 at 8:55 pm
Sorry, I don't know how the duplicate got created and could not find how to delete the post. is there a sticky post or something that says how to delete a duplicate post?
Also, Sorry, new to Sql Server, I am a Linux Admin. I will do some cleanup on the comments. This has been a work in progress. Any idea why the stored procedure works fine but adds one blank space in front of every value in the first column?
February 16, 2022 at 9:22 pm
Perhaps the spaces are present in the input data. One quick way to get past the issue could be to import into a staging table and then use a query to insert into a final table using the TRIM() function on the column with leading space. Not very efficient tho
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 16, 2022 at 10:25 pm
I already checked the source files and they do not have a leading space. I am already doing as you suggested on the access side but eventually I dont want to have to use Access for any of this processing. I currently import it into a table and then use a view to append it to another table.
I tried to add trim to the view TRIM(Initial) but when I do i get the error:
SELECT TRIM(Initial) AS Initial, Number, [Location City], State, Month, Day, Time, [L or E], Event, [Train ID], [Destination City], DState, [Reporting RR]
FROM dbo.TestTracingImportSP
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'ImportWithoutDate' failed because it contains a derived or constant field.
I also changed it to TRIM(Initial) AS Initial1 and updated the stored procedure but it gave me the same error- also tried LTRIM
February 17, 2022 at 2:12 pm
Maybe try it without the VIEW
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 24, 2022 at 2:02 pm
This was removed by the editor as SPAM
March 2, 2022 at 1:43 am
This was removed by the editor as SPAM
March 2, 2022 at 2:49 am
Why do you redo BULK INSERT?
You have all the data in #staging already.
Delete non-valid records and parse the data using a SCV_splitter, for example.
And did you check the content of #staging?
You may find all the answers there.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply