January 7, 2022 at 8:05 pm
The kind users of this forum have helped me develop a stored procedure that imports CSV files in a folder using Bulk Insert. The files have some extra lines at the end so we had to create a process to count the lines and then set that as the LASTROW. Howver, in working with it I realize that I need to have an import date so I can differentiate the data from day to day.
I tried to just add a column to the table with a default value of the current day but it errors out with because there is no date (Column 14) in the CSV file
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 14 (Import_Date)
So I tried to add an Alter Table in my stored procedure but that didnt work
So I created a new stored procedure that does an Alter Table and then Appends it to another table. The problem with that is I will have to delete the table and recreate it every time or else the import will not work and I would have to call three stored procedures to make it work.
Is there any way to add the date field on the fly, I will post my stored procedure below so you can see (Thanks to Frederic_Fonseca)
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
--
set xact_abort on;
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"'
Exec master..xp_cmdshell @moveProcessed
End
Delete from TestTracingImportSP where State is Null
End
January 7, 2022 at 8:08 pm
I tried to just add a column to the table with a default value of the current day but it errors out with because there is no date (Column 14) in the CSV file
You could always create a view of the table that doesn't include the new data column and bulk insert into that
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 7, 2022 at 8:13 pm
Thank you for the reply, I forgot to mention that i am very new to SQL server. I looked up how to create a view but I am not sure what I would do. I can bulk insert into the current table as long as it does not have the Import_Date field in it. Are you saying use a view to bulk import into a table without the Import_Date and then append to the live table?
Or maybe you are saying that since a view is kind of like a query I can create a view and add the Import_Date field to it and then use that view to update the main table using and Append / Insert Into statement??
January 7, 2022 at 11:12 pm
Ok, i created a view and was able to import the data into it using my stored procedure.
Next I created an Insert Into statement
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 ImportWitoutDate
That properly went into my final table.
I then Added the insert statement into my stored procedure and now it appears it is all working fine. I will test more thoroughly on Monday just to make sure and update the thread. Thanks for the lead on the View
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply