Import Wizard in Data Point

Toad Data Point & Toad Intelligence Central
1

Need to know if its possible to pick up latest excel file from Folder for importing into SQL DB via Import Wizard. Everyday one file will be added into the folder and i need to pick up only the latest one to import the same in to sql db

2

To rahul_kumbhat:

Thank you for posting in Toad World!

While we do not have a feature to automatically detect the latest excel file, you may be able to achieve the same effect using some command line scripts! Follow the steps below so that Toad Data Point can detect and import the latest excel file.

My Setup:
A folder with some excel files
Toad Data Point

MySetup.png
MySetup.png926×760 162 KB

Create a batch file to list newest file on top

  1. Right-click in the Windows Explorer -> New -> Text Document (not in the same folder as the excel files)
  2. Name your text document. I named it "ListFilesInChronologicalOrder.txt"
  3. Open the text file
  4. Type in the following, replacing the bracketed text with the folder of the excel files.

cd "[Path to Excel Folder]"
REM Save a list of files ordered with the newest files on top
dir /O-D /B > "[Path outside excel folder]\ListOfFiles.txt"
REM Read in the first line of the file, which should be the newest file in the folder
set /p NEWESTFILE=< "[Path outside excel folder]\ListOfFiles.txt"
REM Copy the newest file to another location where the import wizard can use it.
xcopy /y "%NEWESTFILE%" "[Path outside excel folder]\FileForImport.xlsx"

Batch File.png
Batch File.png815×219 14.5 KB

  1. Close the file
  2. Change the extension of the file from ".txt" to ".bat"

You can test this by running the .bat file. A copy of the newest excel file should appear.

Edit Automation Script

  1. Open your Automation Script.
  2. Add the "Run Program" System Activity into your script.
  3. Configure your "Run Program" Activity so that it runs the batch file
    ie: Program: "C:\Users\quest_dev\Desktop\ListFilesInChronologicalOrder.bat"

I hope this approach, or a similar approach, will allow you to accomplish what you need.

Let us know if this worked for you!

Software Developer I,
-Joshua Liong

3

Thanks Joshua

I liked your approach and will try to use the same for the automation.

Shall keep you posted on the result :slight_smile:

4

If there is a new file each day would I be correct in assuming there is a date in the filename with todays date? You can make your batch job simply copy the file with todays date to a standard name like CurrentFile.xlsx and always import from CurrentFile.xlsx. If the folder/path remains the same and file name only changes by current date just create a toad variable that has the current date formatted to match the date in the file name. Pass that variable to the batch job on the argument line. Your batch job would have the command Copy yourPath\FileName%1.xlsx CurrentFile.xlsx. I usually put another batch job to archive the file after it has been processed (move FileName%1 Archivefolder) just to keep the folder from being cluttered and it shows me which file has not been processed yet. With 3.6 you might be able to do this without batch jobs using Toad’s File copy but I like the control of batch jobs better.

5

i would use the Folder Processing Import type. Here you can put a date range on the file to pick up. There are preconfigured date ranges for last week and last month. That doesn't cover yesterday but maybe you can figure something out. You could use this technique and if needed edit the template with the sysdate - 1.

Debbie

FolderProcessing.png
FolderProcessing.png704×477 23.1 KB

6

Joshua the solution worked :). Also took GregDavis advice and modified the batch programme accordingly.

Thanks guys for your help.