Home   -   Computerkurs   DemoMatrix   Editpointstatic   Folderwatcher   Gipsydrive   Licenses   Shrinkseries   V4   More...  

About   User Manual   Cookbook   Issues  

Lexware Import/Export   Spreadsheet Import   Bankingcommander   V4Net Manual   Brokerage   Deployment  

Previous page Home chapter Next page

Trilo - Downtown - V4 - Cookbook

Receipt 20091014°0123
Custom import from spreadsheets

Receipt content :
(1)   Introduction
(2)   Prepare the source spreadsheets
(2.1)   Prepare the source data
(2.2)   Prepare the source file format
(3)   Prepare your V4 company data
(4)   Run Tools Script
(4.1)   Garantee the latest Tools Script version
(4.2)   Call the import function
(4.3)   Watch the script running
(5)   Postprocess the imported data
(6)   p.s. For curious persons only

(1)   Introduction

This receipt applies to this specific customer situation: A new V4 user is setting up a new V4 company dataset and wants import article and contact data from Excel or OpenOffice spreadsheets into V4.

Custom import means, it is a dedicated routine, programmed for one specific situation.

The Import Tool works incremental, means you can run it, interrupt it, and run it then again. This also means, that after the first import, you can edit your source spreadsheet (to some degree), and run the import again, synchronizing the V4 data to the changes in the spreadsheet.

(2)   Prepare the source spreadsheets

(2.1)   Prepare the source data

The source data have to satisfy certain requirements. The better you prepare the source data, the less postprocessing you have with the imported data.

There must be a unique identifyer per record.

  -   In the articles spreadsheet, this is e.g. field "Abt.Nr.", where you have given each article a unique short code.

  -   In the contacts spreadsheet, there may be no explicit field for this purpose, so V4 builds an identifyer from the fields Anrede, Name, Vorname.

When running the import later again, and you have changed data in the spreadsheet, the changes will be synchronized to in the V4 data. Except if you have changed one of the unique identifier fields - then you will get a new record in V4.

(2.2)   Prepare the source file format

V4 can import Excel 5.0 files or CSV files.

If you spreadsheet is in Excel 5.0 format anyway, you have nothing to do here.

But you may have Excel 2007 or OpenOffice spreadsheets. So you need to save as you original file, where you choose either Excel 5.0 or comma-delimited (csv) as the save format.

You can leave your source file in the directory where it is anyway, and place the saved-as-file side-by-side to it. Later, in V4, you will need to remember this place to point to it.

We recommend to choose the name of the saved-as-file the same as the original file, only with an extension, indicating as what you have saved it. For example the following files, the first ist the original file, the second is saves-as Excel 5.0, the third is saved-as CSV:
C:\Documents and Settings\My Documents\Kontakte+Adressen.xlsx
C:\Documents and Settings\My Documents\Kontakte+Adressen.xlsx.50.xls
C:\Documents and Settings\My Documents\Kontakte+Adressen.xlsx.csv

Spreadsheets may have multiple pages. In the intermediate version, best you delete all other pages, except the one containing import data. Otherwise, V4 will import all the other pages as well.

If you choosed to saving-as CSV file, you probably have only one page anyway. This is one advantage of the csv format. But otherwise it is a little bit more complicated to configure, since you have to tell what separator and what delimiter.

(3)   Prepare your V4 company data

Make a backup! The directory containing the live V4 data, which you have to backup might look like this:

(4)   Run Tools Script

(4.1)   Garantee the latest Tools Script version

If you know, your actual V4 installation contains the latest Tools Script, you have nothing to do here, just skip this task.

But since this is a custom function, the import function in the Tools Script may frequently be re-written, as you complained a bug and we subsequently fixed it.

So the Tools Script in your local V4 installation may not contain the latest version of your custom import function. And for only one single file, it is easier (for the developer) if you fetch this single file Tools.ssl from the developers space, than to update the complete V4.

Fetch latest Tools Script from the V4 developers Subversion repositoriy. Pick that one file from the develop space and drop it into your local V4 program directory.

(4.1.1)   In the Windows Explorer, locate your V4 program dirctory, it is something like   C:\Programme\Trilo Software e.K\V4-095q4.   Remember this place for the next step. This is the place, where you have to put that Tools.ssl file.

(4.1.2)   Just click the following download link   http://downtown.trilo.de/svn/vv/trunk/vv/Tools.ssl , and save the file in the V4 program directory on your machine, the place described above. Or - if you are more curious person, and want see the place where the file comes from, go http://downtown.trilo.de/svn/vv/trunk/vv, see the complete V4 source files, and pick Tools.ssl from them.

(4.1.3)   With Windows Explorer, inspect your V4 program dirctory (the place described in 4.1.1.). Now you should find two Tools Scripts there: Tools.sdl, the compiled version from your original V4 installation and Tools.ssl, the latest source version you just fetched from the developers repository. It is important, that later you start the new second SSL one, not the first SDL old one.

The following instructions applies almost the same on two different Excel tables, one contacts table and one articles table.

(4.2)   Call the import function

Open V4. In the Start Form on the Accessories Page, find the Tools Script Button and press it.

What happens now, depends on whether (1) you have still a pristine V4 installation with a delivered Tools Script only, or (2) if you have added an updated source Tools Script to your V4 program directory. In the second case, you have to tell V4 which of the two versions you want run. Normally you want run the source version then.

Image 20091025°1342

Start the Tools Script from the Start Form and choose the articles or the contacts import. If you have added an updated source version of the Tools Script, you will see below shown dialog, before above menu appears.

Image 20091025°1343

When starting the Tools Script from the Start Form, and you have added an updated source version of the Tools Script to your V4 program directory, you are asked which version to run. Normally you want run the source version.

Image 20091025°1344

If you start the import script for the first time, you are asked to point to the source file. Remember the place where you prepared your spreadsheet, and point now to the respective Excel 5.0 or CSV file.

Image 20091025°1345

If you start the import script a second time, you are asked to run with the previously choosen source file, or if you want to change the source file.

(4.3)   Watch the script running

When the script runs, perhaps it has to create some peripheral records, e.g. needed to import contacts.

Image 20091022°0201

In the contacts import, this dialog may appears one time. Just say Yes.

Image 20091022°0202

Another dialog appearing one time in the contacts import. Please just say Yes.

Running the import will last some minutes. In the V4 Console window, you see the messages scrolling for each imported record. Moving the mouse gives you an opportunity to escape the run.

Since the import function is not heavily tested, you might run into unexpected problems. Please be patient then, and contact the developer, to give them a chance to solve the problem.

(5)   Postprocess the imported data

After the import did run, inspect the V4 company data. Such import is rarely a 1:1 thing. A spreadsheet allows much more freedom for what is in a field, than a database. So on the way from spreadsheet to database, certain adaptions may have been done.

Not every date might have made it into V4. Some dates may have been manipulated, e.g. quotes are omitted or converted to asterisks. Blanks may have been converted to dashes, too long fields are cut or omitted. Talk with the developers of the import function about the details.

A final manual finetuning will be inevitable. The import script can run incremental to improve the result step by step. The improvement comes from two procedures: (1) You improve the quality of your source data, and then run the import again. (2) The developers improve the import function, and you run the updated script again.

The critical consideration is, whether now to start using V4 as live data - or throw away this one import and restore the backup (of the empty company). You must tell, where your live data lives! Is it still in the Spreadsheet, or is it in V4 now.



p.s.: Ich wollte ja dieses Rezept auf deutsch schreiben. Aber jetzt ist es versehentlich doch wieder englisch geworden. Pardon.


(6)   p.s. For curious persons only

For the spreadsheet import, you do not need run scripts from the V4 Project Viewer, since you find a button on the Accessories Page of the Start Form to start the Tools Script.

But since we have already made screenshots and have written some text how to run a script from the Project Viewer, we won't discard that material, but put it here anyway. It may be useful for an experienced user for some other manual tasks at a later time.

Open V4. If you do not see the Project Viewer on your Paradox desktop, click the icon shown pressed on below screenshot, it toggles the Project Viewer on and off. Or use the button Project Viewer Button on the Accessories Page in the V4 Start Form.

The Project Viewer may now point to any directory, but you need the directory with the V4 program files. Switch Project Viewer to the V4 Progam Alias :V4PROGRAM: as shown in below screenshot.

Image 20091014°0021

How to switch to the V4 Program Alias :V4PROGRAM:

Eventually when switching the target in the Project Viewer, all other V4 forms and windows will close, including the V4 Start Form. Then you somehow must restart the V4 Start form. Or just close the Paradox desktop and restart V4.

One way to start a V4 script or a form or a query is from the Project Viewer. Below picture shows how to start the Tools Script directly from the Project Viewer.

Image 20091014°0022

Project Viewer under a V4 installation with Paradox Runtime (as opposed to the developer version). Note, that the originally installed scripts have the file extension sdl (compiled version). The newly added file from the develop space has the file extension ssl (source version).

Image 20091013°2201

Project Viewer in a V4 installation under the Paradox full developers version. The difference to the above shown Runtime Version menu is, that in the context menu, you are offered more things to do with the file.

Image 20091013°2202

The Tools Script Menu.

Another way to start the V4 scripts, forms and queries were from the Windows Explorer. If you have installed V4 on your machine, you find additional items in the Windows Explorer rightclick menu, to start Paradox scripts, forms, queries, tables.



Imprint : www.trilo.de