Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kyle Burrow
Starting Member
1 Post |
 Posted - 2007-04-18 : 11:22:47
|
| i am moving a file for a store from our ftp server to the sql. I want to use buld insert to insert the data. Everything is good to this point. Now I need to do this with a csv formatted file that does not include the store's company number. This would be ok, but I have 62 stores. Can I add the store number to the db as i bulk insert?here is the sproc i have come up with. DECLARE @intReturnCode int -- Error Messages DECLARE @strFTP_SVR_NA char(20) -- FTP Server Name DECLARE @strFTP_DIR_NA varchar(200) -- First part of FTP Directory DECLARE @strFTP_FIL_NA varchar(50) -- FTP file name DECLARE @strFTP_FIL_EXT char(4) -- FTP file extension DECLARE @strFTP_ID char(20) -- Login ID for FTP Server DECLARE @strFTP_PW char(20) -- Password for FTP Server DECLARE @strLCL_DIR_NA varchar(200) -- Local Directory Name on the SQL Server DECLARE @strLCL_DIR_NAA varchar(200) -- Local Directory for the Loc_Sys_Nr DECLARE @strDate char(8) -- Date in string format DECLARE @strString varchar(200) -- Used for the CMD Shell directory checking to see if the directory is there. DECLARE @intResult int -- Runs the cmd shell dir command DECLARE @strFTP_URL_ADR VARCHAR(500) DECLARE @strFTP_DIR_NA2 char(50)--Dynamic part of address, loc_sys_nr DECLARE @X INT -- Counter DECLARE @Y INT -- Counting from 1 to @X incremented by 1 SET @strFTP_SVR_NA = '153.2.133.183' SET @strFTP_DIR_NA = 'SECURED\UPSAPPS\DENG0183\TEXASCLEANWATER\' SET @strFTP_FIL_NA = 'DBASE.CSV' -- USE TO MAKE SURE ALL DATA IS DOWN, THEN CHANGE --SET @strFTP_FIL_NA = 'DAILY.CSV' --ONCE ORIGINAL DOWNLAOD IS MADE, USE THIS SET @strFTP_ID = 'APPS_DENG0183_TCW' SET @strFTP_PW = '0183_TCW' SET @strLCL_DIR_NAA = 'D:\UPSAPPS\DENG0183\TEXASCLEANWATER' SET @strString = 'del ' + rtrim(@strLCL_DIR_NA) + '\*.* /S/Q' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @X = (SELECT count(DISTINCT(LOC_SYS_NR)) FROM TTCWPCS) --gets record count or slics SET @Y = '0' --Base for counting up to @X WHILE @Y <> @X --Start of Loop BEGIN SET @Y = @Y + 1 SET @strFTP_DIR_NA2 =(SELECT LOC_SYS_NR FROM TTCWPCS WHERE UNQ_ID_NR=@Y)--Gets the slic directory SET @strFTP_URL_ADR = @strFTP_DIR_NA+rtrim(@strFTP_DIR_NA2)+'\'+@strFTP_FIL_NA --combines the ftp directory and the slci directory then adds the file name. SET @strLCL_DIR_NA = @strLCL_DIR_NAA+'\'+ rtrim(@strFTP_DIR_NA2)+'\' SET @strString = 'dir ' + rtrim(@strLCL_DIR_NA) EXEC @intResult = master..xp_cmdshell @strString, no_output IF (@intResult <> 0) BEGIN SET @strString = 'md ' + rtrim(@strLCL_DIR_NA) EXEC @intResult = master..xp_cmdshell @strString, no_output END----Create and run FTP script SET @strString = 'echo open '+rtrim(@strFTP_SVR_NA)+' > '+rtrim(@strFTP_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo user '+rtrim(@strFTP_ID)+' '+rtrim(@strFTP_PW)+'>> '+rtrim(@strFTP_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo hash >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo cd '+rtrim(@strFTP_DIR_NA)+' >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo lcd '+rtrim(@strLCL_DIR_NA)+' >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo get '+rtrim(@strFTP_FIL_NA)+' >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo bye >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'ftp -v -n -s:'+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @intReturnCode = @intResult IF (@intReturnCode<>0) BEGIN PRINT('FTP Error') RAISERROR('FTP Error',16,1) END SET @strString = 'echo user '+rtrim(@strFTP_ID)+' '+rtrim(@strFTP_PW)+'>> '+rtrim(@strFTP_DIR_NA) + rtrim(@strFTP_DIR_NA2)+'\script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo hash >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo cd '+rtrim(@strFTP_DIR_NA) + rtrim(@strFTP_DIR_NA2)+' >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo lcd '+rtrim(@strLCL_DIR_NA)+' >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo get '+rtrim(@strFTP_DIR_NA) + rtrim(@strFTP_DIR_NA2)+' >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'echo bye >> '+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @strString = 'ftp -v -n -s:'+rtrim(@strLCL_DIR_NA)+'script.ftp' EXEC @intResult = master..xp_cmdshell @strString, no_output SET @intReturnCode = @intResult IF (@intReturnCode<>0) BEGIN PRINT('FTP Error') RAISERROR ('FTP Error',16,1) END SET @strString = 'dir '+rtrim(@strLCL_DIR_NA)+rtrim(@strFTP_FIL_NA)+'' EXEC @intResult = master..xp_cmdshell @strString, no_output IF (@intResult<>0) BEGIN PRINT('File '+rtrim(@strFTP_FIL_NA)+' not retrieved') SET @intReturnCode = 1 END ----Delete the FTP script used. SET @strString = 'del '+rtrim(@strLCL_DIR_NA)+'script.ftp /S/Q' EXEC @intResult = master..xp_cmdshell @strString, no_output DECLARE @strBISQL varchar(300) SET @strBISQL = 'BULK INSERT DENG0183.dbo.ttcwraw_data FROM ''' +rtrim(@strLCL_DIR_NA) + 'dbase.csv''' + ' WITH (DATAFILETYPE = ''CHAR'', FIRSTROW = ''1'', FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')' PRINT RTRIM(@STRBISQL)-- EXEC (@strBISQL) IF @Y = @X BREAK ELSE CONTINUE ENDthanks for the time... |
|
|
|
|
|
|
|
Bulk Insert with a twist