Excel Sql connection

[Farzadalpa]

Re: Excel Sql connection

Thanks Ted , I am going to use the "a5_AlphaDAO_Import" operation ( the xbasic code) .I can get a list of the excel column names via excel sql connection then I use the list as choices for combobaxs controls in a xdialog window .Each of this controls (variables) corresponds to a field-name in my dbf table .After the user selects the corresponding fields then it's easy to code the query and the field mapping parameters required by "a5_AlphaDAO_Import.
Thank you, Ted and Stan for your help.
Nick

[Ted Giles]

Re: Excel Sql connection

Ok, had a think about this now.
Couple of issues,
Data Formatting. The Numerics are in Scientific Format in Excel? Cannot see how this would be required unless you are building something to very tight tolerances.
The Import routines. I had presumed that you had looked at the text in the Import Panel explaining what to use. Obviously an incorrect assumption on may part - sorry.
In the scenario you describe I don't think that you will be able to do much for the user automatically - even if you parse the field content and try to select the fields based on the actual content.
It depends on the number of fields in the Excel table. If there are only 3 for example - like those in post #1 it could be easy.
You mention that the field names change but the data remains the same? If so, then Field 1/2/3 no matter what their title will contain the data you anticipate.
However you say later that the field positions sometimes change, and that is the killer.

If there are 100 fields and all need analysing, it's a chore.
I wouldn't use SQL, I'd create a template Excel sheet(1) which is in the correct format, and insert the data sheet (2) which is downloaded, then copy the data from (2) to the relevant column in (1).

At some point, one side of the process has to be consistent and if you have no control over the incoming data, it needs to be on your side.
If you go the SQL ADO route, then depending on the number of fields to be imported and computer savvy users, it may work but requires a great attention to detail.

Have you looked here?
https://www.alphasoftware.com/docume...ment/index.xml

[Ted Giles]

Re: Excel Sql connection

Well it's late here in the UK, so I'll look again tomorrow.

[Farzadalpa]

Re: Excel Sql connection

Good news ,I think I found a way to do it using "a5_AlphaDAO_Import".

[Farzadalpa]

Re: Excel Sql connection

Ted,
The answer to 1) is yes. 2) Yes for "c" data, for Numbers data type is made to "E" 20,0.

In may app ,the user downloads an excel table from "overstock.com" which is the inventory listed on the site.
My app imports the excel inventory ,updates it and then make an CSV file to upload to the site.
Every thing works fine but "overstock.com" changes the field names and position from time to time.
So to cope with this the user has to change the format and the top row of the downloaded excel table before my app can import the table.
I like to give the user a way to be able to choose the correct column of the excel table to import to the correct corresponding field.
I know this probably can be down by coding a append operation ,but it would so much easier with the Sql connection ,If worked.
How would you do this?
Thanks,
Nick

[Ted Giles]

Re: Excel Sql connection

So can you confirm that
1 the data to be imported and appended has different field names so you cannot match on field name
2 the data has each field size set to 255 char irrespective of original field size

And, there will be a way around this. Can you list the steps in just a bit more detail.
I am unclear why Excel and SQL figure in the process. I have imported data with all sorts of different field names and sizes into a common Db using the field position, so if you could explain a bit more, hopefully we can suggest alternatives.

[Stan Mathews]

Re: Excel Sql connection

This is frustrating. I found how to reveal the dbfrowsyntax property but the documentation indicates it is read only. From the interactive.

dim conn as SQL::Connection
dim ti as SQL::TableInfo
dim connString as C
connString = "{A5API='Access', FileName='C:\Alpha\a5V11\mdbfiles\alphasports.mdb'}"
if .not. conn.open(connString)
    ui_msg_box("Error", conn.CallResult.text)
    end
end if
if .not. conn.GetTableInfo(ti, "customer")
    ui_msg_box("Error", conn.CallResult.text)
    conn.close()
    end
end if
'run the above and then
? ti.DBFRowSyntax
= CUSTOMER_ID,N,11,0,"notnullable,constant,generate"
FIRSTNAME,C,20,0,"nullable,updateable,setnull,shownull"
LASTNAME,C,20,0,"nullable,updateable,setnull,shownull"
COMPANY,C,32,0,"nullable,updateable,setnull,shownull"
PHONE,C,20,0,"nullable,updateable,setnull,shownull"
FAX,C,20,0,"nullable,updateable,setnull,shownull"
BILL_ADDRESS1,C,40,0,"nullable,updateable,setnull,shownull"
BILL_ADDRESS2,C,40,0,"nullable,updateable,setnull,shownull"
BILL_CITY,C,20,0,"nullable,updateable,setnull,shownull"
BILL_STATE_REGION,C,20,0,"nullable,updateable,setnull,shownull"
BILL_POSTAL_CODE,C,10,0,"nullable,updateable,setnull,shownull"
BILL_COUNTRY,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_ADDRESS1,C,40,0,"nullable,updateable,setnull,shownull"
SHIP_ADDRESS2,C,40,0,"nullable,updateable,setnull,shownull"
SHIP_CITY,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_STATE_REGION,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_POSTAL_CODE,C,10,0,"nullable,updateable,setnull,shownull"
SHIP_COUNTRY,C,20,0,"nullable,updateable,setnull,shownull"
SHIP_SAME,L,1,0,"notnullable,updateable,initialize"
EMAIL,C,60,0,"nullable,updateable,setnull,shownull"
'then you can display
ui_msg_box("Table Information", ti.xml)
'or save for examination
save_to_file(ti.xml,"P:\test\mdb customer table.txt")

conn.close()

[Farzadalpa]

Re: Excel Sql connection

I don't think the problem is Excel limits . I can dump the data to table using "rs.toDbf() " but the table structure is wrong. all fields are at max length.
When I query the Excel connection I get a reslutset with no error, But the "tableinfo" of the resultset is wrong .The "tableinfo.DbfRowsyntax" property is wrong and I cant figure out how to set it.
Please let my know if there is way to set it.
There is a parameter in "conn.Excecue([Statement as c],[scripte as c],[referenceTableinfo as Sql::tableinfo]) .I don't how to set this tableinfo parameter .
At this point I am considering giving up this approach and use operations ,which I really don't want to do when I see all this wonderful possibilities offered by Excel sql connection.
Only if Alpha worked as advertised or the help documents actually showed how to use the these . I am not smart enough to get the one line explanations in the help documents .
Some other manuals do a lot more detailed explanations and even go as far as having examples, but I guess if Alpha did that then it would be too easy and every body could figure things out.
Thanks again ,I appreciate any suggestions.
Nick.

[Ted Giles]

Re: Excel Sql connection

Depending on the version of Excel, there are volume constraints.
Check the Excel manual.

[Farzadalpa]

Re: Excel Sql connection

Stan, Thank you bout the connectiontimeout did not change any thing
I tried to use the rs.Toopentable() ,I get the following error " 1087 Enter\Begin failed when converting resultset to DBF".
I set the sizetofit=.t. for the connection .I checked it in the debug window the connections sizeTofit is .t. and the column width is correct, but when the query is executed in the resultset sizeTofit is .f. and the columns widths are set to Maximum (255) .May this is the problem.Why the resultset dose not conform to the connections tableInfo.
Thanks again for any help.
Nick

[Stan Mathews]

Re: Excel Sql connection

Actually I think it should be


cn.ConnectionTimeout = "60" 'or some number here as a string

my bad.

[Farzadalpa]

Re: Excel Sql connection

Thanks, I will try it.
Nick

[Stan Mathews]

Re: Excel Sql connection

Since your script is hanging and not generating an error I wonder if the connection timeout setting should be adjusted.

cn.ConnectionTimeout = 60 'some number here
cn.PortableSQLEnabled = .t.

Timeout values are "Default", "Infinite", or a string containing an integer specifying the timeout in seconds. Must be set prior to opening the connection.

[Stan Mathews]

Re: Excel Sql connection

You didn't say every file you tried to import from an Excel data source failed. I thought you meant only one. I was thinking you might have results from an append after rs.ToDbf() that would lead you discover the cause of the failure.

[Farzadalpa]

Re: Excel Sql connection

Stan ,
The filed names in the excel date source can change ("but the data is the same ") With a AlphaAdo connection I am able to let the user pick the new name and use the new name in the
Sql "Select new_feild_name as old_feild_name" query and populate my table from the query .Simply just: 1) make Sql query , 2) run it ,3) loop through the results and populate the table.
To do this with import() and append() is complicated and clunky.