John Colby
jwcolby at ColbyConsulting.com
Tue Oct 18 08:38:32 CDT 2005
Gustav,
I certainly appreciate the "hard code" approach, I have doe that myself.
OTOH, I have had a handful now that were fixed formats but needed
"adjustments" to get it just right. For example:
I am working on an export (and later an import) to the US government for
what is called PlanD, having something to do with medicare re-imbursements
to the city government. The upload specification is VERY loosely defined,
basically they say "fixed width (with field widths specified), comma
delimited, with no header (field name) line, then they specify the format
inside of date fields to be YYYYMMDD. And THEN they say "if there is no
data then you don't need anything in that field, but the commas must exist.
So much for FIXED WIDTH!!! The issue here is that there is no upload test
site. How this is going to work I don't know, but we will submit a batch to
them "hoping" that it is in the format they want, and they will either
accept it, or they will respond "change this thing over here". Seems pretty
stupid to me, but that is what I am dealing with.
Another example:
The first system I built was for an insurance company. This insurance
company is in the middle of changing their software to a package they
purchased. It is mainframe stuff, and so they dumped an export spec into a
spreadsheet of a series of records consisting of their field name, start /
end position, data type (string, numeric, date etc) and format CODES such as
date, fixed, str etc. I did the "hard code" routine, then they promptly
came back with "yea but this field has to be fixed with 4 decimal places,
this one has to have two decimal places, this date has to have ddmmyy, this
one has to ...
This is STUPID. So I built a table from the spec they sent, added my own
fields to add a format string, and a code for left / right align etc. This
really does work. They are apparently adjusting something out in their
system because they have come back with "ok, add these fields IN THE MIDDLE
of the string... At which point I added a "field position" field (numbering
the field's position in the BigString). Voila, I just change the field
position and shove the fields around as I need.
Of course if you are dealing with a format cast in cement, where they
actually know what they want, and provide a CLEAR specification, then just
"hard coding" the solution works fine. OTOH, if you can design a table
driven system that works, then you can just open the table and modify the
necessary parameter as the client waffles.
This does work, I am using it with my waffling insurance company. I haven't
changed my code at all since I moved to the table driven design.
After doing this a few times, it becomes obvious that there are field
objects, there is a data set object, and there is an export object. Design
a system to fit that and it will probably work just fine for many, and
perhaps most such exports.
John W. Colby
www.ColbyConsulting.com
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, October 18, 2005 5:06 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Re-Invent The Wheel??
Hi John et all
I prefer to hardcode such routines. Most of them have been for creating
files for transfer of data to banks and these specs change very rarely and
if they do, often both the old and the new format can used for a while. As
the fields of such files are formatted in all sorts of ways, I've found it
convenient to use a Select Case structure for looping through the fields of
a record - an example is appended below - building a line field by field.
Routines of this sort need careful debugging but it's easy this way if you
include in-line documentation.
Also, lines can have different formats dependant of the "type" of record
(transaction) or the contents of another field - and may even have different
count of fields. And to complicate it further, a transfer may have
"attached" one or more lines with text lines from long notes (not used in
this example), and a batch of transfers may need special start and stop
records/lines, not to say (say, for transfer of salaries) a special "Totals"
line (not used here).
It will take some efforts to bring this to a class but, of course, for many
other purposes a class for text export can be nice.
/gustav
Example (Access 2.0 code with Danish comments, but you'll get the
idea):
Function BetalingTilBank_System_Bankdata (ByVal strKontoID As String, ByVal
strEgenKonto As String, ByVal strFilExp As String) As Integer
' RecordSource der udtrækker valgte betalinger.
Const cstrRecordSource = "qdxBetalingsfil"
Const cstrMsgTitle = "Betalingsfil til Bankdata"
' Transaktionstyper (delmængde) if. specifikation 2002-01-10.
Const cstrTranstypeImport = "IB"
Const cstrTranstypeStart = "000000000000"
Const cstrTranstypeSlut = "999999999999"
Const cstrTranstypeIndenlandskOvf = "030202000005"
Const cstrTranstypeBetalingskort = "030207000002"
' Fast antal felter (ekskl. CR + LF) for de transaktionstyper, vi
benytter.
Const cintFilFelterStart = 6
Const cintFilFelterSlut = 8
Const cintFilFelterIndenlandskOvf = 29
Const cintFilFelterBetalingskort = 27
Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim intFile As Integer ' Nummer på fil. Bruges af OS.
Dim strFileFelt As String ' Felt, der skal føjes til linien.
Dim strFileLine As String ' Linie, der skal skrives til
filen.
Dim varSum As Variant
Dim curSum As Currency ' Totalbeløb til overførsel.
Dim intBetalinger As Integer ' Antal betalinger.
Dim strValuta As String ' ISO valutakode.
Dim intFilFelter As Integer ' Antal felter i fil til Bankdata.
Dim strBankdata As String ' Første felt i fil til Bankdata.
Dim intFelt As Integer ' Tæller til feltoprettelse.
Dim datDatoOvf As Variant ' Dato for overførsel.
Dim intKortart As Integer ' 0 for bankoverførsel, ellers type
af IK/FIK.
Dim strMsgTitle As String
Dim strMsgPrompt As String
Dim lngMsgStyle As Long
Dim intOK As Integer ' Resultat.
Dim nl As String ' Ny linie.
On Error GoTo Err_BetalingTilBank_System_Bankdata
strValuta = "DKK" ' Kan også være "EUR".
nl = Chr(13) & Chr(10)
' Opret ny betalingsfil, hvis filen ikke findes, eller
' brug den betalingsfil, der findes.
If CreateFile(strFilExp) = True Then
' Tilføj til betalingsfilen.
intFile = FreeFile
Open GetShortFileName(strFilExp) For Append As #intFile
Set dbs = DBEngine(0)(0)
Set qdf = dbs.Querydefs(cstrRecordSource)
qdf.Parameters("FirmaNr") = Forms!Main!FirmaID
qdf.Parameters("OvfTid") = pdatBank
Set rst = qdf.OpenRecordset()
qdf.Close
If rst.RecordCount > 0 Then
' Skriv startrecord.
strBankdata = cstrTranstypeImport & cstrTranstypeStart
intFilFelter = cintFilFelterStart
For intFelt = 1 To intFilFelter
Select Case intFelt
Case 1 ' Transaktionstype.
strFileFelt = strBankdata
Case 2 ' Dato for dannelse.
strFileFelt = Format(Date, "yyyymmdd")
Case 3 ' Fyld.
strFileFelt = Space(90)
Case 4, 5, 6 ' Fyld.
strFileFelt = Space(255)
End Select
Call BetalingTilBank_System_Bankdata_Felt(strFileFelt, intFelt =
intFilFelter)
strFileLine = strFileLine & strFileFelt
strFileFelt = ""
Next intFelt
Print #intFile, strFileLine
strFileLine = ""
' Skriv betalingsrecords.
While Not rst.EOF
intKortart = rst!Kortart
If intKortart = 0 Then
' Bankoverførsel. Fast længde records.
strBankdata = cstrTranstypeImport & cstrTranstypeIndenlandskOvf
intFilFelter = cintFilFelterIndenlandskOvf
For intFelt = 1 To intFilFelter
Select Case intFelt
Case 1 ' Transaktionstype.
strFileFelt = strBankdata
Case 2 ' Index.
strFileFelt = "0001"
Case 3 ' Dato. Ønsket overførselsdato.
datDatoOvf = rst!Bankdato
If datDatoOvf < Date Then
' Selv om Bankdata vil rette datoen til dags dato, gør vi
det alligevel.
datDatoOvf = Date
End If
strFileFelt = Format(datDatoOvf, "yyyymmdd")
Case 4 ' Beløb. SKAL være uden decimalkomma.
strFileFelt = Format(rst!BankOvf * 100, String(13,
"0")) & "+"
Case 5 ' Valuta
strFileFelt = strValuta
Case 6 ' Fra-konto, finans eller bank. Bank: 2.
strFileFelt = "2"
Case 7 ' Fra konto med fast længde på 14 tegn og med
foranstillet "0".
strFileFelt = "0" & FormatBankkonto410(strEgenKonto)
Case 8 ' Overførselstype, check eller bank. Bank: 2.
strFileFelt = "2"
Case 9 ' Til registreringsnummer.
strFileFelt = Left(TrimNumStr("" & rst!Bankkonto),
4)
Case 10 ' Til konto. Altid 10 cifre med foranstillede
nuller, uden registreringsnr.
strFileFelt = Format(Mid(TrimNumStr("" & rst!Bankkonto), 4
+ 1), String(10, "0"))
Case 11 ' Adviseringstype. På kontoudtog: 0.
strFileFelt = "0"
Case 12 ' Tekst på modtagers kontoudskrift. 35 tegn.
strFileFelt = Left(rst!Betalingsident & Space(35), 35)
Case 13 ' Modtager (firmanavn). 32 tegn.
' Behøves ikke, men vises i bankprogrammet.
strFileFelt = Left(rst!Firma & Space(32), 32)
Case 14, 15, 17 ' Tomme.
strFileFelt = Space(32)
Case 16 ' Tomt.
strFileFelt = String(4, "0")
Case 18 ' Tekst på afsenders kontoudskrift. Maks. 35
tegn.
strFileFelt = Left((rst!Faktura + " ") & rst!Firma &
Space(35), 35)
Case 19 To 27 ' Tomme.
strFileFelt = Space(35)
Case 28 ' Fyld.
strFileFelt = Space(1)
Case 29 ' Tomt.
strFileFelt = Space(215)
End Select
Call BetalingTilBank_System_Bankdata_Felt(strFileFelt,
intFelt = intFilFelter)
strFileLine = strFileLine & StrAnsiToOem(strFileFelt)
strFileFelt = ""
Next intFelt
Else
' Betalingskort.
strBankdata = cstrTranstypeImport & cstrTranstypeBetalingskort
intFilFelter = cintFilFelterBetalingskort
For intFelt = 1 To intFilFelter
Select Case intFelt
Case 1 ' Transaktionstype.
strFileFelt = strBankdata
Case 2 ' Index.
strFileFelt = "0001"
Case 3 ' Dato. Ønsket overførselsdato.
datDatoOvf = rst!Bankdato
If datDatoOvf < Date Then
' Selv om Bankdata vil rette datoen til dags dato, gør vi
det alligevel.
datDatoOvf = Date
End If
strFileFelt = Format(datDatoOvf, "yyyymmdd")
Case 4 ' Beløb. SKAL være uden decimalkomma.
strFileFelt = Format(rst!BankOvf * 100, String(13,
"0")) & "+"
Case 5 ' Fra-konto, finans eller bank. Bank: 2.
strFileFelt = "2"
Case 6 ' Fra konto med fast længde på 14 tegn og med
foranstillet "0".
strFileFelt = "0" & FormatBankkonto410(strEgenKonto)
Case 7 ' Kortart.
strFileFelt = Format(intKortart, "00")
Case 8 ' Betalingsident på IK/FIK. 19 tegn. NB:
Streng, ikke tal.
Select Case intKortart
Case 4, 15, 71, 75
strFileFelt = "" & rst!Betalingsident
If IsNumeric(strFileFelt) Then
strFileFelt = Left(strFileFelt & Space(19), 19)
Else
' Vil være en fejl.
strFileFelt = Space(19)
End If
Case Else
strFileFelt = Space(19)
End Select
Case 9 ' Tomt.
strFileFelt = String(4, "0")
Case 10 ' "Girokonto" for IK, kortart 01, 04 og 15,
ellers tomt.
Select Case intKortart
Case 1, 4, 15
strFileFelt = Format(TrimNumStr("" & rst!ModtagerID),
String(10, "0"))
Case Else
strFileFelt = String(10, "0")
End Select
Case 11 ' ModtagerID for FIK. Tomt for kortart 01, 04
og 15.
Select Case intKortart
Case 1, 4, 15
strFileFelt = String(8, "0")
Case Else
strFileFelt = Format(TrimNumStr("" & rst!ModtagerID),
String(8, "0"))
End Select
Case 12 ' Modtager (firmanavn). 32 tegn.
' Behøves ikke, men vises i bankprogrammet.
strFileFelt = Left(rst!Firma & Space(32), 32)
Case 13 ' Tomt.
strFileFelt = Space(32)
Case 14 ' Eget bilagsnummer. 35 tegn.
strFileFelt = Left((rst!Faktura + " ") & rst!Firma &
Space(35), 35)
Case 15 To 19 ' Tomme.
strFileFelt = Space(35)
Case 20 ' Adviseringstekst. Maks. 35 tegn.
Select Case intKortart
Case 1, 73
strFileFelt = Left(rst!Betalingsident & Space(35),
35)
Case Else
strFileFelt = Space(35)
End Select
Case 21 To 25 ' Tomme.
strFileFelt = Space(35)
Case 26 ' Fyld.
strFileFelt = Space(16)
Case 27 ' Tomt.
strFileFelt = Space(215)
End Select
Call BetalingTilBank_System_Bankdata_Felt(strFileFelt,
intFelt = intFilFelter)
strFileLine = strFileLine & StrAnsiToOem(strFileFelt)
strFileFelt = ""
Next intFelt
End If
Print #intFile, strFileLine
strFileLine = ""
varSum = rst!BankOvf
If Not IsNull(varSum) Then
curSum = curSum + varSum
End If
rst.MoveNext
Wend
intBetalinger = rst.RecordCount
rst.Close
' Skriv slutrecord.
strBankdata = cstrTranstypeImport & cstrTranstypeSlut
intFilFelter = cintFilFelterSlut
For intFelt = 1 To intFilFelter
Select Case intFelt
Case 1 ' Transaktionstype.
strFileFelt = strBankdata
Case 2 ' Dato for dannelse.
strFileFelt = Format(Date, "yyyymmdd")
Case 3 ' Antal transaktionslinier.
strFileFelt = Format(intBetalinger, String(6, "0"))
Case 4 ' Sum af betalinger.
strFileFelt = Format(curSum * 100, String(13, "0")) & "+"
Case 5 ' Fyld.
strFileFelt = Space(64)
Case 6, 7, 8 ' Fyld.
strFileFelt = Space(255)
End Select
Call BetalingTilBank_System_Bankdata_Felt(strFileFelt, intFelt =
intFilFelter)
strFileLine = strFileLine & strFileFelt
strFileFelt = ""
Next intFelt
Print #intFile, strFileLine
Close #intFile
intOK = IsFileLong(strFilExp)
End If
End If
strMsgTitle = cstrMsgTitle
If intOK = True Then
strMsgPrompt = strMsgPrompt & intBetalinger & " betaling" &
IIf(intBetalinger = 1, "", "er") & " blev oprettet i filen" & nl
strMsgPrompt = strMsgPrompt & strFilExp & "." & nl & nl
strMsgPrompt = strMsgPrompt & "Samlet beløb: " & strValuta & " " &
Format(curSum, "Standard") & "."
lngMsgStyle = 0 + 64
Else
strMsgPrompt = strMsgPrompt & "Betalingsfilen" & nl
strMsgPrompt = strMsgPrompt & strFilExp & nl & nl
strMsgPrompt = strMsgPrompt & "kunne ikke oprettes."
lngMsgStyle = 0 + 48
End If
MsgBox strMsgPrompt, lngMsgStyle, strMsgTitle
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
BetalingTilBank_System_Bankdata = intOK
Exit_BetalingTilBank_System_Bankdata:
Exit Function
Err_BetalingTilBank_System_Bankdata:
MsgBox "Fejlkode " & Err & ". " & Error
Resume Exit_BetalingTilBank_System_Bankdata
End Function
---
>>> jwcolby at ColbyConsulting.com 18-10-2005 05:33:25 >>>
I have created a system similar to that which we are about to create.
That
system was the first time I attempted to create a table driven method for
doing this and was dedicated to a specific export for a specific client. I
think it would be useful to pull the concepts out, hash them over in public,
and write up a specification of how this can be done in a generic manner so
that it can be applied to a wide range of "delimited export" applications.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com