• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Database Experts! Need your help please ...

I have gotten assigned to creating an data export/import feature into a large database application (sort of a backup or to pass data from one unit to another).

We are using ADO and currently the database is built in Sybase (with plans on using SQL Server/Oracle).  Approx. 35+ tables (export and import speed is not an issue).

I have considered using XML because it included the database definition along with the data - but I am not an XML expert plus I am a little leary on added all the necessary references (project size?) - correct me if I am wrong.

But what I would like are some suggestions about exporting and importing data (comma-delimited, something new, XML)?  Any type of code or links for me try would be the best option.

Much appreciated!
  • 5
  • 4
  • 4
  • +4
1 Solution
TimCotteeHead of Software ServicesCommented:
Personally I would probably stick to csv format. I don't know much about sybase but certainly SQL server can easily and rapidly import csv files. The database schema is not really important if I understand your situation as you are completely in control of both ends of the import/export and you presumably know in advance exactly what kind of structure(s) you are dealing with.
PBuckAuthor Commented:
ahhh .. good point about the structure.  I guess I am a little shy because I want to allow the user to choose which tables they are interested in exporting.

The selection process would not be an issue - but any suggestions about how to mark/flag the tables within csv.  Does ADO have any feature to help me with this ?

Thanks for the quick response
Éric MoreauSenior .Net ConsultantCommented:
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Éric MoreauSenior .Net ConsultantCommented:
I would use Microsoft Data Transformation Services (DTS). It comes on the SQL server disk. It can be used as a stand alone program and it's FREE and quick to set up. You can also schedule data transfers and it can eMail you with errors or problems in the transfer.

PBuckAuthor Commented:
I am gonna take a few days here and have a look at the links above - plus check my options with csv files.

DTS does not sound like an option if the remote site is using something other than SQL Server?  Correct me if I am wrong ... but can that be used to transport Oracle data to Oracle or even Sybase for that matter?  I am unfamiliar with DTS  :-(

Thanks again all ...
DTS will convert from X to X. Doesn't have to be SQL Server. You could convert to Database to CSV and then convert CSV to another Database. Great little tool.

I have a number of web-sites using the same software.  Each time I added new tables/fields/indexes I had to update all sites remotely before new software versions could be installed.

The problem with DTS is it didn't create indexes and it couldn't handle Identities (Autonumber's etc.), mabe it could but like everything Microsoft how?
The problem with XML is didn't have CRC so you can't be sure the data is good. CSV didn't hold enough of the advanced schema. Also if the data was to pass via public services I wanted to be encrypted.

I created a function to export the schema and data from all or selected tables. I created an output in a kinda text format with CRC on each record to ensure data is good.  I created the import so that new tables, fields and indexes would be automatically, after a backup.  The system would automatically amend field types so that the databases schemas were identical. I thought that this would take just a few hours to complete but it took weeks.  

It took ages because SQL server's documentation was so obscure, where did it hold the Identity data for a table? Where were the constraints held? Etc, etc, etc....

Now I can drop data from SQL server to a text document, to MS Access and back.  I am sorry but it took so long I could not give away the code but I would be interested in an arrangement.

I even created a client server app. that could work across the public Internet dropping encrypted records between servers.
I know this is beyond where you were thinking but with some thought you can take a huge jump.  The other spin-off from using your own proprietary format is that you can create business objects. I wanted to be able to simply copy a load of associated data onto a clipboard and paste the data.  Also if an application fails the object can then be sent by e-mail to the programmer.  If the file type is registered on his system the data gets updated into his test database. So I wrote the e-mail part as an integrated feature for rapid solution of faults.

So I created some object tools that can load all of the data (from records sets/files) with a few simple statements. I have completed the DAO part of this tool but I also want to make it work for ADO too.

Example specificaion for a transaction object:

Title=Saracs Transaction
;Normal file extension

;Sales ledger Transaction Details are stored in
Table=l S/L Item Headers
Key=SL Tran Ref
DescriptionField=SL Comment
; List the tables which are linked
Linkage1=S/L Lines
Linkage2=B/L Entries
Linkage3=N/L Entries
Linkage5=S/L Account
MasterKey1=SL Tran Ref
ChildKey1=SL Tran Ref

[S/L Lines]
;Item lines are found here
Table=l Item Lines
MasterKey1=SL Entry
ChildKey1=SLL Entry

[B/L Entries]
;B/L (Accounts Payable) entries linked with this sale are strored here
Table=l B/L Item Headers
MasterKey1=SL Entry
ChildKey1=SLL Entry

[N/L Entries]
; General Ledger entries are found here
Table=l N/L Entries
MasterKey1=SL Entry
ChildKey1=NL Entry
Linkage1=N/L Accounts

[N/L Accounts]
; Nominal Account details here
Table=l N/L Accounts
MasterKey1=NL A/C
ChildKey1=NA A/C

[S/L Accounts]
;Customer Data
Table=t Company File
MasterKey1=SL A/C
ChildKey1=CO Ac No

; When this invoice was paid/part paid
Table=l S/L Payment Details
MasterKey1=SL Entry
ChildKey1=PD ItemEntry
Key=PD Entry
Linkage1=Payment Batch

[Payment Batch]
; Other items paid with this entry
Table=l S/L Payment Details
MasterKey1=PD Entry
ChildKey1=PD Entry
Linkage=Payment Batch

; Sales analysis Events details
Table=t Venues
MasterKey1=SL Event
ChildKey1=VF Event Code

; Sales Group Details
Table=t Sales Groups
MasterKey1=VF Group
ChildKey1=Sales Group

PBuckAuthor Commented:
Impressive.  Very interesting concept inthedark and I have no doubt that this project took a few weeks to accomplish.  Sounds like you covered many bases and made it as robust as data archiving/backup can be :-)

This side project of mine is more of a bonus add-on that some must have tool.  We thought it it would be very pro-active to be able to backup data or pass records to another instance of our application.  So I think I will keep it simple for now - but maybe down the road I will re-think about a tool such you as suggested.

I will still look over things in the next few days ... thanks again everyone!
Start with a vision and go there one pace at a time.
p.s. My concept started with a respect for the ease of use of the IIS Session and Application objects.  The idea was that in your program you could place stuff into an object which can then exist in a multiple of forms. A database is an excellent medium for recalling information, I wanted to add the same functionality to a Sub or Function's variables so that they can be exported.  In other words you don't need recordsets any more you just build your transaction and then fire it in a direction.  It became so cute to be able to copy/drop/view all variables within a Sub or Function - so the spin-offs just keep coming.

This might be a little too simplistic.  But, with ADO you can persist the data as XML or in a format that can only be read by ADO.  The nice thing is that you can open this file that is created up with ADO in another application as an ADO recordset.  Once you have it in a recordset, you can put that recordset into a table on the other side.  If you would like to see some sample code on this, I have some that I did for something similar.  I used XML instead of the ADO format, but either works.
Yes michaeljs the ADO recordset.save is very interesting and an excellent but simple way of exporting/importing data. Sadly Microsoft missed out the important parts of the table schema so did not provide a solution to my problem. Further if you have millions of records the files can be huge as each field name is re-stated in each record.  Although this may sound trivial, in a 50 field recordset it could add up to .6GB per million records and more than double the file size.  But on the other hand the ADTG format uses just a single character as a field terminator and therefore produces efficient files. So michaeljs, for just data transfer, your proposal must be the quickest solution. Move the files into a zip file and you could get CRC and data security.

Dim GF As New zGF   ' general functions
Dim ADO As New zADO ' handy ADO functions
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim ok
Dim SQL As String

Dim DestFolder As String

DestFolder = "D:\ExportTest\"

GF.CheckSlash DestFolder ' put \ on end if they forgot

On Error Resume Next
MkDir Left(DestFolder, Len(DestFolder) - 1)
Kill DestFolder + "\*.adtg"
On Error GoTo 0

ADO.RegisterConnectionFile "D:\CustData\OB\$WEB\odbc.txt"
ok = ADO.ConnectOK(CN) ' open connection

ReDim Tables(0) As String
Tables = ADO.GetTables(CN)
Dim tc As Long

Screen.MousePointer = vbHourglass
For tc = 0 To UBound(Tables)
    SQL = "Select * from [" + Tables(tc) + "];"
    ok = ADO.OpenRSROOK(CN, RS, SQL) ' open readonly recordset
    If Not ok Then
        MsgBox ADO.GetLastError(CN)
        RS.Save DestFolder + "\" + Tables(tc) + ".adtg", adPersistADTG
        Set RS = Nothing
    End If
Next tc

Screen.MousePointer = vbDefault
MsgBox "Done"
Éric MoreauSenior .Net ConsultantCommented:
Any progress?
PBuckAuthor Commented:
Hate to admit this ... but I just started on this last weekend.  I am gonna try the pure ADO route for both import and export (testing against Sybase and SQL Server). Since I have control of the tables, I should be able to utilize a header function for the import function to read and know which table the data is for.

I built the GUI skeleton to let the user choose which table (or all) etc.  So now I need to add the guts of the program and see how things fly.

Until I come across a successful function I will then award the points.  Thanks everyone for all your patience!
Éric MoreauSenior .Net ConsultantCommented:
This question appears to be abandoned. A question regarding it will be left in the CleanUp
area; if you have any comment about the question, please leave it here.

Unless there is objection or further activity, one of the moderators will be asked to accept the comment
of <inthedark >.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 4
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now