Excel Sql connection

[Farzadalpa]

Hello All.
I am trying to append some data from an excel sheet to a table .I make Sql connection to the excel sheet ,run a "Select" query and then loop through resultset and enter the new records .
The query runs fine and I get the resultset with no errors ,but Alpha hangs when I loop through the results after entering 400 to 700 records .
I Can not figure out whats wrong .Any help or guidance is truly appreciated, I have spent days on the problem.
Here is my code

DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("{A5API='Excel',A5Syntax='Excel',FileName='C:\user_private\os_main.xlsx', A5ExcelVersion=2007}")
if flagResult = .f. then 
	ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
	end 
end if
cn.PortableSQLEnabled = .t. 
dim sqlStatement as c 
sqlStatement = <<%sql%
SELECT src.[Supplier SKU] AS Supplier_SKU, src.Description, src.[Unity Nick, Inc# Inventory on hand] AS Unity_Nick_Inc_Inventory_on_hand  FROM [Sheet0$] src    ORDER BY src.[Supplier SKU]
%sql%
'Execute the Query
flagResult = cn.Execute(sqlStatement)
if flagResult = .f. then 
	ui_msg_box("Error",cn.CallResult.text)
	cn.close()
	end 
end if 
dim rs as sql::resultset
rs = cn.ResultSet
dim flagLoop as l 
flagLoop = .t.
dim xls_src as p
xls_src=table.open("os_main") 
xls_src.zap(.t.)
xls_src.pack()
while flagLoop 
	on error goto close_script
	xls_src.enter_begin()
		xls_src.partner_sku=right(alltrim(rs.Data(1)),20)
		xls_src.option_name=right(alltrim(rs.Data(2)),56)
		xls_src.quantity_on_hand=val(alltrim(rs.Data(3)))
	xls_src.enter_end()
	on error goto 0
	flagLoop = rs.NextRow()
end While
debug(1)
cn.close()
xls_src.close()

end
close_script:
msgbox("",error_text_get())
xls_src.enter_end(.f.)	
xls_src.close()
cn.close()

Thanks Nick.

[Farzadalpa]

Re: Excel Sql connection

Hello again ,
I have not been able to fix this problem.
Is there a limit to the number of records that can be added to a table using " Enter_begin<> Enter_end " code.
I tried to copy about 23000 records from a table to another and the Alpha crashed .After reopening Alpha I checked the destination table and noticed that only about 1800
records were entered.
Could there be a problem with my table? How can I find out.
Thanks again,
Nick

[Stan Mathews]

Re: Excel Sql connection

Look at the last record imported. Did it fill the table record completely or did it stop on some field in the record? Look at the Excel file for the same record and see if there is some anomaly in the data. Are you creating a new table or importing to an existing table? If existing, are there field rules for the table which might be being violated or unable to compute for the Excel data.

[Farzadalpa]

Re: Excel Sql connection

Thanks Stan, I am trying to add an existing table .This table has no field rules, it only has 2 indexes .I am able to use " rs.ToDbf() " to dump the data to a new table.
I dumped the excel date to a new table with no problem, then I tried to enter records from the new table to the original table just to see if the problem is with adding the records ,using a simple "Enter_Begin <> End " script .
The at this point Alpha crashed .

[Stan Mathews]

Re: Excel Sql connection

I tried to enter records from the new table to the original table just to see if the problem is with adding the records ,using a simple "Enter_Begin <> End " script .

Why not just try an append operation?

[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.

[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.

[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.

[Farzadalpa]

Re: Excel Sql connection

Thanks, I will try it.
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

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

[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

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.

[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()

[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.