Mathematica 9 is now available
Student Support Forum
-----
Student Support Forum: 'Using SQLInsert' topicStudent Support Forum > General > Archives > "Using SQLInsert"

Help | Reply To Topic
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: ,
Help | Reply To Topic