Multiple file import from report reader
Forums
I have a text file, which I have imported through report reader.
There is a template set up to recognise the columns.
I have 12 monthly sales reports.
How can I import, apply template and merge results into one database?
The multiple import function built into idea only allows files of the same type to be imported.
Due to a two step process (taking text file and converting to pdf), I cannot do a multiple file import.
What are the shortcuts I can apply (if any)?
Hi Melis,
Hi Melis,
Here is some example code for you. I am storing the 12 files in an array. I then call the ImportFiles function that loops through the 12 files and imports each one. I store the imported filename into another array that I then use to append them together in the AppendFiles function.
Option Explicit
Dim sFileList(12) As String
Dim sIDEAFileList(12) As String
Sub Main
'list of files to import
sFileList(0) = "vendor master list.pdf"
sFileList(1) = "vendor master list - Copy.pdf"
sFileList(2) = "vendor master list - Copy (2).pdf"
sFileList(3) = "vendor master list - Copy (3).pdf"
sFileList(4) = "vendor master list - Copy (4).pdf"
sFileList(5) = "vendor master list - Copy (5).pdf"
sFileList(6) = "vendor master list - Copy (6).pdf"
sFileList(7) = "vendor master list - Copy (7).pdf"
sFileList(8) = "vendor master list - Copy (8).pdf"
sFileList(9) = "vendor master list - Copy (9).pdf"
sFileList(10) = "vendor master list - Copy (10).pdf"
sFileList(11) = "vendor master list - Copy (11).pdf"
sFileList(12) = "vendor master list - Copy (12).pdf"
Call ImportFiles()
Call AppendFiles()
client.RefreshFileExplorer
End Sub
Function ImportFiles()
Dim i As Integer
Dim dbName As String
For i = 0 To 12
dbName = client.UniqueFilename(Left(sFileList(i) , Len(sFileList(i)) - 4))
sIDEAFileList(i) = dbName
Client.ImportPrintReport "E:\IDEA\Projects\Import test\Import Definitions.ILB\vendor master list.jpm" , "E:\IDEA\Projects\Import test\Source Files.ILB\" & sFileList(i) , dbname, FALSE
Next i
End Function
Function AppendFiles()
Dim db As database
Dim task As task
Dim dbName As String
Dim i As Integer
Set db = Client.OpenDatabase(sIDEAFileList(0) )
Set task = db.AppendDatabase
For i = 1 To 12
task.AddDatabase sIDEAFileList(i)
Next i
dbName = client.UniqueFilename("Append Databases")
task.PerformTask dbName, ""
Set task = Nothing
Set db = Nothing
End Function
Hi Melis,
Hi Melis,
Here is a script that might help you out. All you need to do is put all your files in a directory under your project folder, make sure there are no other files in the directory. Once you run the script it will ask for the location of where the files is stored, the script then reads the folder and places the files in an array. The script then asks for the location of the print report reader definition. It will then use this information to import each file and append them all together.
Good luck.
Brian
I have the script but I want
I have the script but I want it to apply to a new year.
The script is importing blank data.
What is the cause of this?
When I run the macro it comes up with 'Browse For Folder'
I then select the folder with the monthly sales reports.
Next it asks for the Import Definitions.ILB folder, where I have the 'Sales report template.jpm'
After going through this process I end up with 11 imports with no data.
Hi Melis,
Hi Melis,
As this is for the new year is there any chance that the file format has changed, this would explain the files having no data. You can test this out by selecting one of the files and the import definition and then select edit and see if the template still matches up with the new files. Let me know if this is the problem, if it is you will need to create a new template for the current years files.
Brian
Hello Brian,
Hello Brian,
In your script, it prompts the user to select a folder within whichever project is currently open in IDEA. How would this script be modified to instead select all files from an external folder? For example, I have 260 PDFs in an external folder that all need to be imported into IDEA and have the same template applied to each file. I'm trying to figure out how to get this script to work for that circumstance.
Thank you!
Marie
Maybe the code below will
Maybe the code below will help you. I used that for comparable work ...
Sub Main
' dialog to choose a path
Dim oShell, oFolder, oFolderItem, strPath
' call Windows function
Set oShell = CreateObject("Shell.Application")
' 17 = jump to top level of folders
Set oFolder = oShell.Namespace(17)
Set oFolderItem = oFolder.self
Set strPath = oFolderItem.Path
'Set oFolder = oShell.BrowseForFolder(0, "Please choose Path!", 1, strPath)
oPath = "D:\IDEA Projects\Project XXX\Quelldateien.ILB\converted"
'If (Not oFolder is Nothing) Then
' Set oFolderItem = oFolder.self
' oPath = oFolderItem.Path
' MsgBox(oPath)
'End If
' go through all files in path
Dim sFile As String
' "\" must be appended at the end of a path
' "*.*" means we look for all files
sFile = Dir(CStr(oPath & "\" & "*.*"))
Do While sFile <> ""
' put here code which should executed for all files
' ------------------------------------------------------------------------------
dbName = sFile & ".IMD"
Client.ImportPrintReportEx "D:\IDEA Projects\Project XXX\Importdefinitionen.ILB\mask.jpm", oPath & "\" & sFile, dbName, FALSE, FALSE
'Client.OpenDatabase (dbName)
Client.CloseAll
'-------------------------------------------------------------------------------
' end of code; next line jumps to next file in path
sFile = Dir()
Loop
End Sub
Hi Melis and welcome to the
Hi Melis and welcome to the site.
You could use my multiple file import script (http://ideascripting.com/ideascript/import-multiple-files) as it allows you to select muliple files and so long as they use the same template it would bring them in. You could then do a regular append. The other way is to create a script that does it all for you. I will put something together and post an example.
Brian