Import text file and copy text on 1 row to each row (Macro)

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
I have a very large fixed-length file (400 chars in length). Each row has a 2-character indicator in position 1-2. On the first row of the file, that begins with HH, it is followed by a field that is 16 digits long. This 16-digit value needs to be copied to position 401-416 on all rows, including HH.

Since I can't do this in Excel due to the row count limitation, how can I do this in Access?
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,946
Office Version
  1. 365
Platform
  1. Windows
Are you sure you can't do this in Excel?

The row limit is currently 65536.
 

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
The file I am working on now is over 66,000 rows.

I am willing to do this outside of Access, but the problem is that my next post to this site after I get this question answered will be how to import the file so that each distinct 2-char row (i.e. HH, V1, V2, etc) get parsed into their own table (I have distinct mapping for each of the >20 distinct record types), automatically. Adding the 16-char value to each row, will allow me to have a foreign key in each table to pull data together for a report.
 

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
Here's an example that may be helpful to understand my problem. Consider a 24-char row length for this example. See how the 1st HH row's 16 digits (pos 3-18) is copied on rows HH, V1, V2, G1 and FF, then again for the next new HH row, etc.

Here's what I have:

HH1234567890123456ABCXYZ
V10000000000000001ABCXYZ
V20000000000000003ABCXYZ
G10000000000000001ABCXYZ
FF0000000000000001ABCXYZ
HH1234567890123457ABCXYW
V10000000000000001ABCXYW
V20000000000000003ABCXYW
G10000000000000001ABCXYW
FF0000000000000001ABCXYW

Here's what I want:

HH1234567890123456ABCXYZ1234567890123456
V10000000000000001ABCXYZ1234567890123456
V20000000000000003ABCXYZ1234567890123456
G10000000000000001ABCXYZ1234567890123456
FF0000000000000001ABCXYZ1234567890123456
HH1234567890123457ABCXYW1234567890123457
V10000000000000001ABCXYW1234567890123457
V20000000000000003ABCXYW1234567890123457
G10000000000000001ABCXYW1234567890123457
FF0000000000000001ABCXYW1234567890123457
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

I'm a little confused, because you talk about positions 401 - 416 to copy this 16 character "field" that starts out in positions 3 - 18. Where does the data for positions 19 - 400 come from?

And, I see the V1, V2, G1, FF record designators. Are these records each 400 positions long? Do these (HH, V1, V2, G1, FF) records build together somehow to get to the 400 position record?

Are all the records the same length? How many different record designators are there? This sample only shows 5. This is a sample, right?
 

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
The file is like an EDI file, with header, detail and footer records. I am not 100% certain how many detail record types there are (I think a dozen or so), but for the sake of this post, assume 4 inner detail records with different record type codes, 1 header and 1 footer.

Each row is 400 chars in length. I am proposing to add 16 more chars to each row. The HH row will have the 16 char field information duplicated (pos 3-18 and 401-416), while all the other rows will have the 16-char field only in pos 401-416. The idea is to have this fiels in the same position on each row.

In regard to your question on how the records are all built, well, I don't know. The file comes from a 3rd party and I have no ability to have them alter it.

Hope this helps.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

As I understand this, the only way to tie the records after the HH record with the HH record is their position RIGHT AFTER the HH record. So, the only way to add positions 3-18 from the HH record to all records would be with a VBA routine. Because all records are the same length, define a record with the following fields: 2 bytes, 16 bytes, 382 bytes. The output record would then have 2 bytes, 16 bytes, 382 bytes, and 16 bytes. Read through the table and at each HH record move the 16 bytes into its own field (the output 16 bytes at the end of the record).

To answer your "Next" question: After you have finished this procedure, then you can create a query for each record type, based on the first two bytes. Each query would define it's own record type, not by field names, but by using the Mid() function.

Something else you could do is to write the records out to their own individual table, based on the first two bytes of each record during the updating of each record with the 16 bytes from the HH record.

I do not know how, but I believe I have seen something in the past to where you can move the whole 416 bytes into a form of a data structure and be able to reference each individual field from the data structure. I would suggest posting that question back to this board if you don't get any other responses to this thread.
 

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
I'm glad that you understand my problem more clearly now. I have no VBA experience, which is why I need a bit more help.

My thoughts were to do as you say:
1) Pull in the data into 1 table per record type, with the extra 16-char field on each. The table could be 3 fields (rec_type, data, 16_char_field) as you suggested.
2) Write a query on each table, using MID to pull the data on field #2, linking all tables to the 16_char_field).

So, at this point, I need to know how I can do the part that gets the 16_char_field from each HH record onto each other row. Once I have this, I may be able to take the query part from there.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Code:
Dim rs as DAO.Recordset   'Define the two recordsets needed
Dim rs1 as DAO.Recordset
Dim fld16 as String		'Define storage area to hold the 16 bytes from the "HH" record.
Set rs = CurrentDB().CreateRecordset("tblRawData", dbOpenSnapshot)
Set rs1 = CurrentDB().CreateRecordset("tblRawData16", dbOpenSnapshot)
While Not rs.EOF
  If rs!fld2 = "HH" then 	'Capture the 16 bytes from the "HH" record to write to the other records.
    fld16 = rs!fld16
  End If
  rs1.AddNew			'Add a new record to the table
    rs1!fld2 = rs!fld2
    rs1!fld16 = rs!fld16
    rs1!fld382 = rs!fld382
    rs1!fldKey = fld16
  rs1.Update			'Update the new record with the data
  rs.MoveNext			'Read the next record 
WEnd

In order for this code to work, you will have to import the file into table "tblRawData"
Table "tblRawData" will be defined as having three fields, "fld2", "fld16", and "fld382"
Table "tblRawData16" will be defined as having four fields, "fld2", "fld16", "fld382", and "fldKey"

Before writing this code, while in a VBA Module, select the "Tools" menu, "References" option, then scroll down until you find: "Microsoft DAO X.X Object Library" (The X.X will probably be "3.6", but select the highest number in the list.) Click in the check box, then click the "OK button"
Hope this helps,
 

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
tblRawData has all the data of what? Do you want me toleave the fld16 blank?

- tblRawData16 has no data. Right?

- I created a new module, added a sub and endsub with a macro name (e.g. AddMid). This was not mentioned on your reply.
- Add the check to the DAO you mentioned (was 3.6).

Just need to know the table layout and contents and then I'll run it.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,675
Messages
5,549,363
Members
410,911
Latest member
AniEx
Top