Importing Data from Access and Excel into Arca - Part II

Important Update: Version 1.5 and later of the Arca Database Browser application can now import CSV files created in Excel or Access directly, using the FILE->IMPORT->TABLE FROM CSV FILE menu. The ability to export data in CSV format is now also available. Arca Database Browser is a free download. This technote is maintained for historical reasons, and for developers that want a better understanding of the CSV file format.

This technote is a sequence to AR003 , showing how to import data in formatted text files into Arca tables.

In the previous technote we prepared a CSV file containing all the records exported from an Access database. In order to bring these records into Arca we need to first create a database file, and then create the table. The following shows the Lingo commands you can use to achieve this. Feel free to type them in the message window, or use it in a handler in your code. We are not checking for errors in order to keep the code easier to follow:

--first, open the xtra

myDB = new (xtra "arca")

--now create the database file, check result in message window

put myDB.createDB("D:\databasefile")

--now create the table to hold our records. the line below will

--create a table exactly like the CUSTOMERS table we exported

--from MS Access

put myDB.executeSQL("CREATE TABLE customers(CustomerID text, CompanyName text, ContactName text, ContactTitle text, Address text, City text, Region text, PostalCode text, Country text, Phone text, Fax text)")

--now simply import the data from our csv text file, using the comma

--as a delimiter character

--IMPORTANT: the COPY SQL command below is NOT available in Arca 2, only in version 1.x

--Users of Arca 2 should use the Arca Database Browser tool, or parse the CSV file using

--Lingo - see routines at http://www.mediamacros.com/item/item-1006687458/

put myDB.executeSQL("COPY customers FROM 'D:\export.csv' USING DELIMITERS ',' ")

That's it. You should now be able to use the Arca Database Browser to open your database file and see the contents of your records, or maybe edit them further. Here is a screenshot of how they will look like:

Of couse this is only one method of bringing data into Arca. For some projects it will be better to read and parse a text (or XML) file using FileIO or the XML parser, and insert records directly from Lingo. It all depends on the characteristics of your project and the original data source. And don't forget the Arca Database Browser, which is an ideal tool to collect and prepare data directly into an Arca database. It is very simple to use, accepts copy and paste shortcuts and does not require any programming knowledge, so you can distribute it to your customers for data preparation as well.

Important: the COPY command is not supported on MacOS 8 and 9 due to differences in the character set. Under MacOSX you must be sure you are using a text file saved with the Windows default (Western) encoding. The TextEdit application in MacOSX can convert text files from Mac Roman to Western encoding.