By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,617 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,617 IT Pros & Developers. It's quick & easy.

I want to Transfer Data of Fixed length text file to MS Access

P: 1
I am a new user for MS Access 2003, I want to transfer a data of Fixed lenght text file into Access Tabel.

The data is contained of four Record Set those are as follows

1. The header record type is ‘H’.
2. The detail record type is ‘D’.
3. The summary record type is ‘S’.
4. “END OF DOCUMENT” record is left justified with no record type.

and the data is as follows
HXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 000.00 0.00 0.00*
DXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 0.0000CS 8.0000 0.00 0.0000 *
DXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 0.0000CS 00.0000 0.00 0.0000 *
DXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 0.0000CS 00.0000 0.00 0.0000 *
S 3*
END OF DOCUMENT

HXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 00.00 0.00 0.00*
DXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 0.0000CS 00.0000 0.00 0.00 *
S 1*
END OF DOCUMENT

I want that this data should be separated with Recordset wise. The data is start with H which is a Header Information of an Invoice then secnond Record is D which is a dsitribution of that Invoice and record S is a summery of Distribuiton while fourth record is END OF DOCUMENT where this Invoice is get completed. I want that Invoice no. should get me with respective Detail records.

Please advise me on the same.

Thanks and Regards,

Sanjay parab
Feb 9 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,382
Have you tried File > Get External Data?
Feb 9 '07 #2

ADezii
Expert 5K+
P: 8,669
I am a new user for MS Access 2003, I want to transfer a data of Fixed lenght text file into Access Tabel.

The data is contained of four Record Set those are as follows

1. The header record type is ‘H’.
2. The detail record type is ‘D’.
3. The summary record type is ‘S’.
4. “END OF DOCUMENT” record is left justified with no record type.

and the data is as follows
HXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 000.00 0.00 0.00*
DXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 0.0000CS 8.0000 0.00 0.0000 *
DXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 0.0000CS 00.0000 0.00 0.0000 *
DXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 0.0000CS 00.0000 0.00 0.0000 *
S 3*
END OF DOCUMENT

HXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 00.00 0.00 0.00*
DXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 0.0000CS 00.0000 0.00 0.00 *
S 1*
END OF DOCUMENT

I want that this data should be separated with Recordset wise. The data is start with H which is a Header Information of an Invoice then secnond Record is D which is a dsitribution of that Invoice and record S is a summery of Distribuiton while fourth record is END OF DOCUMENT where this Invoice is get completed. I want that Invoice no. should get me with respective Detail records.

Please advise me on the same.

Thanks and Regards,

Sanjay parab
As far as Importing Fixed Width Text Data into Access:
__1 File ==> Get External Data ==> Import
__2 Files of type = Text Files(...)
__3 Select Fixed Width
__4 Set your Field Breaks either by Mouse click or manually setting Start and Stop positions via the Advance button
__5 Follow remainder of Prompts

As far as your other request, you will definately have to be more detailed.
Feb 10 '07 #3

NeoPa
Expert Mod 15k+
P: 31,602
Sanjay, Access doesn't handle variable length records well (where they are fixed length fields but to differing specifications depending on the data).
Is it true to say that all your fields are separated by spaces in all circumstances?
If so, you can use the Separated Values form (rather than fixed length), and this should load the data up for you into a single table. You would then need to rearrange the data in your database to handle the different record types. This would typically be by assigning the data to different tables.
Feb 10 '07 #4

nico5038
Expert 2.5K+
P: 3,072
This will require some VBA code to sequentially process the records.
The H and D types indicate to me that you have a master (order?) record and a child (OrderDetails) record that will belong to the master.

You'll need code like:

Expand|Select|Wrap|Line Numbers
  1. ' define recordsets for master and detail table
  2. dim rsMaster as DAO.recordset 'This requires Tools/References to hold a Microsoft DAO version #.## reference
  3. dim rsDetail as DAO.recordset
  4.  
  5. dim strLine as string
  6. dim strKey as string
  7.  
  8. set rsMaster = currentdb.openrecordset("tblMaster")
  9. set rsDetail = currentdb.openrecordset("tblDetail")
  10.  
  11. open "C:\input\myfile.txt" for input as #1
  12. while not eof(1)
  13.   input #1, strLine
  14.   if len(strLine) > 1 then
  15.   select case left(strLine,1)   
  16.    case "H"
  17.      strKey = mid(strLine,2,10)
  18.      rsMaster.addnew
  19.      rsMaster!Keyfield = mid(strLine,2,10)
  20.      rsMaster!Field1 = mid(strLine,12,10)
  21.      '... etc
  22.      rsMaster.Update
  23.    case "D"
  24.      rsDetail.addnew
  25.      rsDetail!Keyfield = strKey
  26.      rsDetail!Field1 = mid(strLine,12,8)
  27.      rsDetail!Field2 = mid(strLine,20,5)
  28.      '... etc
  29.       rsDetail.Update
  30.    end select
  31.   endif
  32. wend
  33.  
  34.  
It's "aircode", but I hope it gives the idea how to process the data.
Each row not starting with H or D will be ignored and the H will define the key for the D.

Nic;o)
Feb 10 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.