3
$\begingroup$

I need to examine the bigdata file with a database/DBMS and have used MS Office 365/Access successfully so to do, import and query. I have also tried, many times, to open a MMA 10.4 database link connection to examine the CSV file (and also the .accdb file resulting from success with Access) under Windows 7.

I never had any success using MMA and choosing either SQLite or HSQL engines. OpenSQLConnection[] gets me into and out of a multistep dialog OK. I seem to have achieved a connection, but it's never one that ever satisfies MMA. For example, SQLTableNames and SQLTableInformation (to list column headings) always fail to produce any correct answers.

Do I need to import then export the .CSV file in a format the MMA is happier with during the multi-step dialog? Some file format other than .csv or other than .accdb? Do I need to specify some properties or identify a "catalog" during the OpenSQLConnection[] dialog? TIA for a reply, Lew R

Per Bill, I scraped the header row and first five data rows of the database. See attached screen shot:

ShangraLa test table

Since the way to upload 'live' small .csv and .xlsl files is not obvious to me (maybe any file uploading is not welcome on this site), I will instead ponder how best to provide them. Maybe uploading the files to my website is the best answer.

Link to Google Drive's publicly shared folder containing 3 small files (2 csv, 1 xlsx) and short MMA notebook. Try downloading small test files and short ShangraLa.nb code for testing.

Edit: Here's the mini CSV example as plain text:

Site_Name,Parameter,DateTime_LST,Value,Unit
Shangra-LA,AmbT,1/1/1990 0:00,4.7,degC
Shangra-LA,AmbT,1/1/1990 1:00,4.8,degC
Shangra-LA,AmbT,1/1/1990 2:00,4.9,degC
Shangra-LA,AmbT,1/1/1990 3:00,4,degC
Shangra-LA,AmbT,1/1/1990 4:00,3.3,degC
Improve this question
$\endgroup$
16
  • 3
    $\begingroup$ First thing to try: Hop into Office, scrape the first ten lines of the database and save that into exactly the same kind of file(s). See if Mathematica can happily pull in that tiny database. If so then that points at the size of bigdata. But if Mathematica can't pull in the tiny database then you would be able to post tiny sanitized example files somewhere so someone can grab the copies and try to figure out what you need to tell Mathematica to successfully use the (small) database. $\endgroup$
    – Bill
    Mar 9, 2017 at 22:29
  • $\begingroup$ Bill, mini database image added, per your suggestion. I ran it as a .csv file $\endgroup$
    – Lew R
    Mar 10, 2017 at 4:23
  • $\begingroup$ Bill, a scraped, mini, database image added, per your suggestion. I entered it as a .csv file using OpenSQLConnection[ ], specified HSQL, and got a True connection Nothing good happened after that. SQLTableNames gave an empty string { }. SQLTableInformation output (wrong) header information for MMA's sample database 'demo'. I can post a screen shot of the notebook's execution if helpful, with a few comments at the end. $\endgroup$
    – Lew R
    Mar 10, 2017 at 4:43
  • $\begingroup$ For Bill and Nasser, OK, I will attempt to post three 'live' versions of the mini database, all created from MS Excel 2016, itself loaded with the original 'bigdata' .csv file. Their names will be; MiniLa.csv, ShangraLa.csv, MiniLa.xlsx. This may take me a while, stumbling to upload them. $\endgroup$
    – Lew R
    Mar 10, 2017 at 6:11
  • $\begingroup$ For Bill and Nasser; The last two red lines added above that begin $\endgroup$
    – Lew R
    Mar 10, 2017 at 18:09

2 Answers 2

Reset to default
3
$\begingroup$

So more and more I think what you're trying to do is simply infeasible via Mathematica.

The bundled Microsoft Access driver (UCanAccess) won't touch Excel and the old Microsoft Excel driver has potentially been removed. This is, I think, why we get errors when trying to build a JDBC link to the file using either of those drivers.

But one alternative, if, indeed, your data isn't too massive, is to simply use Mathematica to move your data over to SQLite:

I'm going to assume you already have a file, but for my use I'll build some random CSV file:

$dbfile = CreateFile[];

$data = RandomReal[1, {100, 100}];
$keys = ToUpperCase@
     StringJoin@{"key_", 
       Alphabet[][[If[# < 26, {0}, {0, 1}] + 
          IntegerDigits[#, 26]]]} & /@ Range[Length@$data[[1]]];
Export[$dbfile, Prepend[$keys]@Transpose@$data, "CSV"];

Then we simply dump this to a random SQL database:

In[414]:= $sqldb = CreateFile[];
$conn =
  OpenSQLConnection[
   JDBC[
    "SQLite",
    $sqldb
    ]
   ];

In[416]:= $dbdata = Transpose@Import[$dbfile];
$dbtable = FileBaseName@$dbfile;
sqlColFormat[{colName_, data___}] :=

  With[{heads = DeleteDuplicates@Map[Head, {data}]},
   SQLColumn[colName,
    "DataTypeName" -> 
     If[Length@heads > 1, "Expr", ToString@First@heads]
    ]
   ];

In[419]:= SQLCreateTable[$conn, SQLTable[$dbtable],
  sqlColFormat /@ $dbdata
  ];

In[421]:= SQLColumns[$conn, $dbtable] // Take[#, 10] &

Out[421]= {SQLColumn[{"m000068920271", "KEY_A"}, 
  "DataTypeName" -> "REAL", "DataLength" -> 2000000000, 
  "Default" -> Null, "Nullable" -> 0], 
 SQLColumn[{"m000068920271", "KEY_B"}, "DataTypeName" -> "REAL", 
  "DataLength" -> 2000000000, "Default" -> Null, "Nullable" -> 0], 
 SQLColumn[{"m000068920271", "KEY_C"}, "DataTypeName" -> "REAL", 
  "DataLength" -> 2000000000, "Default" -> Null, "Nullable" -> 0], 
 SQLColumn[{"m000068920271", "KEY_D"}, "DataTypeName" -> "REAL", 
  "DataLength" -> 2000000000, "Default" -> Null, "Nullable" -> 0], 
 SQLColumn[{"m000068920271", "KEY_E"}, "DataTypeName" -> "REAL", 
  "DataLength" -> 2000000000, "Default" -> Null, "Nullable" -> 0], 
 SQLColumn[{"m000068920271", "KEY_F"}, "DataTypeName" -> "REAL", 
  "DataLength" -> 2000000000, "Default" -> Null, "Nullable" -> 0], 
 SQLColumn[{"m000068920271", "KEY_G"}, "DataTypeName" -> "REAL", 
  "DataLength" -> 2000000000, "Default" -> Null, "Nullable" -> 0], 
 SQLColumn[{"m000068920271", "KEY_H"}, "DataTypeName" -> "REAL", 
  "DataLength" -> 2000000000, "Default" -> Null, "Nullable" -> 0], 
 SQLColumn[{"m000068920271", "KEY_I"}, "DataTypeName" -> "REAL", 
  "DataLength" -> 2000000000, "Default" -> Null, "Nullable" -> 0], 
 SQLColumn[{"m000068920271", "KEY_J"}, "DataTypeName" -> "REAL", 
  "DataLength" -> 2000000000, "Default" -> Null, "Nullable" -> 0]}

And now you can use all of the standard SQL stuff on this. At one point I wrote some stuff for manipulating SQL databases with DatabaseLink (using an OOP framework I've since abandoned) and if I remember correctly it's not bad but also not totally smooth.

Improve this answer
$\endgroup$
0
$\begingroup$

The answer is to use MS Office 365 program Access. It imports the .csv file with no difficulty and answers key operational querys. MMA efforts to get a working connection to SQLite that will also import the bigdata file and answer same querys to continue, with minimal priority.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.