Uploading External Data

You can edit an iDB backup dump, a CSV file, append more data to it (for the purpose of migrating from other applications), and then restore it back to iDB.

There are many, many ways to edit a CSV file. Here is one way:

Suppose we want to migrate some credit card information we have available elsewhere. And supposed it is available in CSV format:
 

1. Create a new database, type: Credit Card, and name: 'My Cards':

2. Put a single record in it. It is not necessary, but it will help as a reference:

3. Save the record. Then launch iDB Server: Console->Server:

4. Now the server is up and running. Connect from a desktop browser. Get a backup dump:

5. This particular browser just saves the file to Desktop. (See your browser settings if it does something else; like spawning an external applications, or displaying the content in browser window, etc).

In our case the dump file is named:

We don't want to modify it. It is never a good idea to modify an original dump, just in case it is needed later. Instead, let's make a copy:

Lets open 'myIdbDump.csv' in a text editor, and search for "My Cards" just to see what it looks like:

In iDB dumps, a database section starts with string: "###IDB-DUMP###", followed by some other info we won't get into right now. That row also includes the database name, in this case "My Cards".

The first two rows of a database section are header information.

(Btw, do not *ever* modify a database header, the first two rows of a database section. That is a sure way to break iDB database internals.)

Starting from 3rd row all the way to the first blank line is the data. This database has 3 rows only; 2 rows for the header, and 1 more row for the record we just created.

Each row is terminated by a "eol" (end-of-line) field, indicating the end of a record.

Second row of a DB-header holds the field names. In the case above, they are: 'name' (record name, or title), 'cardno' (credit card number), 'expiration', 'ctype' (credit card type), 'code' (ccv, or security code), 'web' (URL of the card vendor), 'login', 'passwd', 'phone', 'notes', terminated by 'eol'...

Note that in a dump file, each line does NOT necessarily represent one record. Notice the 5 lines right above this database, starting with "United Milage Plus". Those 5 lines all together represent a single record, ending with "eol", marking the end of the record.
 

6. All that was investigative work so far. Close the text editor, and open the same file in Numbers (Mac) this time:

The usage of Number as a CSV editor is deliberate here. Since iDB has launched, we have received overwhelming number of requests to make this work under 'Numbers'. Normally it wouldn't be our choice, but it seems to be the customers' choice, therefore we will go with it here. 'Numbers' have some issues though, regardless... Also, the choice of Credit Card type as an example is deliberate here; to exhibit a problem that will bite us in a minute.

Let's make this whole thing work while pointing out the potential problems. We will better address those issues in the next release of iDB. Duly noted: customers want 'Numbers' as a CSV editor, and we have to make it work flawlessly.

Lets search for our database name: 'My Cards'. It is found on row '212', column 'H' in the image above. And there sits our 3 lines of dump for that DB.
 

7. Now we will go ahead and delete all other database sections, leaving only ours. This way we will restore only the single database we are interested in, while not disturbing others. After removing all irrelevant rows here is what it looks like:
 

8. It is time to point out the first problem above: In cell 'C3' (row '3', column 'C'), the data was supposed to be "11/12". Even though it was originally a fully quoted string when iDB dumped it, 'Numbers' unilaterally converted it into 'date' format, rather than leaving it as what it is supposed to be, a 'string'.

iDB fully quotes all data fields, trying to protect them from such misinterpretations. Regardless, we have been just bitten by this idiosyncrasy. the choice of Credit Card type was deliberate for this exercise to demonstrate such potential problems.

We will go ahead and fix it manually by entering '11/12 (single-quote 11/12) to that cell.
 

9. Assuming we have the migration data available in another CSV file, let's cut-and-paste and append it under 'My Cards' database as additional records. We won't explain how to do it here. But if you do not know how to cut-and-paste CSV data from another spreadsheet and how to line them up under correct field names, we strongly recommend you *NOT* to exercise this process. Perhaps someone a bit more computer-savvy should help you.

Make sure you add migrated data into correct columns. Use field names on row #2 as a reference.

The last column of the 'fields' row, "eol", must repeat in all following data rows. It is imperative the last field of a record to be "eol":

iDB interprets the first blank line as the end of a database dump. Hence, we left one blank row at the end. Save the file (in 'Number' you should 'export' it into CSV 'format. We just exported it into a file: myIdbDump2.csv).
 

10. Lets look at what 'Number' has saved for us. Open the exported file in a text editor:

There are some issues:

Let fix it. Assuming our saved file is 'myIdbDump2.csv', run this command in a terminal window:

It is a bit cryptic, and apologies for the inconvenience. Technically inclined users may easily tell this command is fixing messed-up end-of-records.

iDB version 1.0.4 and above are conscious about the quirks of these CSV editors, and would tolarate this particular problem. It is very likely you are on a later version; in which case you may skip this cryptic 'sed' command.

The command above creates a new file, 'myIdbDump3.csv' while addressing the problems pointed out above.
 

11. FOR WINDOWS USERS USING iDB version 1.0.3 OR EARLIER:

'sed' is native to Mac, Unix, Linux, FreeBSD, etc. However, you may not have 'sed' on your Windows machine. If that's the case (very likely), you can find a copy of 'sed' at: http://gnuwin32.sourceforge.net/packages/sed.htm.

Or, simply call the URL: http://gnuwin32.sourceforge.net/downlinks/sed.php. It will download 'sed' to your Windows machine. Its default installation is: "C:\Program Files\GnuWin32" unless you change it.

Assuming you were using Excel to edit the CSV file 'myiDbDump2.csv' in 'C:\Downloads' directory:

- Open a terminal window         (Start->Run, type "cmd", click "OK")
- Go to the directory where 'myIdbDump2.csv' is, and type the above command. Example:

12. Restore 'myiDbDump3.csv', and go to 'My Cards' database:
 

SUMMARY:

There are just many ways to edit a CSV file. It seems like 'Notes' and 'Excel' are the customers choice. However both have flaws as a CSV editor. To remedy:

1. Make sure originally 'string' however 'numeric-looking' data (supposed to remain as 'string') does not get processed into a date format; or an actual numerical calculation doesn't occur on it, messing up a field by modifying its content.

   This is usually not a concern, because normally you won't be editing over existing iDB data, rather, bringing (migrating) your own data. Presumably you know what you are doing and by the time you are ready to save, you are happy with how your data looks like.

2. Here is the real issue: If you are on iDB 1.0.3 or earlier version, once the migrated data is saved by 'Numbers' or 'Excel', run the following command:
   sed -e "s/eol,*$/eol/" -e "s/^,*$//" myDumpFile1.csv > myDumpFile2.csv

   in order to fix potential end-of-record and end-of-database problems.

However, we strongly recommend you to update to the latest/greatest version of iDB on AppStore. You may want to read "updating to a new version" before running an update on AppStore.