Monthly records appending to existing data tables

[rrego]

every month the Health Dept sent us an ASCII file with all new infectious admissions and we append the ASC file records to our database. I am just getting my feet wet in Alpha - 2 weeks now- can someone please tell me how to write this code in XBasic?

** Our database tables are in Access
** Demographics is an access table
** EPIDemoImport in an ASCII file with the data to update to demographics

open(Demographics)
dem_records_Before = records(Demographics)
record_count = 0
record_added = 0
Open(EpiDEMOImport)
set(EpiDEMOImport)
loop until eof(EpiDEMOImport)
clear(dem:record)
next(EpiDEMOImport)
Record_Count = Record_Count + 1

Dem:EIN = epi1:EIN
Dem:PRIVPRAC = epi1:pmd
Dem:HMO_HP = epi1:hmo
Dem:HLTHSTRT = epi1:hs
dem:CHC = epi1:chc

add(Demographics)
if errorcode() = 0 then record_added = record_added +1.
end
END

Basically as a newie to Xbasic I will like to get the XBasic equivalent of this code.

Thanks you so very much...

[Steve Wood]

Re: Monthly records appending to existing data tables

That's pretty tight code. Probably easier if you attach a small sample of *fake* data as it would appear in your ascii file, and the schema for your table. You can run an import routine, or hand code it if the data needs to be modified as it is imported.

[MoGrace]

Re: Monthly records appending to existing data tables

That's pretty tight code. Probably easier if you attach a small sample of *fake* data as it would appear in your ascii file, and the schema for your table. You can run an import routine, or hand code it if the data needs to be modified as it is imported.

I have very limited experience in working with importing text files into tables. Usually I just pull the data into Excel and save as a dbf. But I have a DOS program that the only way to get any data out of it, is by generating a report and saving it as a text file. This of course leaves all the headers and printer control codes (formfeeds) in the file.

So Steve, if you see this - where do I start with 'modifying the data' first using xbasic code? The header block of text is consistently the same number of lines, but of course each report will have different dates, pagenumbers etc. After removing that, the data I want should be formatted correctly for importing.

[Keith Hubert]

Re: Monthly records appending to existing data tables

Hi Raul,

There is no need to write any Xbasic for this as with a little thinking this can all be done with action scripting.

First build a Temp table that will hold the imported data, looks like just five fields.

Now build you Operations.

First one, Zap Temp table.
Second, Import EPIDemoImport data into Temp table.
Third, Append from Temp table to Demographics table. Demographics table could have Auto Increment record no field.

Why the Temp table you may ask, well it allows you to make sure your data is good and clean and also allows you to prevent duplicate import.


Now build you action scripts by putting all the operations in one at a time. Test run, when you are happy with the results, set each one to run Silent.

You will be surprised how quickly this runs. Once you have built this you will have the confidence to build even more complex routines.

[Peter.Greulich]

Re: Monthly records appending to existing data tables

Keith, You're late to the party. Raul's post is 3 years old.

[Peter.Greulich]

Re: Monthly records appending to existing data tables

- where do I start with 'modifying the data' first using xbasic code? The header block of text is consistently the same number of lines, but of course each report will have different dates, pagenumbers etc. After removing that, the data I want should be formatted correctly for importing.

Robin,

Grab the text file by putting it into a variable. Take a look at the scanner* functions. You can extract/delete as needed, then save the pout put back as text or whatever form you need it in.

BTW: the scanner* functions/methods are the cat's pajamas. Super fast and really powerful. They don't flinch on mega-text size files.

[Keith Hubert]

Re: Monthly records appending to existing data tables

Ooops. Thanks Peter.

I thought with Robin answering it was a New Post. Should have looked more closely.

[MoGrace]

Re: Monthly records appending to existing data tables

I will Peter, thanks - and then I am sure I will have more questions!

[MoGrace]

Re: Monthly records appending to existing data tables

Hi Keith,
I was able to import the initial report directly into a temp table as long as all the fields are specified as character. It does take a few other operations after that to get the temp table ready to append - which I expect could be automated inside a script. But Peter's idea of scanning functions sounds like an opportunity to learn something useful as well.

We'll see...

[Tom Henkel]

Re: Monthly records appending to existing data tables

Robin,
Look into a program called "Monarch". We use it extensively to "scrape" data fields off of reports. Basically, you tell Monarch where to look on the report for the data you want, and it will grab the specific data requested and can even create a .dbf file for you.

Tom

[MoGrace]

Re: Monthly records appending to existing data tables

Hi Tom,
Monarch is priced at $1370... Thanks, but I think I will keep looking! ;)

[Tom Henkel]

Re: Monthly records appending to existing data tables

Sorry, Robin, I didn't realize it was so expensive. Our finance department got a copy years ago, and we're still using it.

I seriously doubt we paid anything near that way back then.

Tom