What techniques have people come up with for exporting pipe delimited data in one of the db's to another app for import? The receiving application will probably be Access so what is the best way to export and has anyone come up with any automated solutions? I know I could just ftp into the server, retrieve the file and run a macro but I would ideally like to export only new records that have been added (perhaps based on a date). Any ideas?

Thanks in advance.
Rob

ps. Tried the Excel export, never got it to work (like a few others I think).

You could write a subroutine that would do a search for the records you want, create a file for those records and then end up with a link that would let you download the file. That wouldn't be too hard.

You would still have to run a macro in Access to add the records to the Access database.

JPD
http://www.jpdeni.com/dbman/

Sounds good. Are you thinking of something along the lines of buidling a query into a form which sends the results to a file? How do I "trap" only the new records?
Cheers,
Rob

Access is the program I have DBMan data going into. I am having the same problem finding a way of getting the data from DBMan to Access in an automated method. I would be interested in the link idea. If you get an answer to the problem of data from DBMan to Access, I can certainly help you with automating the import into Access.

VaLinda

Rob, you would just do a search.

Would this be done on a regular basis, say, every Monday? If so, you could do a search for any records where the date is since the previous Monday.

Or you could get really fancy and have a separate file that would write the key value at the time a record was added. Then when you create your export file, the script would just look for those values. Once the file was created, the "key value" file would be erased.

The latter idea seems like it would be the most foolproof.

What I envision is a separate subroutine, accessable only by admins, which would just read the .db file and compare each record to the "key value" file. If there's a match, save the record to the export file.

JPD
http://www.jpdeni.com/dbman/

JPD,

Your latter idea seems the most appropriate. Basically it is to take the hours and dates from the database for each time it is entered and export it into Access. Thus your key value comparison would work very well. It is also only required by admin access so that would work well too.

Where do I start? (wink)
Rob

Well, first, you'll need to create a file in your dbman directory. Don't put it in the auth directory!! Call it keys.txt. (Actually you can call it anything you want, as long as the name isn't already used. ) Set the permission to this file to 666.

Set up a variable in your .cfg file --

In sub add_record, after

close DB; # automatically removes file lock

add

Add a couple of records and check to be sure that the key values are being written to the file, just to be sure.

I'd really like to be certain this is working before I go on. The next step is a lot of coding.

JPD
http://www.jpdeni.com/dbman/

Ok is done.
The file "keys.txt" has two counter values written to it when I add records. These match the counter values in the original database. Just for info the key field is userid.

Thanks JPD,
Rob

I find it's best to list the components first, so I don't forget anything.

Add variables for the output file
Add a link to the footer
Add an "elsif" to db.cgi
Add the subroutine.

I think we can do this all in one subroutine, which is a major advantage. Lots less coding.

Add variables for the output file --

You don't have to create the file first, but if you don't you may have trouble deleting it through FTP. If you do create the file, set the permissions to 666.

Add a link to the footer --

print qq!| <A HREF="$db_script_link_url&save_new_records=1">Save New Records</A> ! if ($per_admin);

Add an "elsif" to db.cgi, sub main --

elsif ($in{'save_new_records'}) { if ($per_admin) { &html_save_new_records; } else { &html_unauth; } }

Add the subroutine --


JPD
http://www.jpdeni.com/dbman/

Top!! Works well. The only problem is that I can't get it to write to a public file within a public directory. It only ever wants to create the file in my DbMan location. I've tried various permissions and settings but to no avail. I tried the path and URL settings in both the relevant .cfg and db.cgi but same problem :( I had/have the same problem with the Excel mod so I'm going to take it up with the ISP.

Thanks a bunch anyway, I'll let you know how I get on!!
Rob

Please do. I'd really like to know how it works.

Can you download the file if it's in your cgi-bin?

JPD
http://www.jpdeni.com/dbman/

No - as I get CGI Wrap errors (expected as it's within my cgi-bin area). The link and everything looks fine though.

Have you tried downloading the file through FTP? Even if you can't get the link to work, you would still be able to get just the most recent records.

JPD
http://www.jpdeni.com/dbman/

Yes JPD - did both. Spoke with the ISP, I needed to use the physical path on the server in the $save_records_path and not the virtual one (/data off the virtual root would not be seen in this case). Once I did that the save records file was accessible with no problems. I now have a separate file of just new records.

Thanks for the code - what is your email address for the Amazon voucher - I owe you!!

Thanks,
Rob

Hmmmm...the mod worked fine for the database I wanted it to. However, when I try and add records into another table (which only shares the pass file, count is different) I get an, "error in add_record. unable to open key file: .
Reason: No such file or directory" (the sub is only in the one html.pl file - I checked). It sounds like a db.cgi prob and I checked that the permissions are ok and the files are all ok. The data has been added to the file so I am puzzled.
Any thoughts?
Thanks, Rob

You got an error, but the new record was added to the external file?

JPD
http://www.jpdeni.com/dbman/

Sorry, Im being unclear.

1. The mod works fine for the database it was designed for. Both data and duplicated "new" data are writen to the save_records file.
2. It may well be unrelated but now when I add records to another database I get this error. Data is written to the other database file and nothing goes into the save_records (which is correct).

Could well be unrelated JPD but it is coincidental.
Rob

Okay. Now I got it.

Do you have a $save_records_path variable in the other .cfg file?

JPD
http://www.jpdeni.com/dbman/

Nope. It uses a different count and db file too. The only common theme is the pass file and of course db.cgi (which I just checked does not have a save_path setting either).

Me puzzled - it has been working fine.

Did you want to save the new records to a file in the other database?

If so, then you'll need to add a variable for the save path (and URL) to the file you want to save the records to.

JPD
http://www.jpdeni.com/dbman/

No, not at this stage. Each add function (for the other 4 databases) with the exception of the one it's designed for is getting the same error.

Looks like you need to add an "if" statement so it only works on the one you want it to.

JPD
http://www.jpdeni.com/dbman/

Nice, works fine. Thanks again.

Where do you want the voucher sending to please, you help everyone so much.
Rob

I'm glad it worked. There were a couple of ways to go with the "if" statement, but this way if you ever want to save new records from another database, all you have to do is set the path to the file.

You can send the gift certificate to hall@drizzle.com.


JPD
http://www.jpdeni.com/dbman/

Finally worked how to get "private" msgs!!
Cheers JPD