Explanation - Do Read !

 Top Next

Take care !

Before you try to use the application read thoroughly through these few support notes so that you are aware of what the Data Transform application is, and how it is meant to work.

It is suggested that you follow the guidelines for setting up disk folders and place files as indicated. Then the application will work as tested.

Don't be in a hurry, there are a few tricky things to get right before you can start "batch" mode processing.

I advise that once you have DBFs in a suitable folder, you select a single DBF and try to take it through the complete 'cycle', one stage at a time.

When you have seen the results of a transformation by looking at rows in the SQL table, then try a single file and the "One Touch" facility.

Only after a successful few "One Touch" operations move on to "Auto Batch" mode.

Obviously, it is important to get your server connection details input correctly, and a 'test connection' or two tried out, just to see that all is well.

Remember, do NOT be in a rush - take your time - then success will be the result !

PHH - March 2009.


Background to the Project

 Top Previous Next

Existing System :

A network system started in 1993 needed to be modernised.

The system started life as a Clipper 5.2 DOS application using DBFs for data storage.

In about 2003 the system was converted to Windows using Visual Objects 2.6, and the same data files were carried through into this new system. These files are now many more in number, and we index using CDX instead of the original NTX. In all we have about one hundred and ten data files, as well as index files.

Purpose to the System Re-Write :

The purpose of the re-write of the network system is to bring it up to date - well at least move it forward a decade or two in its underlying technology.

We wish to go to dotNet and SQL Server 2005, as this is what is in use on another part of the client network for their accountancy package.

'State' of the System :

I know that like many similarly 'dated' systems we need to do a lot of normalisation on our existing data. As well as a fair degree of normalisation we will need to set primary and foreign keys (PK and FK) to suit the new normalisation situation.

Indexes will also be made to suit the new SQL tables, so we ignore the existing ones.

The consequence of all this is that I need not concern myself with indexes or keys, but make sure I transform the data, the whole data, and nothing but the data - and that it has integrity preserved 100%. So all DBF records must translate into a matching table row, and all fields must translate into a matching table column.

Also, all table columns must have data types closest to the DBF data types, and where possible update to most appropriate ones.

Since modern dotNet classes allow it, we don't wish to lose the strong typing of out data at any point between the DBF read operation and the SQL table write operation.

[ See later section on date conversion in 'Data Type Conversions'. ]


The Contract Agreement !

The agreement between me (the supplier) and you (the user) is quite simple.

You are free to use and modify the code to suit your personal requirements - for both yourself and any client of yours. It is not yours to sell and there is no come-back to me if any of your data (or your client's) is lost or corrupted in any way. If you charge your customers for your time and effort while using the application, then that is fine by me - good luck.

The application has performed very well under tests at this end, and no data loss has been detected so far. It would appear that my test SQL tables have correct and intact data, both at a row, column and cell level. data types seem to have been correctly set too.

However, it is up to you to check-out the application to see it meets your requirements - before you embark on any serious data transformation projects.

To be fair, that is exactly what I have done here, before I risk it on my customers data. Just good practice !

The code "as-is" is not for you to "sell-on" to a third party, but you are welcome to pass it on to others free of charge.

PHH - March 2009


Data Safety and Integrity

Before we go any further, PLEASE realise that the data you use for this "Transform" process is either yours or the property of your client.

Make sure you only use ONLY copies of the master DBF files.

Also take time to create a suitable folder for the transform activities - follow the guidelines later in these notes.

However, since the Visual Studio solution is supplied to you, for you to do as you wish, you may use it in any way you please - just don't blame me!

Also, since these sorts of file intensive procedures are very much an individual style, change the folder and file approach to suit yourself - and obviously the Vulcan code to go with it - oh! and a few hours extra testing too ;O)

Below is an example of my test folder structure :-

=========================================================================================

Your place the copies of your master DBFs in the transform folder - in this case "OriginalGBdata" and the application with make the three sub-folders, as well as copy DBFs into the apptly named sub-folder, 'DBFs'.

Obviously, the other two sub-folders are for the use of the application, and you will find that all you data ends up as XML data, with associated XSD files, in the appropriately named folder.

All the text files with SQL script contents are in the other folder named 'CreationScripts'.

So actually, one you have used the DBFs once - to make the XML files - there is not need to have to use them a second time. The code can be modified to do "Auto Batch" on a set of XML file names, and not re-use the DBFs. It all depends what you wish to do.


Data Type Conversions

Luckily, this is not too complex or complicated. However, you need to know that there could be some changes to your recorded dates in your DBF data.

Dates :

Again, the 'design envelop' for my project (and data set) meant that we did not have dates going back before 1900. Yes, we did have blanks which needed taking care of, so I had to make the following changes :-

For the dotNet DataTable objects which are created from the DBF data, I have used SqlDateTime (SDT) data types to hold the dates. These have a limitation of the earliest date being 01/01/1753. The first day of the year 1753.

However, the SDT type can have a 'null' value. So quite simply I have converted all dates prior to 1753 to a 'null' and this does get copied through into the new SQL table successfully. Obviously, SQL tables have 'nulls' available to all types, if the properties are set in that way - which I have done to make the transform process as flexible as possible.

If you happen to have meaningful dates earlier than this then you need to change the transform code to create a further column which has a TimeSpan data type, this way you can have a date and an off-set, from which you can rebuild the original dates. That is all for you to sort out, not difficult, but fiddly - and needs a lot of checking too ;-)

My data frees me from the chore ;-)

Numeric :

The single numeric DBF data type is turned into INT32 or Double, depending on the number of decimals.

Logic :

DBF logic types end up as Boolean 'bits'. So TRUE/FALSE become 1/0.

Character :

As you may expect, strings are strings, are strings. Well, some of the time anyway ;O) I have not had to worry about Unicode or other related character issues. So be aware, you may need to make some changes.

PHH - April 2009.


Your Personal Needs

I would fully expect that many people wishing to use a transform facility such as this application, would also require customization of the code - to suit their particular problems and situations.

Please feel free to change the code all you need to, BUT, remember, the more you change and add to the code, the more testing you will need to carry out.

I have spent a lot longer testing and checking than I thought I would have to. Its just one of those problems ;O)

Because we intend to have the transformed table data considerably altered and 'chopped about' once we have it in SQL format, we did not add any particular "bells and whistles".

If you need to normalize your data to bring it up to modern standards, I would suggest you do this on the SQL server side of things. Just make sure you get the DBF data over as cleanly and as safely as possible first.

Good Luck !


Possible Limitations

Because I designed the Transform tool to do a specific job, for me and one of my clients, there will be limitations in its application to situations outside of our "design envelope".

File Size :

The most obvious is that of file size. Most of my files are less than 100 Mbytes in size - that is any of the single original DBFs.

However, I have tested the "One Touch" process on a file size up to 137 Mbytes and it worked just fine. If you are dealing with larger file you may need to divide or 'split' the DBF first, before the Transform process.

If this is the case then you will need to be prepared to do a little more coding. Once a table exists in the SQL DB it can have a bulk copy done on it multiple times, with different XML files. It is just that I did not setup the main form to work that way.

Rows and Columns :

My tests have also proved successful with up to 122 (one hundred and twenty two) columns, and nearly 99,000 (ninety nine thousand) rows.

Batch Total :

I have also used a batch size (total number of files) of over one hundred and been successful too (see below).

The image displayed below is a portion of the report log, which shows the final summary of the batch of 104. Total time under 21 minutes, and a total DBF file size of just under 0.7 Giga bytes. It may well be faster with Windows XP, I will have to give it a go soon.

I suggest you find your largest file and try it as a single operation. If you can 'walk' it through the conversion process then you should be able to progress much further.

The limitation which hit me, with my file set, was in displaying the XML file data, when using a 'walk through' approach to testing.

Initially I had the form to display XML and XSD file data, with the rich text control being given the whole of the file string content. This did hit a limit for me, and so I have limited this to display just the first 1,300 (thirteen hundred) lines. A next button allows successive 'chunks' of the file to be seen.

For most parts the XML data is only required for the user to see and check out the structure - not examine the full detail of a huge file ;-)

Windows O/S and Hardware :

In case there may be problems lurking due to the version of Windows used, and hardware too, I will tell what I used when getting my successes here in development.

I am using Vista 64 with a quad core processor and 4 Gigs of RAM.

So far I have not tried XP with less RAM but I do intend to use my XP laptop to do this Transform task - so I will report back on this later.

PHH - April 2009.


Possible Conflicts

What Else are you Running ? :

Although I have had quite a few other applications running, and in memory, while the "Data Transform Tool" is processing data, I have found one possible problem !

The spyware software shown in the image below seems to have a mind of its own on one of my Vista 64 platforms. The other morning it was resident in memory while I was testing and it caused all sorts of problems while I was trying to create and remove SQL tables, and write data too.

Once I removed the offending ? application, I had no more problems at all, and I did many, many, large batch tests.

Until you get confident in what you are trying to do with the Transform Tool I suggest you are careful with spyware, and possibly anti virus applications too.

I am running Avast Pro and that has been fine. I also have Visual and Management Studios up and running and that is no problem either.

Just be aware of this potential problem.

===============================================================================

PHH - April 2009.


Testing and Checking

Even though you may run the code in the VS solution without making your own changes, you still need to make your own tests when it comes to transforming your DBF data.

Management Studio (MS) is a wonderful tool to help you see just what is going on :-

Microsoft Management Studio :

=================================================================================================================================================

When creating the DTT I found MS very helpful indeed to see that tables were created and removed from the app as they should be. And that data had been inserted correctly. Even to count the rows in the tables.

I would strongly suggest that you have a copy of MS to use, and remember MS for 2008 will see 2005 databases but not the other way round.

Before you use the DTT for any serious personal or client / customer data transformation, I suggest you also check out certain columns with datetime data types and a few more that may also be critical to you.

REMEMBER, the correctness and integrity of you data is in your hands - the tool is just to help you. Don't blame the tool, blame your lack of testing ;O)


Compression Information

It would appear that some people keep clear of XML saying that it takes up huge amounts of space.

Well I hope to show you the results of some real tests to show you what the situation is in reality.

It may surprise you ;-)

Since a lot of colleagues appear to stick with standard zip for their file compression, I will use that, as well as 'RAR', for the comparison format. Also I will use standard settings for both of these - 'Normal', not Good or Best !

Out of interest you may wish to know I started to use 'RAR' years ago when I had to send data and applications as attachments to emails. We seemed to squeeze a lot more down the wires !

7Z is a free download which uses the same compression format as RAR (referred to below as the unRAR code?). It is to be found at :-

http://www.7-zip.org/

===========================================================================================================================

First of all, look below to see an image of the results after compressing the whole DBF set in that particular folder :-

===================================================================================================

So the set of DBFs zipped up has a size of 50.0 and the 'RAR'ed set 26.3 MB.

In the next image below, we see that the complete set of XML (data) and XSD (schema) files related to the DBF file set, has a 'RAR'ed size of 39.3 MB.

=================================================================================================

The second 'RAR'ed file was a test to see what improvement would be got from using the 'best' compression for RAR.

As you can see it is only better by 1.3 MB, and so we will stick with comparing 'normal' compressions.

In the image below we have a RAR file which is the complete DBF and XML set, including the text script files too.

===================================================================================================

=====================================================================================================

So, from all this we can say the zipped DBFs were 50 Mb and the RAR version of the XML data 39.3 MB.

Yes, I know, the actually size of the XML files is LARGE. However, the DTT performance seems more than acceptable.

I don't know if Microsoft compresses XML for transfer between 'layers' of its technology. By this I mean for XML data sets being set back to an app from the SQL Server, or similar operations elsewhere. It would be interesting to find out.


How it Works

Basically, the approach is to use dotNet classes that operate on XML and associated schema files.

XML was designed to transport and hold data, and is what under-pins much of the workings of .NET itself.

It would seem that in today's climate of data integrity and strong typing, that the CSV approach needs to be cast aside. Some would say "thank goodness".

There is a dotNet class called "SqlBulkCopy" which allows XML data in various forms to be sent to the SQL Server in one operation. I use the DataTable object to pass in to the exposed 'WriteToServer()' method as a suitable parameter.

Don't get mixed up with the SQL script statement which is "BULK INSERT ...." as this is an older offering, and does use a previously created CSV file.

The framework SDK says that these two methods are quite different in nature.

For a future version of the DTT I will change code around so that the size limitation of about 135 Mbytes for a single DBF is removed. The new version code will use streaming at both the reading and the writing phases - so that 'in memory' resources are not used up, or filled.

Keep a look out in Pearls for any future such version.


What to Do - How to Get Going

Once you manage to use the "Data Transform Tool" (DTT) to convert one DBF successfully, you ought to find the operation easy indeed.

The problem of such a tool is that it needs to be correctly, and carefully, set up before it will work correctly. So the start is the hardest part, just when the user has least experience ;-) Such is life!

I have tried to make things as easy as possible, by allowing the user inputs to be stored in a configuration file, which is re-read each time the DTT is launched.

- Create a new folder and place a few DBFs in it which are not large - I suggest start less than 30 Mbytes.

- launch the DTT and point the software at this new folder.

- check that you can get your combo box to be filled with the names of the test DBF file names.

- select one of the file names from the drop down list.

- try out some of the conversion operations from the push buttons, working from left to right across the form.

- any operations requiring an SQL Server connection will not be successful until you provide the correct connection string for your system.

- to start your transformation with as little trouble as possible, select an existing SQL database (DB) which you know works, and one you do not mind having a few tables added to. When you success in transforming to this existing DB you can try a new and 'empty' one.

- use the connection screen to input and explore your connection string parameters.

[ Read details in the 'Your Connection' sub-section later in these notes. ]

- when you can get an 'Open' status on testing, then serialize the input data so the config file is updated. Check this in the re-sizable rich text box by loading and viewing the XML file. You should see your recently input fields in the XML script text.

- close the DTD and re-launch it. See that the correct data is re-loaded and that it checks out.

- try to take one simple DBF through the 'walk through' process - that is using the individual push buttons in place of the 'One Touch' button.

- try all the buttons in sequence - left to right and top to bottom in each button row.

- when one 'walk through' process has succeeded try the same DBF with a 'One Touch' operation.

- leave the 'Display in Grid' checked, while you do these initial tests - just to see what is going on.

[ It does not seem to be as much of an overhead as I first thought it would be. ]

- now be brave, and choose to make a whole batch from the list of DBFs in the drop down box.

- for you first 'Auto Batch' just try two or three DBFs and of smallish size too. (Just saves testing time.)

- once 'Auto Batch' seems to work okay, 'go for it' and try what you like ;O)

- I suggest you now use MS to make a new database specifically for the transformation tests, and point at this DB.

- get the path and name of the new DB from the properties in the MS software.

- this bit may prove a bit tricky, so make sure you first test with an existing and working DB.

Good Luck.


More Information

Please be aware that DBFs with no records will not have a table structure created in the target SQL database.

Only files with one or more records will be fully processed. You can of course change this if you require blank tables. Since I was focused on the 'data' in the transform process I excluded empty files from the process.

Any detail which is thought to be outside of the expected behaviour has been trapped (I hope) and subsequently an entry will be made into the report log file. See the next section on this file, and what it will contain after you use it.

If you wish to gain more knowledge about Vulcan and its use with SQL, and also the XML side of this Data Transform Tool (DTT) then look to my web site WWW.ClickStartVulcan.Net where 'ClickStartVDN' is available for download right now, and where 'ClickStartMnS' will be quite shortly.

ClickStartMnS is to cover "Markups and Scripts" and focuses particularly on XML and XSD use underlying many Vulcan and dotNet processes.

On this site I may well in future post a DTT version which will process larger DBFs than at present. Instead of working with DataTables in memory I will change the operation to stream to and from disk files of a suitable format. SqlBulkCopy can use a stream instead of an object in RAM.

Use the check box 'Last Cell' to see the results of a full transformation of a DBF. It works with the 'See First 200' and 'See All Recs' buttons to display SQL table rows and show in the grid the last column which is RECID and is a simple record count identifier.

A config file is kept in the folder where the DTT exe file is stored. If this is missing at first, then a new copy is made and you need to set the field and property contents using the appropriate parts of the application - like the connection screen.

If you have any 'Lock Up' problems, it may be due to some stored data in this config file, which does not work when the file is open and applied at DTT startup time. Simply delete or rename the file and start all over again.


Report Log

Recorded Details :

At every meaningful stage of the transformation process the code causes suitable details to be recorded in a report log file on disk.

A portion of a typical log file is shown in it's simple viewer screen, and can be seen below :-

Wherever there is need to record a possible problem, a line or two of code is entered into the log.

For example when a DBF has zero records - in this case an SQL table is not created, not even the structure, and so the log needs to know.

For a successful transformation, only the start and close is recorded, along with the transformation rate - calculated for more than trivial files, once the 'Bulk Copy' process has been completed.

For interest, the transformation rate will depend on just how many date fields need to be processed and data converted.

Other issues which are recorded are if (and I have not seen any in my testing) the number of records in the DBF is not equal to the number of rows in the eventual table. Obviously the number of fields and the data table columns is checked also.

I am sure the code can potentially place many other messages in the report log too, they just have not been seen as yet - thankfully !

Summary Details :

These are provided at the end of a batch process, and are overall information on the body of the batch transformation.

Obviously time taken, total DBF file size, and transfer rate is of note, but also the number of empty files too.

PHH - April 2009.


Your Connection

This could be the hardest part of the exercise. It all depends on you, your experience with SQL and also your particular PC platform.

I suggest that for maximum chance of success you have a copy of Server 2008 Express installed from Microsoft's free download zone.

Also that the install is done without need for password and username - i.e. it has the 'Windows Authentication' setting applied. Also download the advanced tools - or whatever they are called.

If you do all these then you will be able to use Management Studio to prompt you with what it has found from your system settings.

See below for my system settings :-

On my system, I used this MS prompt approach to provide me with details to create my connection string which is shown below :-

We will discuss the value of Timeout in just a moment. The testing is done directly from the transform tool.

Once you have a suitable connection string you may then test to see if you can get a connection with your SQL Server.

See below, and that the open state is shown at the end of the text string :-

As well as this message box you will also see the '>> OPEN <<' status shown in the main form's caption bar and an example is shown below :-

======================================================================

Obviously, to get an open connection it helps to have pointed the connection form at the correct SQL database, as well as giving it the correct connection string.

Below is a set of inputs which work for me on this system :-

====================================================================

Note that if you use a value of zero for time out then this keeps the connection open indefinitely (thanks Willie !).

Here is a shot of MS with the properties details displayed :-

==========================================================================================================

What if your system is different to this ?

Well Good Luck ;-)

The best I can do is to say look at the information below for a web link to suitable material on how to get the right connection information 'assembled'.

If you have an 'SQL Server Authentication' then you will need to change the code in my solution so that the details that are input at the connection screen are used to create a suitable string for you and your system.

There is little point me try to code these alternatives to mine, as I can't test them to see if I have it right.

Here is some suitable and support material :-

http://www.connectionstrings.com/sql-server-2008

=========================================================================================================================

And finally, here is the section of existing code in the 'MyPartConnection' prg file :-

=======================================================================================================

Eventually, you need your version of 'cTest' to contain the information shown in the web details for your situation.

Only you can change and test these to see if you have it right. Keep making changes and doing tests till it works - one thing at a time.

This is one of the strengths and weaknesses of SQL. Some call it security, some call it a pain in the "*****" when all we want is to get onto the server and use an SQL application.

Well, in that case you should have set up you Server so it was a 'simple' install ;O)

Thanks goodness we have Management Studio!

PHH - April 2009.


Thanks and Acknowledgements

 Top Previous

As usual I have to thank a number of very supportive colleagues from the VOPS news group of the Grafxsoft server site.

I could not produce my ClickStart and Pearls materials without their full support. Many thanks guys and gals !

Also if it were not for DevFest (over many years) and John Parker, then I would not be here now ;-)