Appending from multi-line-structured SDF

[VFRDavid]

I have a PBX that is spitting out data in essentially an SDF file format. Unfortunately, the record is split into several lines:

N 010 00 T050 002 DN3009 12/02 15:41 00:00:18
0000 0000 4285

I have checked with the PBX vendor, and read the reporting guides, and there's no way to remove the CR/LF at the end of line one. So, my question is...how do I automate the import of this data? I know that I can do with with low-level file commands, read in one line at a time, and then decide which fields I'm populating...create some sort of flag so that I know when I've gotten all the lines read for the current record before APPENDing BLANK, but, I thought that someone might have a more elegant suggestion...

I will write the code described above, and post it when I have the solution...but any suggestions in the meantime will be very much appreciated!

Thanks...David

 

[VFRDavid]

OK - here's the code that I wrote...it works, but I've only tested it with the first two files created...I will have to keep a close eye on it for the first few weeks, to make sure that there aren't any structures that I haven't allowed for.

*************************************************************************************************
*************************************************************************************************
***
*** CDR Import
*** 12/04/2008 David Blum DMKB Consulting Corp
***
*** This program will look for all ".TXT" files in the startup folder, and attempt to import them
*** into the CDRWhole table.
***
*** The expected structure is a two-line format, where line 1 contains the record type, date, time
*** and duration, and the line 2 conains the Caller ID and DNIS information.
***
*** By APPENDing FROM the file as SDF, and including an appropriate FOR clause, I could split the
*** .TXT file in two, JOIN them with a SQL statement, then APPEND the resulting TABLE into the
*** master table, CDRWhole.
***
*** This HEAVILY relys on the fact that the records will be RIGHT NEXT TO EACH OTHER and that there
*** won't be any jumbling of the data.
***
*** Additionally, I had to STRTRAN the file's contents due to the propensity for the CDR output to
*** insert random NULL values in the data in unpredictable locations.
***
***
PARAMETERS llDebug
PUBLIC lcOrigCDRFile, lcFixedCDRFile, laCDR [1], liCDRFiles, liCurrCDR

IF TYPE ([llDebug]) = [L]
llDebug = IIF (llDebug, [Y], [N])
ENDIF

IF UPPER (llDebug) = [Y]
SET STEP ON
ELSE
SET STEP OFF
ENDIF

CLOSE DATABASES ALL
SET EXCLUSIVE OFF
SET SAFETY OFF

WAIT WINDOW NOWAIT NOCLEAR [Locating CDR Files to Import...]

m.liCDRFiles = ADIR (laCDR, [*.TXT])

IF m.liCDRFiles = 0
WAIT WINDOW NOWAIT [No files found to import, exiting...] TIMEOUT 5
RETURN
ENDIF

USE CDRLine1 EXCLUSIVE IN 0
USE CDRLine2 EXCLUSIVE IN 0
USE CDRWhole SHARED IN 0

FOR liCurrCDR = 1 TO m.liCDRFiles
WAIT WINDOW NOWAIT NOCLEAR [Importing ] + laCDR [liCurrCDR, 1] + [...]
DO ImpCDR WITH laCDR [liCurrCDR, 1], DATETIME()
NEXT liCurrCDR

WAIT CLEAR
WAIT WINDOW [Done!] TIMEOUT 5

RETURN
***
***
*************************************************************************************************
*************************************************************************************************
*************************************************************************************************
*************************************************************************************************
***
***
PROCEDURE ImpCDR
PARAMETERS lcSelCDRFile, ltStamp
PRIVATE lcFixedCDR, lcCDRContents, lcCDRFixedContents

lcFixedCDR = STUFF (lcSelCDRFile, AT ([.], lcSelCDRFile), 0, [-fixed])
lcCDRContents = FILETOSTR(lcSelCDRFile)
lcCDRFixedContents = STRTRAN (lcCDRContents, CHR(0))

?STRTOFILE (lcCDRFixedContents , lcFixedCDR)

SELECT CDRLine1
ZAP
APPEND FROM (lcFixedCDR) type SDF FOR LEFT(CDRLine1.rectype ,1)<>[ ]
REPLACE ALL CDRLine1.linenum WITH RECNO(), CDRLine1.imp_stamp WITH ltStamp

SELECT cdrline2
ZAP
APPEND FROM (lcFixedCDR) type SDF FOR LEFT(CDRLine2.fd, 1)=[ ] AND cdrline2.chginfo<>[ ]
REPLACE ALL CDRLine2.linenum WITH RECNO()

SELECT * FROM cdrline1 LEFT JOIN cdrline2 ON cdrline1.linenum = cdrline2.linenum INTO TABLE CDRImported

SELECT cdrwhole
APPEND FROM CDRImported

USE IN CDRImported
DELETE FILE CDRImported.DBF
RENAME (lcFixedCDR) TO ([Imported\] + lcFixedCDR)
RENAME (lcSelCDRFile) TO ([Imported\] + lcSelCDRFile)

RETURN
***
***
*************************************************************************************************
*************************************************************************************************


Anyway - any feedback will be greatly appreciated!

 

[Olaf Doschke]

I see you're importing odd and even lines into two tables CDRLine1 and CDRLine2. As I don't see thier structure I assume the FOR conditions LEFT(CDRLine1.rectype ,1)<>[ ] and
LEFT(CDRLine2.fd, 1)=[ ] AND cdrline2.chginfo<>[ ] will filter odd and even lines. This is the downside of this approach, as you ned to hardcode this kind of filter.

Anyhow you'll need to make two passes over each txt file, if you do it this way, or remove each second CRLF. Maybe it's possible to do that in the same step you remove the chr(0) to the "fixed" files. I assume the format of the txt files is UTF-8 or Unicode, then you're doing no good with this line: lcCDRFixedContents = STRTRAN (lcCDRContents, CHR(0)). You should instead convert with STRCONV().

Bye, Olaf.

 

[cricket]

APPEND all records to a table say THELOT.DBF - one field 254 char long

USE a new table FIRST which has a field structure to match the entire record from PBX
APPE from THELOT ony the odd number records using for MOD(RECNO(),2)=1


USE a second table SECONDS.DBF whose structure matches the second record from your PBX, and whose field names and type are identical to the unfilled records in table FIRST.DBF

APPE from THELOT ony the even number records using for MOD(RECNO(),2)=0


By setting a relationship between the 2 tables FIRST and SECOND based on RECNO(), you can then infill the blanks in FIRST from SECONDS

Not as elegantas the earlier solution, but easy to understand

 

[Olaf Doschke]

cricket, first of all THELOT.dbf should have a MEMO ield for the purpose to read in the file. What this approach lacks is, if you append from dbf to another, eg from Lot to First, this will not split the memo contents, appending from dbf to dbf will always look for identical fields in the source and destination dbf, you can't append from some.dbf type SDF, that is you can, but even if some.dbf will have a single char or memo field the SDF clause will not split this text.

Bye, Olaf.

 

[cricket]

Olaf
Output from PBX is normally just what was traditionally streamed to 80 column printers. You can see that inheritance in the sample data. Hence my field size of 254 in the starting dbf table. Should not need the infinite capacity of a memo field

 

[VFRDavid]

Thanks for all of your feedback...the problem is, it's not necessarily odd / even. In reality, the CDR output feed to the text file usually contains one additional CR/LF between records, so it's really a three line rotation (but it has included two blank lines between records on occasion). I thought about doing it with a MODulus, to see if I can determine every 1st, 2nd and 3rd line, but, this seems to work, so, I haven't investigated that method yet. That's why for the 1st line, I'm only looking at the CDRLine1.RecType field, but for the CDRLine2 record, I'm checking two fields (first should be empty, second shouldn't if it's not just an extra blank line).

I should have posted a larger sample of the data file, so here it is:

Q 046 00 T050 014 TN0446 12/03 09:59 00:00:02
0000 0000

N 047 00 DN3021 T005 023 12/03 10:00 00:00:30 A 705118665551212
0000 0000

N 048 00 T050 014 DN3022 12/03 09:59 00:00:32
0000 0000 4283

N 049 00 DN3009 T050 024 12/03 10:05 00:00:48 A 705014045551212
0000 0000

Q 050 00 T050 019 TN0407 12/03 10:08 00:00:06
0000 0000

Additionally, the file sometimes starts with a blank line, and sometimes not...so Line1 might be the even lines in one file, and odd in the next. By going with the data, and not the line's odd / even-ness, I think I'm a little more explicit.

Agreed with the comment about the 254 character field - it won't chop like an SDF will, I'd have to read in the 254 character field, and SUBSTR(THELOT.all254chars, pos, len) all over the place and do a bunch of REPLACEs.

Lastly - the STRTRAN() works perfectly to strip out the random NULL characters, but I will look into the suggested function, to see if there's any benefit to using it.

Thanks again for everything...I've posted threads on other sites, and waited weeks before I saw any activity. I'm pleasantly surprised by the prompt replies, as well as the quality of the feedback...this site will now become a regular stop for me...for posting questions as well as attempting to help our fellow colleagues!