Importing File 3

[advpay]

Hi,<br><br>I have a bank (lockbox) file that I need to import into 3 different tables on a daily basis.<br><br>tblFileHeaderLayout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= 1<br>tblBatchHeaderLayout&nbsp;&nbsp;&nbsp;&nbsp;= 2<br>tblDetailHeaderLayout&nbsp;&nbsp;&nbsp;= 3<br><br>The file has three distinct lines and will have multiple batches in each file like 123333323333233333 (vertical)&nbsp;&nbsp;&nbsp;<br>each starting in position one of the record:<br><br>1XXXXXXXXXXXXXXXXXXX002XXXXXXX&nbsp;&nbsp;&nbsp;&nbsp;(One File)<br>2XXXXXXXXXXXXXXXXXXXXXXXXXX001X&nbsp;&nbsp;&nbsp;(Multiple Batch Records)&nbsp;&nbsp;<br>30361038000004016326396000396000&nbsp;&nbsp;(Multiple Detail Records)<br>2XXXXXXXXXXXXXXXXXXXXXXXXXX002X&nbsp;&nbsp;&nbsp;&nbsp;<br>30361038000004016326396000396000&nbsp;&nbsp;(Detail Records) 30361038000004016326396000396000&nbsp;&nbsp;&nbsp;<br>30361038000004016326396000396000&nbsp;&nbsp;&nbsp;<br><br>1(File)&nbsp;&nbsp;=RecTypCd/Date/CompanyID/DolTotal/BatchCnt/ItemCnt<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Pos(1)&nbsp;&nbsp;&nbsp;2-7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8-19&nbsp;&nbsp;&nbsp;20-31&nbsp;&nbsp;&nbsp;32-35&nbsp;&nbsp;&nbsp;&nbsp;24-29<br>2(Batch) =RecTypCd/BatchNum/Date/CompanyID/DolTotals/ItemCnt<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Pos(1)&nbsp;&nbsp;&nbsp;2-4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5-9&nbsp;&nbsp;&nbsp;&nbsp;10-20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;21-26&nbsp;&nbsp;27-32<br>3(Detail)=RecTypCd/BatchNum/Seqnum/CompanyID/AmtDue/AmtPaid<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Pos(1)&nbsp;&nbsp;&nbsp;2-4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5-9&nbsp;&nbsp;&nbsp;&nbsp;10-20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;21-26&nbsp;&nbsp;27-32<br><br>I am looking for guidance for the best way to process this.<br><br>Thanks for your help.<br><br><br><br><br>

 

[JimHorton]

advpay,<br>I think you could use the Access Text Import facility, using Fixed Width (assuming this format is set in stone).&nbsp;&nbsp;Then save the Spec, and call it in code using the docmd.TransferText, using the saved spec, filenames, etc<br>--Jim

 

[DougP]

Yes as Jim suggested<br>First create an &quot;Import Specification&quot; for each file<br>and save it.<br>you can automate the process as far as you want<br>Next create a form<br>Add a button to the form which has 3 docmd lines one for each file<br>look up docmd in help here are the bascis<br><br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.TransferText acImportFixed, &quot;SpecificationName1&quot;, &quot;Import_Table_In_Access&quot;, &quot;Name_Of_Textfile&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.TransferText acImportFixed, &quot;SpecificationName2&quot;, &quot;Import_Table_In_Access&quot;, &quot;Name_Of_Textfile&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.TransferText acImportFixed, &quot;SpecificationName2&quot;, &quot;Import_Table_In_Access&quot;, &quot;Name_Of_Textfile&quot;<br><br><br><br>

 

[Amiel]

Advpay ;<br><br>I got a blue ribbon on the job a few months ago for this one. They had been importing text (exported from UNIX application) as part of the daily routine. I'm talking <i>Mission Critical Processes</i> here. <br><br>Whenever the guy who does the import stuff decides to go fising for the day, the warehouse crew takes the day off too; phones stop ringing at the front desk; the company stock move a couple of points! Amazing !<br><br>I'm gonna print this one out and wait for your email (none is indicated for you on this page). It's pretty straight foreward, but will take some time to get it right. <br><br>And, yes this can be automated.<br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>

 

[advpay]

Thank you all so much for your quick responses. I will be adding this next week and am far ahead, thanks to this site and all your help.

 

[Amiel]

Im a little confused by the following :<br><br><i>1(File)&nbsp;&nbsp;=RecTypCd/Date/CompanyID/DolTotal/BatchCnt/ItemCnt<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Pos(1)&nbsp;&nbsp;&nbsp;2-7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8-19&nbsp;&nbsp;&nbsp;20-31&nbsp;&nbsp;&nbsp;32-35&nbsp;&nbsp;&nbsp;&nbsp;24-29<br></i><br><br><b>Question:</b> If I use the following...<br><br>Const Pos1 = 1<br>Const Pos1x = 1<br>Const DatePos = 2<br>Const DatePosx = 7<br>Const CompanyIdpos = 8<br>Const CompanyIdposx = 19<br>Const Do1Totalpos = 20<br>Const Do1Totalposx = 31<br>Const BatchCntpos =32<br>Const BatchCntposx =35<br>Const ItemCtpos= 24<br>Const ItemCtposx= 29<br><br>Const delimiter = &quot;,&quot;<br><br>Sub parceHeader(strHeader as str)<br><br>Dim buff as string<br><br>buff = mid(strHeader, pos1, pos1x)<br>buff = buff & delimiter<br><br>buff = buff & mid(strHeader, DatePos,DatePosx)<br>buff = buff&nbsp;&nbsp;& delimiter<br><br>buff = buff & mid(strHeader, CompanyIdpos,CompanyIdposx)<br>buff = buff&nbsp;&nbsp;& delimiter<br><br>buff = buff & mid(strHeader, Do1TotalPos, Do1TotalPosx)<br>buff = buff&nbsp;&nbsp;& delimiter<br><br>buff = buff & mid(strHeader, BatchCntpos ,BatchCntposx)<br>buff = buff&nbsp;&nbsp;& delimiter<br><br>buff = buff & mid(strHeader, ConstItemCtpos, ItemCtposx)<br><br>End sub<br><br>Do I now have the header information in a delimited text form?&nbsp;&nbsp;I guess that I'm asking, &quot;what is pos(1)&quot;.<br><br>Then of course (provided that I'm on the right track) if we treat the batch and detail items in the same way, the question is, &quot;is CompanyID (in the batch and detail records) the linking value for the header?&quot;. I assume so.<br><br>... and so there are two(2) ways to handle things from here: Write the raw records to the seperate tables and let the provided keys do the work (business rules?) in making the proper relationships (meaning that we can write the delimited text that we've extracted to a file and use pre established links to the file(s)) or can take this some steps further and validate that when the header indicates (BatchCt) 'X' number of items that the items are in fact there. Do you follow ?<br><br><br><br>Let me know <br><br><br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>