Open file - Address inside IDEA Script
Forums
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!!
Hi Brian! Thank you for your
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)
Sorry, i don't know how to
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
To choose a special folder
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.
For the second question you
For the second question you can do something like this:
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.