In the previous chapter on InfoSet Import/Export we've seen that you can export and import entire Alventis tables with their accompanying InfoView forms. This is the best way to share information between multiple copies of Alventis, but it won't be of much help if you want Alventis to share data with any other application: to share something they need to "speak the same language", which in this particular case means sharing a common data format. Data Export gives Alventis the ability to export its data in one of the most standard data exchange formats, and Data Import allows it to read several popular formats of data that may be coming from other applications.
Data Export. Clicking the Data Export button launches the Data Export single-screen Wizard. It allows you to export a table of your choice into a delimited text file.
The Database/Table combo at the very top lets you select the table whose data you would like to export. As soon as you make a selection, the rest of the form is automatically filled-out according to the contents of that table.
The Export Field grid on the left lists all fields of the table.
The exported values will be written to the output file in the order in which the fields are listed in the Export Field grid. You can adjust this order very easily by dragging fields up and down in the grid with the mouse (even several at a time).
The Delimiter group to the right of the grid lets you select what delimiter you would like to use for the destination file. The most common ones (Comma, Semicolon, Tab) are available to you by simply selecting the appropriate radio button. You can use some other delimiter of your choice by specifying it in the Character Text Box. You can input either a literal character or its numerical ASCII code, which you can enter in decimal or hexadecimal format (0x2F or $2F, for example).
Text Qualifier is the Quote character you'd like to enclose values in, when necessary. Normally, this would be a double or single quote character, but you can choose whatever you want in the same way it has been done for the Delimiter Character above.
Decimal Separator will be used in exported numerical values and you have a choice between the Point and the Comma.
Quoting Level specifies how "aggressively" you want your output values quoted. The options are self-explanatory.
Field Names on First Row checkbox allows you to include the field names as the first row of the exported text file.
Memo Options control how Memo text will be exported, if at all. Alventis Memos can obviously contain many elements that cannot be exported to a plain-text file, e.g., formatting, pictures. Alventis can only export the textual contents of the Memos. Many applications cannot import multi-line Memos from delimited text files. Sadly, even Alventis finds itself in this category. For such applications you can export the entire Memo text as a single long line by stripping line breaks from it. Other apps may have trouble with Tab character embedded in Memo text – Strip Tabs option may help those. Finally, even Microsoft Excel and Microsoft Access do not share the same exact format when it comes to Memos – and that, despite being members of the very same Office suite! This is why you find an option specifically tailored for making the exported file compatible with Excel, while the last option is most generic (and should be compatible with Access).
Data Formats allow you to select how specific types of data will get exported. You can select one of the pre-defined formats or type whatever you want it to be following the common formatting convention.
The Preview displays the first several records exactly as they would get exported using the current settings. It is auto-updated whenever you change any of the above options.
Export to Text Box specifies the filename of the destination file where the data will be saved. You can click the dotted button to browse to an appropriate folder.
The Export button does the deed.
We have attempted to select the most common defaults for all configuration options in the dialog. Ultimately though, there is no "correct" set of values. Whatever output you end up with, it must be "understood" and correctly imported by whatever application happens to be the "recipient" of the data. If you know that application's import requirements, this should give you a strong indication of what settings you need to use to make it "happy".
Data Import. Clicking the Data Import button launches the Data Import Wizard. It allows you to import data from a variety of formats into an Alventis table of your choice. Unlike other one-step single-screen Wizards used in Alventis, this one is a "real" multi-step beast of a Wizard. The bad news is that there are so many screens and options that explaining them all in detail would double the size of this Guide. The good news is that it is fairly intuitive, so you should be able to find your way through it quite easily. We will therefore mention only some selected facts and issues here and leave the rest to your intuition.
Before we go any further, you should remember that an Import operation will modify your local data. If you ever make a mistake, such modification can lead to drastic results, including data loss. You should therefore have a secure recent backup of your data before you attempt importing something into it.
The Wizard starts by asking you for an Import Destination, which is of course the Database and Table into which you would like to import some data.
Once you have selected the desired Destination, clicking Next brings you to the Import Source page of the Wizard depicted below:
You are presented with a choice of 6 import formats:
The Template that the lower portion of the dialog refers to is simply a saved import setup. You can save and re-load such setup Templates if you frequently make the same kind of import.
The next step of the Wizard will depend on which input format you have selected. The basic task you will be presented with is explaining to the Wizard what incoming data should go into what fields of the Destination table. The snapshot below shows the interface you would use when importing from an Excel spreadsheet.
Once this is done, you arrive to the next screen where you can specify the Data Formats that will be used to interpret the incoming data, when necessary.
The Data Values section lets you make some adjustments to how the destination fields are filled out. You can, for example, auto-fill some field's values using the Generator value/step controls. Each imported record will have the specified values placed in the field of your choice. For example, if you were to set Generator value/step to 10 and 5 respectively for field SomeNumber of the Destination table, the imported records would have this field's values set to: 15, 20, 25, 30, etc. All of this is specified on a per-field basis: select the field on the left, and set whatever options you want for it, if any, on the right.
You can specify what value you want in place of Null values or what quote characters you want to insert. You can even perform automatic search-and-replace operations on the incoming data.
The final, 3rd Step of the Wizard lets you specify how many records you want to import, as well as some logging options. You can save the error log (should there be any import errors) to a file of your choice. If you elect to not save it to a file, you will be presented with a list of errors on-screen.
The Import Mode tab sheet may be the most important and complicated step. You should read our general discussion of InfoSet Import Modes for a conceptual understanding of what is going on and what possibilities exist. Unlike the InfoSet Import which offers you a choice of only the Modes that Alventis has determined as valid and reasonable, the Data Import Wizard always offers you the full spectrum of options. Whatever records it does not manage to import (most probably due to Key violations: two records with the same ID value), it will simply list in the error log/list you will be presented with.
The Key Columns would normally be set to just the ID field since this is the key of all Alventis tables. This is used by some of the above Import Modes to figure out which records are matching and which aren't.
It should be noted that you can import multi-line plain-text Memo fields – but only from Microsoft Access databases.