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

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

P: 1
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*555555555~
NM1*QE*1*FIGUEROA~
RMR*11*DB020920002010080013644378028**0~
DTM*582****RD8*20060801-20060831~
RMR*IK*47A0612**0*0~
ADX*.00*IA~
ENT*4*2J*34*999999999~
NM1*QE*1*FIGUEROA~
RMR*11*DB020920002010080013644378028**0~
DTM*582****RD8*20060901-20060930~
RMR*IK*47A0612**0*0~
ADX*.00*IA~

Iwant the file like this:
ENT*3*2J*34*555555555~NM1*QE*1*FIGUEROA~RMR*11*DB0 20920002010080013644378028**0~DTM*582****RD8*20060 801-20060831~RMR*IK*47A0612**0*0~ADX*.00*IA~
ENT*4*2J*34*999999999~NM1*QE*1*FIGUEROA~RMR*11*DB0 20920002010080013644378028**0~DTM*582****RD8*20060 901-20060930~RMR*IK*47A0612**0*0~ADX*.00*IA~

Well, all the data in one record.

I apreciate if someone can help me. I only know how to use access.
Jan 26 '08 #1
Share this Question
Share on Google+
1 Reply


Jim Doherty
Expert 100+
P: 897
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*555555555~
NM1*QE*1*FIGUEROA~
RMR*11*DB020920002010080013644378028**0~
DTM*582****RD8*20060801-20060831~
RMR*IK*47A0612**0*0~
ADX*.00*IA~
ENT*4*2J*34*999999999~
NM1*QE*1*FIGUEROA~
RMR*11*DB020920002010080013644378028**0~
DTM*582****RD8*20060901-20060930~
RMR*IK*47A0612**0*0~
ADX*.00*IA~

Iwant the file like this:
ENT*3*2J*34*555555555~NM1*QE*1*FIGUEROA~RMR*11*DB0 20920002010080013644378028**0~DTM*582****RD8*20060 801-20060831~RMR*IK*47A0612**0*0~ADX*.00*IA~
ENT*4*2J*34*999999999~NM1*QE*1*FIGUEROA~RMR*11*DB0 20920002010080013644378028**0~DTM*582****RD8*20060 901-20060930~RMR*IK*47A0612**0*0~ADX*.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 :)
Jan 26 '08 #2

Post your reply

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