Importing multiple Excel files

[neecy]

Hello-
I want to write a script or use Action scripting to import multiple Excel worksheets from different folders. Each worksheet have the same name and field names and I want to import them into a table I created. Is this possible? I did an import on one table and it worked, however, I don't want to run each import separately 15 times a day/week.

Thank you-

Neecy

[Stan Mathews]

Re: Importing multiple Excel files

Examine the xbasic for a saved import operation and you will find a line like

import_filename = filename_decode("f:\output\sal_item_file.txt")

If you want to be able to dynamically change that line altering the source for the import you need to save the xbasic as a script, alter it by changing that line to something like

import_filename = ui_get_file("Choose","Text(*.txt)","C:\ ","X")

save the script and run it rather than the saved operation.

Copy this

ui_get_file("Choose","Text(*.txt)","C:\ ","X")

into the interactive editor and <enter key> at the end of the line to see what happens.

[neecy]

Re: Importing multiple Excel files

Hi Stan-


Thank you-
The script worked in choosing the files with no problem.

Just another quick question, is there a way to automatically copy Excel files from multiple folders into one folder? That way, the import script will just import the multiple Excel files from one location into the tables I have setup.

Thanks again

Neecy

[Stan Mathews]

Re: Importing multiple Excel files

The script worked in choosing the files with no problem.

We really need to add something to abort if the user doesn't choose a file.

import_filename = ""
import_filename = ui_get_file("Choose","Text(*.txt)","C:\ ","X")
if import_filename = ""
end
end if

Just another quick question, is there a way to automatically copy Excel files from multiple folders into one folder? That way, the import script will just import the multiple Excel files from one location into the tables I have setup.

Not understood.

Each worksheet have the same name and field names and I want to import them into a table I created.

Wouldn't each successive copy overwrite the previous leaving you with one excel file?

[neecy]

Re: Importing multiple Excel files

Hi Stan-

Quote:
Wouldn't each successive copy overwrite the previous leaving you with one excel file?

I apologize, I just found out from the end user that the file will have distinct names, however the field names are the same. The files will overwrite each other which is what we want when importing the Excel files over every week. Afterwards, the import_filename script will append the records to the tables I created. Since the main objective is to import the multiple Excel files into a master table, I need to know if there is a way to have the Excel files that are in 15 different locations to copy into one folder I will create and then have the import filename script to append the records to my tables.

Thanks-

Neecy

[Stan Mathews]

Re: Importing multiple Excel files

You may or may not want the ui_msg_box() line I included that confirms that the file was found. Same with the remove line.

if file.exists("C:\temp\Book1.xls")
ui_msg_box("Found","Book1.xls exists in c:\temp",UI_OK)
file.copy("C:\temp\Book1.xls","C:\tempdb\Book1.xls")
file.remove("C:\temp\Book1.xls")
else
rslt = ui_msg_box("Oops!","Book1.xls NOT FOUND, cancel to abort",UI_OK_CANCEL)
if rslt = UI_CANCEL_SELECTED
end
end if
end if

Just put 15 of these in a script and edit the filenames and directories.

If you are going to be importing .xls files you can change the earlier line to

import_filename = ""
import_filename = ui_get_file("Choose","Excel(*.xls)","C:\ ","X")
if import_filename = ""
end
end if

[Stan Mathews]

Re: Importing multiple Excel files

This might be easier to maintain. Just edit/add to the list at the top of the script and the destination directory.

lst = <<%file_list%
[COLOR="RoyalBlue"]c:\temp\book1.xls
c:\program files\a_gloss.dbf
h:\executive\hdb\awg\download\elst2550.b[/COLOR]
%file_list%

for each foo in lst
	if file.exists(foo)
	file.copy(foo,"[COLOR="red"]c:\tempdb[/COLOR]"+chr(92)+file.filename_parse(foo,"ne"))
	file.remove(foo)
	else
	rslt = ui_msg_box("Oops!",""+foo+" NOT FOUND, cancel to abort",UI_OK_CANCEL)
	if rslt = UI_CANCEL_SELECTED
	end
	end if
	end if
next