Import an Excel file using Xbasic

[jopowers68]

I have a connection string setup to import an excel file that stays in the same format every time but the file name changes. I would like to be able to have a file selection dialog box popup so I can select the new file each week. I can get the box to popup but I can't seem to get the value of the file selected to populate the connection string line of code. Can anyone help me with this? I have included my code below.

'Date Created: 11-Oct-2007 06:33:25 PM
'Last Updated: 11-Oct-2007 06:46:43 PM
'Created By  : JamieP
'Updated By  : JamieP
'
dim connectionString as c
dim filn as c
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
filn = ui_dlg_box("Alpha Five",<<%dlg%
{region}
Matrix File:| [%p=ui_get_file("","Excel (*.xls)",filn,"X");I=$a5_smart_file%.120filn];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%)
 
connectionString = "{A5API='Excel',A5Syntax='Excel',FileName=filn}" 
 
delete options 
dim options as p 
options.ConsolidateArguments= .t.
options.AddTablesToDatabase= .t.
options.ShowProgress= .t.
options.AllowCancel= .t.
 
 
delete a_import
dim a_import[0] as p 
 
 
a_import[].ObjectName = "Sheet1$"
a_import[..].SQLSelectStatement = "SELECT * FROM [Sheet1$]"
a_import[..].SQLType = "Portable"
a_import[..].Arguments = ""
a_import[..].ImportType = "Append to existing table"
a_import[..].AppendOption = "Append all"
a_import[..].AppendToTableName = a5.get_path() + chr(92) + "matrix dummy"+".dbf"
a_import[..].AppendFieldMap = <<%txt%
Week_of|Week
Event_date|Event_date
Campaign|Campaign_
Volume|Volume
Postcard_qty|Postcard_vol
Post_card_drop_date|Postcard_drop_date
Dealer_number|Dealer_num
Drop_date|Drop_date
Letter_800|Letter_800_
Postcard_800|Postcard_800_
Postcard_yn|Postcard_y_n_
Logo_name|Logo_1
Logo_name2|Logo_2
Stacked_offer|Stacked_offer
Option_d_amount|Option_d_amt
Option_d_store|Option_d_store
Dealer_nam|Dealer1
Dealer_nam_1|Dealer2
Address|Address
Address_2|Address_2
City|City
State|State
Contact1|Contact1
Contact2|Contact2
Zip|Zip
Phone_number_1|Phone_num
Phone_number_2|Phone_num_2
Event_date_1|Event_date_1
Event_date_2|Event_date_2
Event_date_3|Event_date_3
Event_date_4|Event_date_4
Event_date_5|Event_date_5
Changes|Changes
Tagline_1|Tagline_1
Tagline_2|Tagline_2
Tagline_3|Tagline_3
Tagline_4|Tagline_4
Tagline_5|Tagline_5
Tagline_6|Tagline_6
Tagline_7|Tagline_7
Tagline_8|Tagline_8
Tagline_9|Tagline_9
Tagline_10|Tagline_10
Notes|Notes
Option_e_text|Option_e_text
%txt%
 
 
dim flagSilent as l
delete args 
dim args as sql::arguments
flagSilent = .f.
delete p 
dim p as p 
 
p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent,args)
'p has an .hasError and .errorText property
'a_import[] has the following properties for each item: .countRecordsImported, .hasError, .errorText, .userCancelled

[Tbaker]

Re: Import an Excel file using Xbasic

I don't get involved too much with Excel imports

but I think that the connection string is expecting the filename to be in single quotes.

try FileName="'"+filn+"'"

Like I said, I really don't get involved in Excel - but I read that the connection strings sometimes require the filenames to be withing single quotes.

Hope it works

Tom

[Tim Kiebert]

Re: Import an Excel file using Xbasic

Same as Tom. I don't do much with excel either or connection strings. But if you use one of the genies with in alpha to build a connection string you will find that Tom is correct about the single quotes around the file name. Your expression as you have it returns this
"{A5API='Excel',A5Syntax='Excel',FileName=filn}"
In order to substitute in the value contained in filn you need to insert it with +filn+ as Tom suggests

connectionString = "{A5API='Excel',A5Syntax='Excel',FileName='" +filn+ "'}"

There is one more issue and that is that you are using the filn variable to hold the result of the UI_DLG_BOX() function which records the value of the button used to close the dialog. So after choosing the file filn holds the file name, then when you click OK the function places "OK" into the variable replacing the file name. Just use some other variable name to record the result of the actual dialog. and the file name will remain in filn for use later in the script. You would use the result of the dialog, which in this case would be OK or Cancel, to test if the user canceled so you can end the script.

[Tbaker]

Re: Import an Excel file using Xbasic

Tim

I missed the result of the dialog box completely. I'm glad you caught that.

Tom

[jopowers68]

Re: Import an Excel file using Xbasic

Thank you both.

How do I set a different variable to keep the value of the dialog box?

I meant to say "result of the actual dialog"

[Tbaker]

Re: Import an Excel file using Xbasic

You can varC_result as the variable for the ui_dlg_box. Your filename is preserved in the variable "filn" from the ui_get_file dialog box.

Here is a little code to show you

Date Created: 12-Oct-2007 06:12:33 PM
'Last Updated: 12-Oct-2007 06:12:33 PM
'Created By : Tom
'Updated By : Tom
dim filn as c
dim varC_result as c
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Alpha Five",<<%dlg%
{region}
Matrix File:| [%p=ui_get_file("","Excel (*.xls)",filn,"X");I=$a5_smart_file%.120filn];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%)
if varC_result="CANCEL" then
end
end if

msgbox("","'"+filn+"'")




All it does is allow you to select an XLS file and then returns the file name that you selected in a msgbox.

It also shows you how to format the variable "filn" for inclusion in your connection string.

Tom

[jopowers68]

Re: Import an Excel file using Xbasic

Thanks Tom - this worked perfectly and helps me to better understand dialogs.