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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy