Open file - Address inside IDEA Script

7 posts / 0 new
Log in or register to post comments
Last post
Tue, 03/03/2020 - 14:53
#1
marconb10
Offline
Joined: 11/13/2019 - 12:39
Open file - Address inside IDEA Script

Hi!
I have some questions:
1) Is there a way to open all files from a folder? -> and put the different names (addresses) to an array?For example, now I ask the user using Input to say the address from the file, but I would like to process a lot of different .xlsx files at the same time.
2) If the user write the address as folder1/folder2/open.xlsx 
Is there a way to break the string and get only the "open"?
 
Thank you!!

Top
Tue, 03/03/2020 - 15:21
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

For the second question you can do something like this:


Sub Main
	text = "folder1/folder2/open.xlsx"
	text = iSplit(text, "", "/", 1, 1)
	MsgBox text
End Sub

The iSplit is from the @Split in the equation editor.  It allows you to split out some part of a text field.  In this case the first parameter is the text file, the second file is the start character, since I am doing this from right to left it is blank to indicate the start of the script and the third parameter is the end of the partion, in this case the /.  The fourth parameter is which part you wish to extract, in this case it is the first and the final parameter if you put it as 1 it means it will split from right to left, the default is left to right.  So this will extract everthing from the end to the first "/" starting on the right side and moving left.

For your first question I am not sure what you are looking for.  Can you give an example.

Top
Wed, 03/04/2020 - 07:13
(Reply to #2)
marconb10
Offline
Joined: 11/13/2019 - 12:39

Hi Brian! Thank you for your help!
The first question, is about opening files, without having to write all the addresses.
Example: I have 50 files inside my "Source Files.ILB" folder.
They Are 25 files from A1-01 to A1-25 and from B1-01 to B1-25
In my script, now I'm comparing A1-01 with B1-01 (to check differences).
But now, when I run, for example, I have to ask the user to write the A1-01 address and then B1-01 address. And then I run again, set A1-02 and B1-02 address..
I would like to know if there is a way to set only the folder, and open All files from it.
My goal is to make a script to compare them all without having to Run the script 25 times, and setting 50 different file addresses. (like with a loop)
 

Top
Wed, 03/04/2020 - 07:55
(Reply to #3)
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Yes that should be possible, any chance you can create a few example files for A1 and B1 so I would have something to work with to try and do a demo for you.

Top
Wed, 03/04/2020 - 12:24
(Reply to #4)
marconb10
Offline
Joined: 11/13/2019 - 12:39

A1-01= MOV20190101
B1-01 = VEN20190101
A1-02= MOV20190102
B1-02 = VEN20190102
A1-03= MOV20190103
B1-03 = VEN20190103

Files: 
File A1-01
File B1-01
File A1-02
File B1-02
File A1-03
File B1-03
Top
Wed, 03/04/2020 - 08:12
marconb10
Offline
Joined: 11/13/2019 - 12:39

Sorry, i don't know how to make the identation here.Here is my full code:
 
Dim Data As String
 
Sub Main
Data = InputBox ("Insert your date")
Call ExcelImport()
Call ExcelImport1()
Call ModifyField()'MOV"+Data+"-SQL Results.IMD
Call ModifyField1()'MOV"+Data+"-SQL Results.IMD
Call AppendField()'MOV"+Data+"-SQL Results.IMD
Call AppendField_Custo()'MOV"+Data+"-SQL Results.IMD
Call DirectExtraction()'MOV"+Data+"-SQL Results.IMD
Call ModifyField2()'VEN"+Data+"-SQL Results.IMD
Call ModifyField3()'VEN"+Data+"-SQL Results.IMD
Call AppendField1()'VEN"+Data+"-SQL Results.IMD
Call Summarization()'MOV_Vendas_only_"+Data+".IMD
Call RemoveField()'PT_MOV"+Data+".IMD
Call Summarization1()'VEN"+Data+"-SQL Results.IMD
Call RemoveField1()'PT_VEN"+Data+".IMD
Call JoinDatabase()'PT_MOV"+Data+".IMD
Call AppendField2()'PT_MOV"+Data+"_.IMD
Call JoinDatabase1()'PT_VEN"+Data+".IMD
Call AppendField3()'PT_VEN"+Data+"_.IMD
Call ExportDatabaseXLSX()'PT_VEN"+Data+"_.IMD
Call ExportDatabaseXLSX1()'PT_MOV"+Data+"_.IMD
End Sub
 
 
' File - Import Assistant: Excel
Function ExcelImport
Set task = Client.GetImportTask("ImportExcel")
dbName = "C:\Users\mbochernitsan\OneDrive\Desktop\IDEA_A2\Varejo\IDEA_"+Data+"\MOV"+Data+".xlsx"
task.FileToImport = dbName
task.SheetToImport = "SQL Results"
task.OutputFilePrefix = "MOV"+Data+""
task.FirstRowIsFieldName = "TRUE"
task.EmptyNumericFieldAsZero = "FALSE"
task.PerformTask
dbName = task.OutputFilePath("SQL Results")
Set task = Nothing
Client.OpenDatabase(dbName)
End Function
 
' File - Import Assistant: Excel
Function ExcelImport1
Set task = Client.GetImportTask("ImportExcel")
dbName = "C:\Users\mbochernitsan\OneDrive\Desktop\IDEA_A2\Varejo\IDEA_"+Data+"\VEN"+Data+".xlsx"
task.FileToImport = dbName
task.SheetToImport = "SQL Results"
task.OutputFilePrefix = "VEN"+Data+""
task.FirstRowIsFieldName = "TRUE"
task.EmptyNumericFieldAsZero = "FALSE"
task.PerformTask
dbName = task.OutputFilePath("SQL Results")
Set task = Nothing
Client.OpenDatabase(dbName)
End Function
 
' Modify Field
Function ModifyField
Set db = Client.OpenDatabase("MOV"+Data+"-SQL Results.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "CODIGO_DA_FILIAL"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = ""
field.Length = 8
task.ReplaceField "CODIGO_DA_FILIAL", field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
' Modify Field
Function ModifyField1
Set db = Client.OpenDatabase("MOV"+Data+"-SQL Results.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "CODIGO_DO_ITEM"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = ""
field.Length = 8
task.ReplaceField "CODIGO_DO_ITEM", field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
' Append Field
Function AppendField
Set db = Client.OpenDatabase("MOV"+Data+"-SQL Results.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "CONCATENATE"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = "CODIGO_DA_FILIAL+"".""+CODIGO_DO_ITEM"
field.Length = 100
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
' Append Field
Function AppendField_Custo
Set db = Client.OpenDatabase("MOV"+Data+"-SQL Results.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "CUSTO_TOTAL"
field.Description = ""
field.Type = WI_NUM_FIELD
field.Equation = "CUSTO_MEDIO_ANTERIOR*QUANTIDADE"
field.Decimals = 2
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
' Data: Direct Extraction
Function DirectExtraction
Set db = Client.OpenDatabase("MOV"+Data+"-SQL Results.IMD")
Set task = db.Extraction
task.IncludeAllFields
dbName = "MOV_Vendas_only_"+Data+".IMD"
task.AddExtraction dbName, "", "@If(@Left( DESCR_DA_OPERACAO_DE_ESTOQUE;5)==""VENDA"";DESCR_DA_OPERACAO_DE_ESTOQUE;"""")"
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
 
' Modify Field
Function ModifyField2
Set db = Client.OpenDatabase("VEN"+Data+"-SQL Results.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "CODIGO_DA_FILIAL"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = ""
field.Length = 8
task.ReplaceField "CODIGO_DA_FILIAL", field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
' Modify Field
Function ModifyField3
Set db = Client.OpenDatabase("VEN"+Data+"-SQL Results.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "CODIGO_DO_ITEM"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = ""
field.Length = 8
task.ReplaceField "CODIGO_DO_ITEM", field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
' Append Field
Function AppendField1
Set db = Client.OpenDatabase("VEN"+Data+"-SQL Results.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "CONCATENATE"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = "CODIGO_DA_FILIAL+"".""+CODIGO_DO_ITEM"
field.Length = 100
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
' Analysis: Summarization
Function Summarization
Set db = Client.OpenDatabase("MOV_Vendas_only_"+Data+".IMD")
Set task = db.Summarization
task.AddFieldToSummarize "CONCATENATE"
task.AddFieldToInc "CODIGO_DA_FILIAL"
task.AddFieldToInc "CODIGO_DO_ITEM"
task.AddFieldToTotal "QUANTIDADE"
dbName = "PT_MOV"+Data+".IMD"
task.OutputDBName = dbName
task.CreatePercentField = FALSE
task.UseFieldFromFirstOccurrence = TRUE
task.StatisticsToInclude = SM_SUM
task.PerformTask
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
 
' Remove Field
Function RemoveField
Set db = Client.OpenDatabase("PT_MOV"+Data+".IMD")
Set task = db.TableManagement
task.RemoveField "NO_OF_RECS"
task.PerformTask
Set task = Nothing
Set db = Nothing
End Function
 
' Analysis: Summarization
Function Summarization1
Set db = Client.OpenDatabase("VEN"+Data+"-SQL Results.IMD")
Set task = db.Summarization
task.AddFieldToSummarize "CONCATENATE"
task.AddFieldToInc "CODIGO_DA_FILIAL"
task.AddFieldToInc "CODIGO_DO_ITEM"
task.AddFieldToTotal "QUANTIDADE"
dbName = "PT_VEN"+Data+".IMD"
task.OutputDBName = dbName
task.CreatePercentField = FALSE
task.UseFieldFromFirstOccurrence = TRUE
task.StatisticsToInclude = SM_SUM
task.PerformTask
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
 
' Remove Field
Function RemoveField1
Set db = Client.OpenDatabase("PT_VEN"+Data+".IMD")
Set task = db.TableManagement
task.RemoveField "NO_OF_RECS"
task.PerformTask
Set task = Nothing
Set db = Nothing
End Function
 
' File: Join Databases
Function JoinDatabase
Set db = Client.OpenDatabase("PT_MOV"+Data+".IMD")
Set task = db.JoinDatabase
task.FileToJoin "PT_VEN"+Data+".IMD"
task.IncludeAllPFields
task.AddSFieldToInc "QUANTIDADE_SUM"
task.AddMatchKey "CONCATENATE", "CONCATENATE", "A"
task.CreateVirtualDatabase = False
dbName = "PT_MOV"+Data+"_.IMD"
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
 
' Append Field
Function AppendField2
Set db = Client.OpenDatabase("PT_MOV"+Data+"_.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "DIFERENÇA"
field.Description = ""
field.Type = WI_NUM_FIELD
field.Equation = " QUANTIDADE_SUM - QUANTIDADE_SUM1 "
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
' File: Join Databases
Function JoinDatabase1
Set db = Client.OpenDatabase("PT_VEN"+Data+".IMD")
Set task = db.JoinDatabase
task.FileToJoin "PT_MOV"+Data+".IMD"
task.IncludeAllPFields
task.AddSFieldToInc "QUANTIDADE_SUM"
task.AddMatchKey "CONCATENATE", "CONCATENATE", "A"
task.CreateVirtualDatabase = False
dbName = "PT_VEN"+Data+"_.IMD"
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
 
' Append Field
Function AppendField3
Set db = Client.OpenDatabase("PT_VEN"+Data+"_.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "DIFERENÇA"
field.Description = ""
field.Type = WI_NUM_FIELD
field.Equation = " QUANTIDADE_SUM1 - QUANTIDADE_SUM "
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX
Set db = Client.OpenDatabase("PT_VEN"+Data+"_.IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "C:\Users\mbochernitsan\OneDrive\Desktop\IDEA_A2\Varejo\IDEA_"+Data+"\Exports.ILB\PT_VEN"+Data+"_.XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX1
Set db = Client.OpenDatabase("PT_MOV"+Data+"_.IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "C:\Users\mbochernitsan\OneDrive\Desktop\IDEA_A2\Varejo\IDEA_"+Data+"\Exports.ILB\PT_MOV"+Data+"_.XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 

Top
Thu, 03/05/2020 - 02:36
klmi
Offline
Joined: 02/13/2019 - 08:41

To choose a special folder you can use the following code template:

Sub Main
  Dim oShell, oFolder, oFolderItem, strPath
  Set oShell = CreateObject("Shell.Application")
  ' 17 = jump to toplevel folder
  Set oFolder = oShell.Namespace(17)
  Set oFolderItem = oFolder.self
  strPath = oFolderItem.Path
  Set oFolder = oShell.BrowseForFolder(0, "Please choose Folder!", 1, strPath)
  If (Not oFolder is Nothing) Then
    Set oFolderItem = oFolder.self
    oPath = oFolderItem.Path
    MsgBox(oPath)
  End If
End Sub

If you want to iterate through all the files in a choosen folder please search for the Dir() object in the language browser. You will find an example there how to do that.

Top