//
you're reading...
Database, IT-Center, SQL Server

Import CSV File Using Stored Procedure SQL Server 2005

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[import_csv_dec]
@user_created varchar(40),
@file_name varchar(200)
AS

DECLARE @Err INTEGER
DECLARE @ErrMsg VARCHAR(200)

RETRY:
BEGIN TRY
--BEGIN TRANSACTION
declare @jum int

set @jum = (select count(*) from tbl_ft100_dec where file_name=@file_name)
if @jum<1
begin
exec ('delete from tbl_ft100')
exec ('delete from tbl_ft100_dec where file_name='''+@file_name+'''')
exec ('BULK insert tbl_ft100 from ''' + @file_name + ''' with (
FIRSTROW = 2,
MAXERRORS = 0,
fieldterminator = '';'' ,
rowterminator =''\n''
)')

-- 1/8/2008 11:27:00 AM

exec ('insert into tbl_ft100_dec select GETDATE(),'''+@user_created+''','''+@file_name+''', convert(datetime,time_csv,103),
convert(decimal(18,2),Analog_PV_AT101_PV),
convert(decimal(18,2),Analog_PV_AT501_PV),
convert(decimal(18,2),AT501_H2S_AT501_H2S_PV),
convert(decimal(18,2),Analog_PV_FT100_PV),
convert(decimal(18,2),Analog_PV_FT101_PV),
convert(decimal(18,2),Analog_PV_FT103_PV),
convert(decimal(18,2),FT103_501_Real_FT103_M3_H),
convert(decimal(18,2),Analog_PV_FT501_PV),
convert(decimal(18,2),FT103_501_Real_FT501_M3_H),
convert(decimal(18,2),Analog_PV_LT101_PV),
convert(decimal(18,2),Analog_PV_LT102_PV),
convert(decimal(18,2),Analog_PV_LT103_PV),
convert(decimal(18,2),Analog_PV_PHT101_PV),
convert(decimal(18,2),Analog_PV_PHT102_PV),
convert(decimal(18,2),Analog_PV_PHT501_PV),
convert(decimal(18,2),Analog_PV_PT103_PV),
convert(decimal(18,2),Analog_PV_PT502_PV),
convert(decimal(18,2),Analog_PV_PT503_PV),
convert(decimal(18,2),Analog_PV_TT101_PV),
convert(decimal(18,2),Analog_PV_TT102_PV),
convert(decimal(18,2),Analog_PV_TT103_PV),
convert(decimal(18,2),Analog_PV_TT502_PV),
convert(decimal(18,2),Totaliser_FT100_24HTot),
convert(decimal(18,2),Totaliser_FT100_24T),
convert(decimal(18,2),Totaliser_FT100_Tot),
convert(decimal(18,2),Totaliser_FT101_24HTot),
convert(decimal(18,2),Totaliser_FT101_24T),
convert(decimal(18,2),Totaliser_FT101_Tot),
convert(decimal(18,2),Totaliser_FT103_24HTot),
convert(decimal(18,2),Totaliser_FT103_24T),
convert(decimal(18,2),Totaliser_FT103_Tot),
convert(decimal(18,2),Totaliser_FT501_24HTot),
convert(decimal(18,2),Totaliser_FT501_24T),
convert(decimal(18,2),Totaliser_FT501_Tot),
convert(decimal(18,2),G501A_B_GCV501A_PowPV),
convert(decimal(18,2),G501A_B_GCV501B_PowPV)
from   tbl_ft100')
exec ('insert into tbl_filename values('''+@file_name+''',GETDATE())')
/*exec ('drop table '+@tbl_name)*/

end

--COMMIT TRANSACTION
END TRY
BEGIN CATCH

SET @Err = @@ERROR

if @Err <> 0
--ROLLBACK TRANSACTION
print @Err

END CATCH

Important you must handle in this script :
exec (‘BULK insert tbl_ft100 from ”’ + @file_name + ”’ with (
FIRSTROW = 2,
MAXERRORS = 0,
fieldterminator = ”;” ,
rowterminator =”\n”
)’)

_________________________________________________________________________________
See Also :
*) http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
*) http://midnightprogrammer.net/post/Import-CSV-File-Into-SQL-Server-Using-Bulk-Insert.aspx

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: