Create one record with a file with some records related to the same record

[rhector31]

Hi, I have this file that some lines are related to the same record. I need to analize it but I need to create a flat file. I want to every time that the record start with an ENT split it and add to the right the records below the ENT.

ENT*3*2J*34*555 555555~
NM1*QE*1*FIGUER OA~
RMR*11*DB020920 002010080013644 378028**0~
DTM*582****RD8* 20060801-20060831~
RMR*IK*47A0612* *0*0~
ADX*.00*IA~
ENT*4*2J*34*999 999999~
NM1*QE*1*FIGUER OA~
RMR*11*DB020920 002010080013644 378028**0~
DTM*582****RD8* 20060901-20060930~
RMR*IK*47A0612* *0*0~
ADX*.00*IA~

Iwant the file like this:
ENT*3*2J*34*555 555555~NM1*QE*1 *FIGUEROA~RMR*1 1*DB02092000201 008001364437802 8**0~DTM*582*** *RD8*20060801-20060831~RMR*IK *47A0612**0*0~A DX*.00*IA~
ENT*4*2J*34*999 999999~NM1*QE*1 *FIGUEROA~RMR*1 1*DB02092000201 008001364437802 8**0~DTM*582*** *RD8*20060901-20060930~RMR*IK *47A0612**0*0~A DX*.00*IA~

Well, all the data in one record.

I apreciate if someone can help me. I only know how to use access.

[Jim Doherty]

Hi, I have this file that some lines are related to the same record. I need to analize it but I need to create a flat file. I want to every time that the record start with an ENT split it and add to the right the records below the ENT.

ENT*3*2J*34*555 555555~
NM1*QE*1*FIGUER OA~
RMR*11*DB020920 002010080013644 378028**0~
DTM*582****RD8* 20060801-20060831~
RMR*IK*47A0612* *0*0~
ADX*.00*IA~
ENT*4*2J*34*999 999999~
NM1*QE*1*FIGUER OA~
RMR*11*DB020920 002010080013644 378028**0~
DTM*582****RD8* 20060901-20060930~
RMR*IK*47A0612* *0*0~
ADX*.00*IA~

Iwant the file like this:
ENT*3*2J*34*555 555555~NM1*QE*1 *FIGUEROA~RMR*1 1*DB02092000201 008001364437802 8**0~DTM*582*** *RD8*20060801-20060831~RMR*IK *47A0612**0*0~A DX*.00*IA~
ENT*4*2J*34*999 999999~NM1*QE*1 *FIGUEROA~RMR*1 1*DB02092000201 008001364437802 8**0~DTM*582*** *RD8*20060901-20060930~RMR*IK *47A0612**0*0~A DX*.00*IA~

Well, all the data in one record.

I apreciate if someone can help me. I only know how to use access.

Hi,

You may only know how to use Access but my guess is you might have used 'excel' as well which gives an easy approach for some prep work on your data prior to working in Access if you wish to take advantage of that and that is to open in in excel first, then use the menubar command 'Data...Text to Columns' which automatically parses out your data for you into separate columns based on a delimiter. That way you readly see how your data is presented to you as columns.

Once prepared you could then easily attach the table to Access and run a query over the top of that to extract into a new table all records where either the first three charaters equals ENT or by the looks of that data the first column would be ENT on its own anyway.

If you don't want to use excel then yes Access can do it anyway by merely linking to the datafile if it is a textfile on disk attachng it to the Access database window.

Have a look at the database window menubar command...file. ..get external data then select 'files of type' dropdown and then 'text file' you will be presented with a wizardry designed to ask of you how the data should be parsed into separate columns. The result is a non editable attached table presented to you in the database window from which you can extract into a new table records of your chioce based on any query you may then create on that attached table.

From the looks of that data your first column will be a three letter uppercase denotation of ENT (amongst other values) from which you can then extract all records to suit your purpose ie: SELECT * FROM My Table where column1="ENT"

Regards

Jim :)