Is it possible to add a calculated field in a exported excel file

[Reckley]

I have a collection of grids that has a login. After a user is logged in they will only see their information that also has reports linked on the local filter. What i would like to do now is allow them to download the report
in excel make a change and have the other fields auto-calculate and re-upload the excel file with the information populating to the grid. However, feel free to advise any better way to accomplish what i would like to do.
Also is there any way to add information from an excel file having it clear the information that is already in grid first instead off duplicating. I am new to alpha five without any knowledge of coding but any help would be greatly appreciated.

[jcarrallo]

Re: Is it possible to add a calculated field in a exported excel file

Hello Alex,
This video shows how you can export a report to excel including live formulas.
http://www.youtube.com/watch?v=jo5fjO8jaEc
I hope this helps,
Javier

[Reckley]

Re: Is it possible to add a calculated field in a exported excel file

Thank you very much that is just what i needed. However, how about my second question. How do you have a grid erase its content and update with the new content that would be updated by uploading the excel spread sheet.

[Scott B]

Re: Is it possible to add a calculated field in a exported excel file

Are you using dbf or SQL? If the latter, I would accomplish this by creating/running a delete query:

To create the code:
>From the main control panel, select 'code'
>then 'new'
>Script (Using Action Scripting)
>Add New
>SQL (using Alpha ADO)
>Execute DELETE query on an SQL database

Then insert the code created above in the appropriate client-side event in the grid.

[Reckley]

Re: Is it possible to add a calculated field in a exported excel file

I am using SQL and thank you for your response. With this code would it take place automatically once i upload the new excel spread sheet or would i have to delete before uploading the excel document.

[Peter.Greulich]

Re: Is it possible to add a calculated field in a exported excel file

I think you will have to use Alpha's import methods to get the updated Excel data into your SQL table.

[Al Buchholz]

Re: Is it possible to add a calculated field in a exported excel file

Are you using dbf or SQL? If the latter, I would accomplish this by creating/running a delete query:

To create the code:
>From the main control panel, select 'code'
>then 'new'
>Script (Using Action Scripting)
>Add New
>SQL (using Alpha ADO)
>Execute DELETE query on an SQL database

Then insert the code created above in the appropriate client-side event in the grid.

Or a truncate - depending on if it's single user or not - and other circumstances.

[Steve Wood]

Re: Is it possible to add a calculated field in a exported excel file

Alex,

You can use Scott's suggestion only if you are going to run this manually using Alpha Five Developer. Those 'code SQL' features, and the underlying xbasic they produce, are only usable on Desktop. Importing into an SQL table can be tricky because you often have to convert 'Excel' format in to SQL table format. For instance, your Excel might produce a 'Y' to mean Yes, but you want that to be a 1 in your table, meaning True. Or, Excel might produce an empty string to mean 'empty' whereas you specifically need your SQL table value to state NULL.

Yes you have to upload the file first. If you use the method I show below, you will upload the CSV output of your Excel file, not the XLS(x) file.

The fastest import method is to use LOAD DATA (mysql) or BULK INSERT (mssql). If you use multiple INSERT statements, it can take forever on a big file.

Example Import CSV using LOAD DATA (minus some error checking)

'==assumes CSV file has been uploaded
dim cn as sql::Connection
dim rs as sql::ResultSet
dim args as sql::Arguments
cn.open("::name::conn")

'==get defaults
fldr = "c:\mydata" + chr(92)
fldr = stritran(fldr,"\\","\\\") '== required for server path

'==purge table, ignore "not found" errors
vsql = "DELETE FROM mytable"
if cn.execute(vsql) = .f.
if contains(cn.callresult.text,"could not find the data you requested")=.f.
'==use code to write an error message to a log. Include cn.callresult.text in the output
end if
end if

'==perform import
vSQL = <<%longstring%
LOAD DATA INFILE '{fldr}myfile.csv' INTO TABLE mytable
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(ID,@Variable1,@within1,@NULL1)
set
within1 = IF(@within1='',0,@within1),
null1 = IF(@null1='Y',1,0),
variable1 = UPPER(@variable1)
;
%longstring%
vSQL = evaluate_string(vSQL)
if cn.execute(vSQL) = .f.
'==use code to write an error message to a log. Include cn.callresult.text in the output
end if

cn.close()