Teaser

Hi, My task basically is to dump the data in two tables into a text file. Seems simple enough but wait... The data in both tables is fomatted differently.. such as one table has many more columns than the other, different data types and so on.. I need a script which will put the data in both tables into one text file.. The overall task is much more complicated that this but hopefully someone can start me off. Much appreciated.

Thanks LosOjos. I have amended the post I've had a look at the link you provided and am even more lost. Took me forever to write the code above and now this... am loosing my marbles as we speak

forgot to say, individually they work fine. Am just trying to integrate them.

Hi Guys, I have two sets of code. The first one basically looks in a folder, identifies the xls files and loads the data into a database tbl. The 2nd is basically an email script. What I am trying to do is add error handling to the 1st code so that it works as such : if a file fails to load then send out this email. both codes are below. Thanks for any assistance you can offer. Function Main() Const adOpenForwardOnly = 0 Const adLockReadOnly = 1 Const adCmdText = &H0001 dim strSqlConnection dim dbSqlConnect dim strSQL dim objSqlCmd dim dbXlConnect dim strXlSQL dim rstXlResults dim arrXlData dim objFSO dim objStartFolder dim objFolder dim objFile objStartFolder = "F:\Metastorm BPM\MDS\Output\" strXlSQL = "SELECT * FROM [sheet1$A3:T65000]" Set strSqlConnection = CreateObject("ADODB.Connection") strSqlConnection.Provider = "mmm" strSqlConnection.Properties("Data Source").Value = "mmm" strSqlConnection.Properties("Initial Catalog").Value = "mmm" strSqlConnection.Properties("User ID").Value = "mmm" strSqlConnection.Properties("Password").Value = "mmm" Set objFSO = CreateObject("Scripting.FileSystemObject") If Not objFSO.FolderExists(objStartFolder) Then ' Does folder exist? Else Set objFolder = objFSO.GetFolder(objStartFolder) For each objFile In objFolder.Files ' Begin to LOOP through all files in the specified folder and process If objFSO.GetExtensionName(objFile) = "xls" Then Set dbXlConnect = CreateObject("ADODB.Connection") dbXlConnect.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & objFile.Path & ";Extended Properties=""Excel 8.0;HDR=Yes;"";" ' Excel Object connection properties Set rstXlResults = CreateObject("ADODB.Recordset") rstXlResults.Open strXlSQL, dbXlConnect, adOpenForwardOnly, adLockReadOnly, adCmdText if rstXlResults.bof and rstXlResults.eof then ' Begin to create recordset else arrXlData = rstXlResults.GetRows end if rstXlResults.Close Set rstXlResults = Nothing dbXlConnect.Close Set dbXlConnect = Nothing if isarray(arrXlData) then Set dbSqlConnect = CreateObject("ADODB.Connection") dbSqlConnect.Open strSqlConnection for x=0 to ubound(arrXlData,2) strSQL = "INSERT INTO dbo.MDS_TEMP_Staging VALUES ('" & arrXlData(0,x) & "','" & arrXlData(1,x) & "','" & arrXlData(2,x) & "', '" & arrXlData(3,x) & "','" & arrXlData(4,x) & "', '" & arrXlData(5,x) & "','" & arrXlData(6,x) & "' , '" & arrXlData(7,x) & "','" & arrXlData(8,x) & "','" & arrXlData(9,x) & "', '" & arrXlData(10,x) & "','" & arrXlData(11,x) & "', '" & arrXlData(12,x) & "','" & arrXlData(13,x) & "' ,'" & arrXlData(14,x) & "','" & arrXlData(15,x) & "','" & arrXlData(16,x) & "', '" & arrXlData(17,x) & "', '" & arrXlData(18,x) & "', '" & arrXlData(19,x) & "')" Set objSqlCmd = CreateObject("ADODB.Command") objSqlCmd.ActiveConnection = dbSqlConnect objSqlCmd.CommandType = adCmdText objSqlCmd.CommandText = strSQL objSqlCmd.Execute Set objSqlCmd = Nothing next set dbSqlConnect = nothing set arrXlData = nothing end if End If Next End If set objFSO = Nothing Main = DTSTaskExecResult_Success End Function AND THE EMAIL CODE IS : '************************************************* ********************* ' Visual Basic ActiveX Script '************************************************* *********************** Function Main() Set conLocal = CreateObject("ADODB.Connection") conLocal.Provider = "sqloledb" conLocal.Properties("Data Source").Value = " conLocal.Properties("Initial Catalog").Value = " conLocal.Properties("Integrated Security").Value = conLocal.ConnectionTimeout = 5 conLocal.commandtimeout = 5 conLocal.Open dim html_email strRecipients = "XXXX" 'strCC = "XX" Call sendEmail(strRecipients, strCC, html_email) Main = DTSTaskExecResult_Success End Function Function sendEmail(strRecipients, strCC, html_email) Dim iMsg 'Use COM to create Message and Configuration Objects Set iMsg = CreateObject("CDO.Message") ' Apply the settings to the message. With iMsg .To = strRecipients If Not IsNull(strCC) Then .CC = strCC .From = "XX" .Subject = "MONTHLY load has failed" .HTMLBody = html_email .Send End With ' Clean up variables. Set iMsg = Nothing End Function

thanks a million guys... it worked first time out again many many thanks and much appreciated

Thanks All.. am not sure what the syntax should be. Please have a look at my code below. Its the variable "clpApprovedData" that I need to apply this trim on. function IncorrectAttachment() { var actionName = eworkGetField("txtActionName"); var strorginal = eworkGetField("clpGeneratedData"); var strreturned = eworkGetField("clpApprovedData"); if(strorginal.toUpperCase()!= strreturned.toUpperCase()) { alert("strorginal=" + strorginal + "strreturned=" + strreturned); return false; } return true; }

cheers mate I'll give it a go now and let you know how I get on.

Hi All, Ho can I trim a string so that all characters after a specific value are dropped. E.g I want to DROP the "~1.xls" from the following string. 0000715 RECORDATI PHARMACEUTICALS LTD 07 2010~1.xls. So I want the code to lookup "2010" in the string and delete all charaters after that. Thanks T

am totally new to programming hence the question, how??

Not sure what that means

We are using a BPM tool that only supports JAVASCRIPT or maybe VBSCRIPT. The reason for the comparison is that the file being sent out will be manipulated externally and once it has been returned, a user must attach the file to this BPM process. To negate the possibility of the user attaching the wrong file to the 'folder', this check is critical. Hope that helps.

Hi All, Basically I need to compare two variables using the file name to ensure what file was sent out is what was received back using JAVASCRIPT. var A - will have a file name such as 0000462 J&J MSD CONS PHARM 07 2010.xls var B - will be the same file name Windows automatically makes var B appear as 0000462 J&J MSD CONS PHARM 07 2010~1.xls Thus the comparison does not work. Any ideas would be much appreciated. I was looking at the contains method but cannot find anything. The file name above is just an example and the number of characters prior to the ".xls" will vary a lot. Thanks, T

Anyone????

Hi Guys, I have two sets of code. The first one basically looks in a folder, identifies the xls files and loads the data into a database tbl. The 2nd is basically an email script. What I am trying to do is add error handling to the 1st code so that it works as such : if a file fails to load then send out this email. both codes are below. Thanks for any assistance you can offer. Function Main() Const adOpenForwardOnly = 0 Const adLockReadOnly = 1 Const adCmdText = &H0001 dim strSqlConnection dim dbSqlConnect dim strSQL dim objSqlCmd dim dbXlConnect dim strXlSQL dim rstXlResults dim arrXlData dim objFSO dim objStartFolder dim objFolder dim objFile objStartFolder = "F:\Metastorm BPM\MDS\Output\" strXlSQL = "SELECT * FROM [sheet1$A3:T65000]" Set strSqlConnection = CreateObject("ADODB.Connection") strSqlConnection.Provider = "sqloledb" strSqlConnection.Properties("Data Source").Value = "gbr00wrkd1" strSqlConnection.Properties("Initial Catalog").Value = "e-work" strSqlConnection.Properties("User ID").Value = "ework" strSqlConnection.Properties("Password").Value = "cq" Set objFSO = CreateObject("Scripting.FileSystemObject") If Not objFSO.FolderExists(objStartFolder) Then ' Does folder exist? Else Set objFolder = objFSO.GetFolder(objStartFolder) For each objFile In objFolder.Files ' Begin to LOOP through all files in the specified folder and process If objFSO.GetExtensionName(objFile) = "xls" Then Set dbXlConnect = CreateObject("ADODB.Connection") dbXlConnect.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & objFile.Path & ";Extended Properties=""Excel 8.0;HDR=Yes;"";" ' Excel Object connection properties Set rstXlResults = CreateObject("ADODB.Recordset") rstXlResults.Open strXlSQL, dbXlConnect, adOpenForwardOnly, adLockReadOnly, adCmdText if rstXlResults.bof and rstXlResults.eof then ' Begin to create recordset else arrXlData = rstXlResults.GetRows end if rstXlResults.Close Set rstXlResults = Nothing dbXlConnect.Close Set dbXlConnect = Nothing if isarray(arrXlData) then Set dbSqlConnect = CreateObject("ADODB.Connection") dbSqlConnect.Open strSqlConnection for x=0 to ubound(arrXlData,2) strSQL = "INSERT INTO dbo.MDS_TEMP_Staging VALUES ('" & arrXlData(0,x) & "','" & arrXlData(1,x) & "','" & arrXlData(2,x) & "', '" & arrXlData(3,x) & "','" & arrXlData(4,x) & "', '" & arrXlData(5,x) & "','" & arrXlData(6,x) & "' , '" & arrXlData(7,x) & "','" & arrXlData(8,x) & "','" & arrXlData(9,x) & "', '" & arrXlData(10,x) & "','" & arrXlData(11,x) & "', '" & arrXlData(12,x) & "','" & arrXlData(13,x) & "' ,'" & arrXlData(14,x) & "','" & arrXlData(15,x) & "','" & arrXlData(16,x) & "', '" & arrXlData(17,x) & "', '" & arrXlData(18,x) & "', '" & arrXlData(19,x) & "')" Set objSqlCmd = CreateObject("ADODB.Command") objSqlCmd.ActiveConnection = dbSqlConnect objSqlCmd.CommandType = adCmdText objSqlCmd.CommandText = strSQL objSqlCmd.Execute Set objSqlCmd = Nothing next set dbSqlConnect = nothing set arrXlData = nothing end if End If Next End If set objFSO = Nothing Main = DTSTaskExecResult_Success End Function AND THE EMAIL CODE IS : '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Set conLocal = CreateObject("ADODB.Connection") conLocal.Provider = "sqloledb" conLocal.Properties("Data Source").Value = "gbr00wrkd1" conLocal.Properties("Initial Catalog").Value = "e-work" conLocal.Properties("Integrated Security").Value = "SSPI" conLocal.ConnectionTimeout = 5 conLocal.commandtimeout = 5 conLocal.Open dim html_email strRecipients = "XXXX" 'strCC = "XX" Call sendEmail(strRecipients, strCC, html_email) Main = DTSTaskExecResult_Success End Function Function sendEmail(strRecipients, strCC, html_email) Dim iMsg 'Use COM to create Message and Configuration Objects Set iMsg = CreateObject("CDO.Message") ' Apply the settings to the message. With iMsg .To = strRecipients If Not IsNull(strCC) Then .CC = strCC .From = "XX" .Subject = "MONTHLY load has failed" .HTMLBody = html_email .Send End With ' Clean up variables. Set iMsg = Nothing End Function

thanks dwhyte85.. i'll give this a go