Importing Data

17 02 2013

The workaround …

Accounting data are to be imported on an iOS-Device.

The data source is a CSV file exported from Microsoft Excel or Microsoft Access.

If you want to check out same results or need data for tests download the file here

SAMPLE-RECORDS-ACC-6Years-US.csv

It contains accounting data with account numbers, account names, and accounting values over 6 years and look like this:

20130110-131444.jpg

As you can see CSV files are readable files with single lines where the data are separated by a delimiter (a semi-colon or a tabulator) and thus ordered in columns.
Nearly every application is capable of importing data from those files as it is a received standard all over the world.

The data receiving applications are Tap Forms HD for the iPad (Tap Zapp Software Inc., Canada) and Numbers (the spreadsheet part of Apple’s iWork-Suite).

The device is an Apple iPad 3 with 64 GB storage capacity.

If you want to import data to Tap Forms HD or Numbers you should know something about the formatting options for numbers and the encoding of text.

Formatting of numbers in Microsoft Excel, Access …

20130110-133019.jpg

Excel
Format cells with numerical content as numbers (not as currency).
Adjust the decimal places and set the 1000-separator.

Access
There are lots of options when exporting data from Microsoft Access to a CSV file. Use ‘Export to Textfile’ to set up the appropriate values. The settings can be saved for further exports.

Encoding …

Differences between Ansi and UTF-8

Summary
1. UTF-8 is a widely used encoding while ANSI is an obsolete encoding scheme
2. ANSI uses a single byte while UTF-8 is a multibyte encoding scheme
3. UTF-8 can represent a wide variety of characters while ANSI is pretty limited
4. UTF-8 code points are standardized while ANSI has many different versions

The import …

Just importing data is unprofitable arts. So in both applications the mean value of the 6-years accounting data is calculated as an example. For Year6 all values are zero so the mean value is taken just from the first 5 years.

Numbers (Apple)

As it is not possible to directly access data in iCloud you must use the Numbers functionality ‘Copy from WebDAV‘ to get the import file. Unfortunately Dropbox does not support WebDAV access while Tap Forms HD does. If you have the file elsewhere you may use the ‘Open in‘ command. It works also in the iOS Dropbox app. What you should know is that the Dropbox app does not show you the contents of a CSV file (App version 2.0.2).

If the file is downloaded the import starts directly and you don’t have to set any options.
It took 12.5 sec to scrape the CSV plate. It took another 15 sec to kick it into iCloud.
And it was a bit crazy to drag the yellow ‘Fill’ rectangle over 389 rows.

20130110-145226.jpg

Tap Forms HD (Tap Zapp Software Inc., Canada)

There is an easy way to prepare for an import by letting Tap Forms HD automatically create a table. In this case all fields are imported with field type TEXT and thus cannot be included in calculations. Tap Forms HD does not first analyze the data and then set the correct field type.

Note
Also Apple’s Numbers cannot identify field types. After importing into Numbers the relating columns have to be formatted as numbers before including them in formulas.

As an alternative you do this automatic import and after its finished change the field type for numeric values from Text into Number, delete all records and restart the import.

Here are the steps to do the import with Tap Forms HD.

20130110-173934.jpg

Summary …

There are not many rows imported in Numbers nevertheless Numbers reacts a bit spastic when adjusting the column width, setting any formatting or adjusting the column width.

See my blog

To the limits

A benefit of Numbers is the free-style formatting, printing all data and a large number of built-in functions for more complicated calculations. Import options are not available following Apple’s KISS principle. Usability problems already come up with more than 300 records. Numerical values can be compared in charts.

Importing into the Tap Forms HD database (SQLite based) is recommended for a larger amount of data and usual operations on numerical values. The SQLite database working in the background does not have any performance problems even with the 65535 records I imported in my test-environment (Performance: 2 min/1000 records). As i wrote in my blog ‘To the limits’ Apple’s Numbers crashed long before reaching this limit.

In Tap Forms HD queries (respectively Searches) can be executed and saved for further usage. This feature is not available in Numbers but often needed to reduce the total contents to the relevant records.
Printing of records is available but currently just for a single record. You might say printing is old-fashioned but if you extracted relevant records (e.g. all records starting with 8 in the account number (these are accounts related to items like Sales in the Profit and Loss Account) or account values greater than 500.000) you usually want to print them out as a spreadsheet and review the data without the need of any device.

20130111-052936.jpg

Tap Zapp Software, the developer of Tap Forms HD, is highly innovative and its just a question of time until app updates bridge some current gaps.

Can both applications work hand in hand?

Yes, they can. Use Tap Forms HD to define a search rule (query), export the subset as CSV file, and import it in Numbers for further processing.

Technical Note …

There is one feature of Tap Forms that allows to include field-type tags in the header row to tell Tap Forms which fields are numbers and which are text or dates or other field types.

For example if the first header row was as follows, Tap Forms would correctly create the form with the correct types:

Account[text];Name[text];Year1[number];Year2[number];Year3[number];Year4[number];Year5[number];Year6[number];Item[text];Name[text];Updated[date];Kto[number]

(WordPress deletes all pointed brackets from blogs so I used square brackets; the square brackets must be replaced by pointed brackets)

Although [text] is the default if it’s not specified in the column. When importing dates the Tap Forms date import format must match the format of the date in the CSV file.

Here is a list of all field-type tags for Tap Forms HD.
It can be used when migrating from a previous database app to Tap Forms HD.

Text… [text]
Number… [number]
Date… [date]
Time… [time]
Date + Time… [date_time]
Audio Recording…
Calculation… [calc]
Check Mark… [check_mark]
Contact… [contact]
Drawing… [draw]
Email… [email]
File Attachment… [file]
Link to Form… [form]
Location… [location_address]
Location… [location_latitude]
Location… [location_longitude]
Location… [location]
Note… [note]
Phone… Number[phone]
Photo… [photo]
Rating… [rating]
Section Heading… [section]
Web Site… [web_site]

Related Links …

DBMS on iOS devices (1)

DBMS on iOS devices (2)

DBMS on iOS devices (3)

DBMS on iOS devices (4)

DBMS on iOS devices (5)

Form Access To Tap Forms

Databases and Mind Maps

Thanks for visiting http://iNotes4You.com.


Actions

Information




%d bloggers like this: