Skip to main content

Multiple file import from report reader

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)?

Brian Element Wed, 07/13/2016 - 07:49

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

Brian Element Wed, 07/13/2016 - 19:54

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

 

Melis Wed, 07/13/2016 - 20:27

Thanks Brian. This is quite detailed and advanced.
I am only at beginner level of IDEA and do not have any exposure or experience with scripts.
 
 

Brian Element Thu, 07/14/2016 - 19:09

In reply to by 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

Melis Thu, 07/14/2016 - 20:06

Thanks Brian,
That worked amazingly. It has obtained each of the monthly sales reports, applied the template and extraced them into a separate database.
How do I merge the 12 databases into one database so that I can sort the data for the whole year?

Brian Element Thu, 07/14/2016 - 20:30

In reply to by Melis

Hi Melis,

The script should have done that for you.  If you look there should be an append file under one of your 12 databases that contains all of the information in one file.

Glad it worked for you.

Brian

Melis Mon, 06/26/2017 - 01:19

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.
 
 

Brian Element Mon, 06/26/2017 - 06:09

In reply to by 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

mvhoie Tue, 07/02/2019 - 16:26

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

klmi Wed, 07/03/2019 - 03:35

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