If you write database applications for use by others you will often be faced with the problem of allowing the end user to import data from another application or data source. Until recently I have had two main tools I have used for allowing end users to import data in my apps.
Super Import/Export from Boxsoft provides a quick and very easy way to import data from .csv (comma separated format) files and this has been my choice for many applications. However, not all of your customers will already have data in a .csv format - or have a method of converting data to .csv files. Even those who have their data in Microsoft Excel format files don't always know that they can choose the File, Save As option to convert their data to .csv files. I have also found that unless data is in a very simple format, csv files can be "non standard" and if punctuation is used in some of the fields importing can be less than perfect.
The other tool I have used is Impex from Sterlling Data. Impex has the advantage of offering dbase format and Excel format importing as well as Exporting of data. However, I have found it more time consuming to add Impex to my applications. Also, a minor inconvenience of adding additional .dll files to the project.
Now, I have a third tool to allow for end user importing of data and find that dpQuery from Ingasoftplus. Now it is not only easy to add to my applications, end users can import data from a wider variety of data sources. Yes, they can still import from .csv files, as well .dbf and .xls but they can also import from MS Access, SQL Server, just about any data source they may have on their computer.
The screen above is shown with a single record. I created a dictionary with a single table similar in structure to the Customers table in the famous Northwind.mdb file. After setting up dpQuery in my application, I then use the Import button and the screen below shows the record above any the imported data.
How'd I do that? The next few screen shots show what I did to enable the import. First you will see from the screen below that I had to add the global dpQuery extension to my application.
Next, on my browse screen I add the main dpQuery extension.
In order to use the extension I have to create a queue that has the same basic structure as my table that I will be importing in to.
Above, you can see that I have filled in a Window Caption, and selected my just created Data Queue. I then click the Insert button to add the columns from my queue and give them each a description.
The next step is the field pairs - to match my fields in the table with the corresponding field in my Data Queue.
The prefixes above are SAM for my SAMple table and dpq for my dpQuery data queue. The file to save is my sample table.
Now after I have finished setting up the extension I just go back to the window formatter and add a button to call the Import extension.
The screen above shows me selecting the embed after accepted on my Import button. It is really not too hard to implement dpQuery.
When the application is run, the end user can click the ... button to choose the type of data to import from, in my case the Northwind.mdb file which can be selected either user the Access .mdb option or an ODBC DSN.
Once the data source has been selected it is a simple matter of using the arrow keys or drag and drop to choose the fields to be imported, as shown above. Click the next button and drag and drop to match fields from the source (import) to the target or destination table.
The next couple of screens show two other great features in dpQuery. The ability for the end user to Filter the data before importing and the ability to select a sort order for the imported data.
If I wanted to, I could add the Region by clicking the arrow, and then choose from a drop down list a number of operators, and finally, from the last drop down list above select a value from the data being imported. This is a very nice feature.
The next screen shows that I can choose how to sort the incoming data.
Finally, before clicking the Finish button the end user can view the data as it will be imported.
I have wanted a tool for a long time that was easy for me to implement, easy for customers to use, and yet powerful enough to allow for importing a broad range of data with filtering and sorting options. dpQuery fits the bill in all areas and is my "Editor's Choice" for adding import options to Clarion applications.
