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

thanks maniac.. hope you can help

Please note running the cmd directly works fine.

Hi All, I have a requirement of running the dos command line below in VBA. Any help with the code/syntax will be much appreciated. c:\> eRaiseFlag.exe /FlagName:CreateFolder Thanks TS

Hi All, Basically I have a vbscript that exports data from a SQL server 2000 into Excel. There is a db field "Supplier_Code" held as a varchar, 7 digits long with leading zeros. When the data is pumped to excel the leading zeros are dropped. Below is the line of code that pumps this specific column to excel. objSheet.cells(intRow,2).value = Trim(rsBatchResults.Fields("Supplier_Code").value) I need to preserver the leading zero's in the excel output. I have tried Right("0000000" & rsBatchResults.Fields("Supplier_Code").value),7) which does not give me an error but doesn't add the leading zeros either

some progress... The code below is almost there... i have a problem with the insert statement strSQL = "INSERT INTO dbo.tblbaztest VALUES (" & arrXlData(0,x) & ",'" & arrXlData(0,x) & "')". With some debug code i noticed it was picking up the same value twice, once without any quotes and once with single quotes. So i did this "INSERT INTO dbo.tblbaztest VALUES ('" & arrXlData(0,x) & "')" . This works if the xls file has one column and the table has one column. However I need to load numerous columns. So in the code below I do not have a multidimentional array because with .Net "INSERT INTO dbo.tblbaztest VALUES (" & drSheet(0) & ",'" & drSheet(1) & "')" appears to work without a multidimentional array. Any ideas how to get this to work in my code below? Function Main() '--------------------------------------------------------------------------- ' set page variables and constants '--------------------------------------------------------------------------- '---- Constants required for database access ---- Const adOpenForwardOnly = 0 '---- CursorTypeEnum Const adLockReadOnly = 1 '---- LockTypeEnum Const adCmdText = &H0001 '---- CommandTypeEnum '---- Page Variables ---- dim dbSqlConnect, strSqlConnection, strSQL, objSqlCmd dim dbXlConnect, strXlSQL, rstXlResults, arrXlData dim objFSO, objStartFolder, objFolder, objFile objStartFolder = "D:\Baz\workflow" strXlSQL = "SELECT * FROM [sheet1$]" strSqlConnection = "Provider=sqloledb; Data Source=GBU0033\PROD; Initial Catalog=Ad hocs; User Id=stock_sales; Password=letmein;" '--------------------------------------------------------------------------- ' do the fun stuff '--------------------------------------------------------------------------- Set objFSO = CreateObject("Scripting.FileSystemObject") '---- check the folder exists ---- If Not objFSO.FolderExists(objStartFolder) Then Wscript.Echo "Folder does not exist." Else Set objFolder = objFSO.GetFolder(objStartFolder) '---- loop through all files and process xls files ---- For each objFile In objFolder.Files If objFSO.GetExtensionName(objFile) = "xls" Then 'Wscript.Echo "Processing file: " & objFile.Path '--------------------------------------------------------------------------- '---- connect to spreadsheet and grab all the data ---- '--------------------------------------------------------------------------- Set dbXlConnect = CreateObject("ADODB.Connection") dbXlConnect.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & objFile.Path & ";Extended Properties=""Excel 8.0;HDR=Yes;"";" Set rstXlResults = CreateObject("ADODB.Recordset") rstXlResults.Open strXlSQL, dbXlConnect, adOpenForwardOnly, adLockReadOnly, adCmdText if rstXlResults.bof and rstXlResults.eof then Wscript.Echo "There was no data in this spreadsheet" else 'grab everything into an array to work with, quicker since we don't 'keep connection open and have to keep going back for data arrXlData = rstXlResults.GetRows end if '---- close results set ---- rstXlResults.Close Set rstXlResults = Nothing ' ---- close excel connection ---- dbXlConnect.Close Set dbXlConnect = Nothing '--------------------------------------------------------------------------- '---- import all the data into the SQL Server DB ---- '--------------------------------------------------------------------------- if isarray(arrXlData) then Set dbSqlConnect = CreateObject("ADODB.Connection") dbSqlConnect.Open strSqlConnection 'loop data and insert into db for x=0 to ubound(arrXlData,2) strSQL = "INSERT INTO dbo.tblbaztest VALUES (" & arrXlData(0,x) & ",'" & arrXlData(0,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 End Function

I was struggling with VB Script so reverted to what I know but only latter found out sql 2000 does not support .Net. Unfortunately it does have to be in a DTS as this is just a small part of a very big process. Any help will be much appreciated.

thanks guys.. i had some luck.. i used visual studio to put together my code and EVENTUALLY got it to work.. See code below. However, when I put this into an active x script in a DTS in SQL Server 2000 it dont work. Not sure why but it does not like me declaring the objects.. for example the first error i get is on line 2. When i comment out the line after dir and leave it as Dim Dir then the error moves onto the next line.. i have tried declaring/setting the objects before the function and latter in the function but i get all sorts of errors.. is there something fundamentally wrong with the code? it does work though in visual studio.. PLEASE HELP Function Main() Dim Dir As New System.IO.DirectoryInfo("D:\Baz\workflow") Dim fil As System.IO.FileInfo Dim conn As OleDbConnection Dim sqlConn As New SqlConnection("Server=GBU0033\PROD;Database=Ad hocs;uid=stock_sales;password=letmein;connect timeout=100;Integrated Security=SSPI;") Dim sqlcmd As New SqlCommand("", sqlConn) Dim cmd As OleDbCommand Dim drSheet As OleDbDataReader sqlConn.Open() sqlcmd.ExecuteNonQuery() For Each fil In dir.GetFiles("*.xls") conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fil.FullName & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';") cmd = New OleDbCommand("SELECT * FROM [sheet1$]", conn) conn.Open() drSheet = cmd.ExecuteReader Do While drSheet.Read() sqlcmd.CommandText = "INSERT INTO dbo.tblbaztest VALUES (" & drSheet(0) & ",'" & drSheet(1) & "')" sqlcmd.ExecuteNonQuery() Loop conn.Close() conn = Nothing cmd = Nothing drSheet = Nothing Next sqlConn.Close() sqlConn = Nothing sqlcmd = Nothing Main = DTSTaskExecResult_Success End Function

Many Thanks pcstru .. thats a good place to start.. however I still need to means to scan the folder for the xls files and rather than doing a transformation of data by column, is there no means to say select * from the xl file and load into table XYZ as the structure for the files and the table are locked?

Thanks Jinnantonnix.. it is not a one off task. I need to automate this process. I can get a very simple code to work in access which as you say can buy me some time but this requirement is part of a very large project where there are a lot of depencies on jobs so a coded solution that can run in an active x scipt in sql 2000 is required.

Hi. I am very new to VBA and the task in hand is a tough one to start with. Basically I have requirement of code I can run using Active X in DTS to load data that sits in excel files in a certain folder location into an sql server 2000 table. All the files will be of the same format and will be on the first sheet. I know I need a connection to the sql server database, a connection string to Excel Application, something to identify what table the data is to go in, an array and loop to hold the file names and load one file at a time. However I just do not know how to put it all together. I am in desperate need of this code so any help would be very appreciated.