| Author |
Comment/Response |
ubgr
|
 02/13/12 1:40pm
Hi,
I'm trying to transfer a dataset from an excel-file to an hsql database using the demo-database in DatabaseLink. The file I'm trying to import is the World Agricultural Supply and Demand-data from USDA/WOAB (http://www.fas.usda.gov/psdonline/psdDownload.aspx)
The data is 448 620 rows, divided into 12 columns.
First, I'm importing the xls-data using wasde=Import["C:\Mydir\psd.xls"]
Then I'm creating a table in the example database by
SQLCreateTable[conn, "WASDE", {SQLColumn["Commodity_ID",
"DataTypeName" -> "INTEGER"],
SQLColumn["Commodity_Description", "DataTypeName" -> "VARCHAR"],
SQLColumn["Country_Code", "DataTypeName" -> "VARCHAR"],
SQLColumn["Country_Name", "DataTypeName" -> "VARCHAR"],
SQLColumn["Market_Year", "DataTypeName" -> "INTEGER"],
SQLColumn["Report_Year", "DataTypeName" -> "INTEGER"],
SQLColumn["Report_Month", "DataTypeName" -> "INTEGER"],
SQLColumn["Attribute_ID", "DataTypeName" -> "INTEGER"],
SQLColumn["Attribute_Description", "DataTypeName" -> "VARCHAR"],
SQLColumn["Unit_ID", "DataTypeName" -> "INTEGER"],
SQLColumn["Unit_Description", "DataTypeName" -> "VARCHAR"],
SQLColumn["Value", "DataTypeName" -> "DOUBLE"]}]
Then I'm construction an array of the columns-names repeated 448620 times:
arr = Array[{"Commodity_ID", "Commodity_Description", "Country_Code",
"Country_Name", "Market_Year", "Report_Year", "Report_Month",
"Attribute_ID", "Attribute_Description", "Unit_ID",
"Unit_Description", "Value"}, 448620]
Last I'm trying to insert the data (wasde) with
SQLInsert[conn, "WASDE", {arg}, {wasde60}]
But it doesn't work. The message I'm getting is very large, so mathematica only shows some of it. It looks like this:
SQLInsert[
SQLConnection["demo", 1, "Open",
"TransactionIsolationLevel" ->
"ReadCommitted"], "WASDE", {{{"Commodity_ID", <<10>>, "Value"}[
1], <<10843>>, <<1>>}}, {{{{430000., "Barley", "AF",
"Afghanistan", 1970., 2006.,
Anyone see something wrong in the way I'm doing it? Is it impossible to use the demo-database to do this? If so - how do I create a new database (I don't have any experience with hsql.
Thank you.
URL: , |
|
