code to check length of data fields and to convert them into text file as per the bytes prescribed

[vinod9111]

Hi All,

Have to do huge task of reporting more than 80 lakhs rows of data at account level to regulators. Request your help for construction of code in access which can check the length of the data fields and also convert them into text post checking as per the bytes mentioned.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl65, width: 53"]Job_Code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl65, width: 45"]Month_[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]Year_[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]FillerI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]BSR_CODE_PART_I[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40"]FillerII[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 43"]
<tbody>[TR]
[TD="class: xl65, width: 43"]pag_no[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40"]sr_no[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40"]FCL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 36"]
<tbody>[TR]
[TD="class: xl65, width: 36"]dist code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65"]population grp_Code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl65, width: 45"]type of acct[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]org code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]occupation[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
<tbody>[TR]
[TD="class: xl65, width: 57"]nature of borrw acct[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40"]asset class[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Secured/Unsecured Loan code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65, width: 61"]Fixed/Floating interest Flag[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 41"]
<tbody>[TR]
[TD="class: xl65, width: 41"]BANK RATE1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59"]AMTFIN_roundoff[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65, width: 61"]POS_roundoff[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl65, width: 45"]Lot_no[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40"]Colan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58"]Unique ID[/TD]
[TD="class: xl65, width: 58"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl65, width: 52, align: center"]Length[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl65, width: 53, align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl65, width: 45, align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35, align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1*[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1*[/TD]
[TD][TABLE="width: 43"]
<tbody>[TR]
[TD="class: xl65, width: 43, align: center"]4*[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 43"]
<tbody>[TR]
[TD="class: xl65, width: 43, align: center"]4*[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl65, width: 52, align: center"]Bytes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="class: xl65, width: 53, align: center"]1-2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl65, width: 45, align: center"]3-4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35, align: center"]5-8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35, align: center"]9-9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60, align: center"]10-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40, align: center"]17-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 43"]
<tbody>[TR]
[TD="class: xl65, width: 43, align: center"]18-21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40, align: center"]22-25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40, align: center"]26-26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 36"]
<tbody>[TR]
[TD="class: xl65, width: 36, align: center"]27-29[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65, align: center"]30-30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl65, width: 45, align: center"]31-32[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: center"]33-34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60, align: center"]35-39[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 57"]
<tbody>[TR]
[TD="class: xl65, width: 57, align: center"]40-41[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40, align: center"]42-42[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: center"]43-43[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65, width: 61, align: center"]44-44[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 41"]
<tbody>[TR]
[TD="class: xl65, width: 41, align: center"]45-48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59, align: center"]49-58[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65, width: 61, align: center"]59-68[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl65, width: 45, align: center"]69-72[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="class: xl65, width: 40, align: center"]73-73[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58, align: center"]74-93[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
* is a space

a) From the above table the first row is a header.

b) Second row is the length of the data required for submission. The asterisk length is a space required for that particular filed.

c) The third row indicates the bytes required for conversion of data into text.

Request you to help me with the code which can check the length of this humongous data and also help me convert the data in text file with the above bytes position.

any help in this regard will be appreciated.

regards,

Vinod
[TABLE="width: 1271"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Micron]

Your post is kind of cryptic to me. Maybe a sample of the actual data and desired outcome would help. I'm not getting the significance of the byte row values.
If you want to know the length of the value in a field, it's just Len(NameOfField). If you want to convert it to text, use the CStr function. Note that you'll generate errors if the field is Null, so you'd have to wrap CStr around the Nz function. And what are "lakhs"?

Edit: Never mind. I looked it up.

 

[vinod9111]

Dear Micron,

Thanks for your revert

Sample data is below

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Job_Code[/TD]
[TD]Month_[/TD]
[TD]Year_[/TD]
[TD]FillerI [/TD]
[TD]BSR_CODE_PART_I [/TD]
[TD]FillerII [/TD]
[TD]pag_no [/TD]
[TD]sr_no [/TD]
[TD]FCL [/TD]
[TD]dist code[/TD]
[TD]population[/TD]
[TD]type of acct[/TD]
[TD]org code[/TD]
[TD]occupation[/TD]
[TD]nature of borrw acct[/TD]
[TD]asset class[/TD]
[TD]Secured/Unsecured Loan code[/TD]
[TD]Fixed/Floating interest[/TD]
[TD]BANK RATE1[/TD]
[TD]AMTFIN_roundoff[/TD]
[TD]POS_roundoff[/TD]
[TD]Lot_no[/TD]
[TD]Colon[/TD]
[TD]Unique ID[/TD]
[TD]Semi colan[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]60 [/TD]
[/TR]
[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]09[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]0000567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]440[/TD]
[TD]4[/TD]
[TD]30[/TD]
[TD]41[/TD]
[TD]95019[/TD]
[TD]99[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]8790[/TD]
[TD]450[/TD]
[TD]340[/TD]
[TD]0001[/TD]
[TD]:[/TD]
[TD]0000303LN37130840034[/TD]
[TD];[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]09[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]0000789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]440[/TD]
[TD]4[/TD]
[TD]30[/TD]
[TD]41[/TD]
[TD]95019[/TD]
[TD]99[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4560[/TD]
[TD]320[/TD]
[TD]210[/TD]
[TD]0001[/TD]
[TD]:[/TD]
[TD]0000303LN37130921575[/TD]
[TD];[/TD]
[/TR]
</tbody>[/TABLE]

I need a code to check the data in the field has the length which is shown above , for instance job code should be two digit, month should be two digit and so on and the blank cells are to be left blank. Hope am able to explain properly

With regard to export of tables into text files, the bytes i mentioned is the starting point and width required of each field while exporting.

Lakhs is the measure to count in India, 10 lakhs = 1 million


regards,

Vinod

 

[mole999]

rough and ready (and i'm sure someone has a better way)
=SUM(LEN(B4)+LEN(C4)+LEN(D4)+LEN(E4)+LEN(F4)+LEN(G4)+LEN(H4)+LEN(I4)+LEN(J4)+LEN(K4)+LEN(L4)+LEN(M4)+LEN(N4)+LEN(O4)+LEN(P4)+LEN(Q4)+LEN(R4)+LEN(S4)+LEN(T4)+LEN(U4)+LEN(V4)+LEN(W4)+LEN(X4)+LEN(Y4))

 

[xenou]

As far as I know, Cstr() won't necessarily give you fixed width fields, btw. The basic task here is to create a fixed width text file as output. You have to do some padding one way or another to make sure that fields that aren't the necessary length are padded to fill in the extra space.

 

[Micron]

As far as I know, Cstr() won't necessarily give you fixed width fields, btw. The basic task here is to create a fixed width text file as output. You have to do some padding one way or another to make sure that fields that aren't the necessary length are padded to fill in the extra space.

Not sure how you arrived at that conclusion but I'll go with it. The "sample" data doesn't do much for me. To me it says, job code, month, account type, etc. always has to be 2 characters. Year has to be 4. BSR has to be padded so that it is 7; Unique ID has to be 20, etc. For padding, I would suggest using the solution found here in post 21. If I'm right about the rest, then validation a the table field level might be easiest. If I'm not, then I still don't know what the need is.

As for CStr, I took this literally

and also convert them into text

 

[xenou]

Fixed width fields come in two flavors: one is where the data is stored in a fixed width and is also always exactly of a certain length. Example is a four digit year that must be four digits. The other is where data is stored in a fixed width field of but the data itself is of a variable length (i.e, -- the data is stored in a fixed width field but can be padded). Example is month that is stored in a 2-byte field with or without leading zeros and/or padding with spaces.

so we can imagine a fixed width files like these:
201701
20171
2017 1
The first is a four byte year followed by a 2 byte month, no padding.
The second is a four byte year followed by a 2 byte month where the month is padded with a trailing space.
The third is a four byte year followed by a 2 byte month where the month is padded with a leading space (not so common I guess).

Generally fixed width files are for importing or exporting data, and the nature of the beast is such as that typically you are providing the data to a somewhat older system that still uses fixed width files like this. Naturally if the data is stored in a database the job can be simplified if the table fields themselves are requiring data be stored with constraints to ensure valid data, at some level (such as years be four digits or months be numbers between 1 and 12).



Edit: note that what I usually do in these situations is write a custom routine that generates the text file - as each line is written, the fields can be validated and formatted to meet requirements.

 

[Micron]

Thanks for the insight. As old as I am, I've never had to work with such legacy files

 

[xenou]

VBA remembers those good old days (the I/O routines contain procedures that are made for reading and writing formatted output. You can also declare fixed width strings (a little known fact, I guess):

Dim s As String * 10
s = "Hello"
Debug.Print "|" & s & "|"