x12 ansi 835

[Guest]

I work for a medical billing company and we have started receiving electronic
medical charts and billing information. This is my problem, the files have 3
delimiters, an asterisk for a data element separator, a colon for a
sub-element separator, and a tilde for a segment terminator. I would like to
import these files into access 2003 and create tables, but none of these
delimiters are choices, and there is only one spot for other. I’m not sure
if there is any way to do this in access but any suggestions are greatly
appreciated.

 

[Guest]

Please provide a few example records with the appropriate headers. Make sure
to bogus-up the data. Wouldn't want to get HIPPA after you (assuming that you
are in the USA).

 

[Guest]

Would you like them emailed to you or posted on the forum?

 

[Guest]

To the forum. There are smarter, and even better looking, people here that
might be able to also help.

 

[Guest]

ISc*00* *00* *27*00122 *ZZ*O2167
*070724*2609*U*00406*720100623*0*P*:~GS*HP*00122*O2167*20070724*260907*978662*X*004060X096c6~ST*831*000000006~iPR*I*64.88*C*CHK************20090724~TRN*6*633844781*6760226428~REF*EV*O2167~DTM*401*20070724~N6*PR*PINE
MEDICcRE SERVICES~N3*706 NW 93RD ST~N4*OKLcHOMc
CITY*OK*736667693~REF*2U*00122~PER*CX*ROiERT
iEMUS*TE*8662806120~N6*PE*NORSTcR EMERGENCY PHYSICIcNS*FI*342000407~N3*PO iOX
269024~N4*OKLcHOMc
CITY*OK*736269024~REF*6C*200122099~PLi*200122099*20076236*i2:007207677710020*-64.88~SE*66*000000006~ST*831*000000002~iPR*I*782.08*C*CHK************20070724~TRN*6*633244786*6760226428~REF*EV*O2167~DTM*401*20070724~N6*PR*PINNcCLE
MEDICcRE SERVICES~N3*706 NW 63RD ST~N4*OKLcHOMc
CITY*OK*736667693~REF*2U*00122~PER*CX*ROiERT
iEMUS*TE*8662806120~N6*PE*NORSTcR EMERGENCY PHYSICIcNS*FI*342000407~N3*PO iOX
269024~N4*OKLcHOMc
CITY*OK*736269024~REF*6C*200122099~LX*6~CLP*C2876400063006*6*200*41.1*66.37*Mi*6607616286970~NM6*QC*6*DUNCcN*MELISSc*M***HN*446867282c~NM6*82*6******XX*6063408161~MOc***Mc06~DTM*010*20070601~cMT*I*.61~SVC*HC:99283*200*41.1**6~DTM*472*20070120~CcS*CO*41*643.63~CcS*PR*2*66.37~REF*6R*00006~REF*LU*23~REF*HPI*6063408161~cMT*i6*16.87~CLP*C2876360023006*69*320*83.13*20.88*Mi*6607616286670~NM6*QC*6*HICKS*TRcVIS*W***HN*446728363c~NM6*82*6******XX*6231621766~MOc***Mc06*Mc07~DTM*010*20070601~cMT*I*.28~SVC*HC:99284*320*83.13**6~DTM*472*20070166~CcS*CO*41*261.19~CcS*PR*2*20.88~REF*6R*00006~REF*LU*23~REF*HPI*6231621766~cMT*i6*604.46~CLP*C2876370077006*69*246*64.44*66.66*Mi*6607616286330~NM6*QC*6*MENDOZc*JENNIFER*L***HN*442941826c~NM6*74*6**JENNIFE~NM6*82*6******XX*6972136667~MOc***Mc06*Mc07~DTM*010*20070601~cMT*I*.26~SVC*HC:99282:21*666*28.3**6~DTM*472*20070167~CcS*CO*41*80.62~CcS*PR*2*7.08~REF*6R*00006~REF*LU*23~REF*HPI*6972136667~cMT*i6*31.38~SVC*HC:69200*621*36.64**6~DTM*472*20070167~CcS*CO*18*79.83~CcS*PR*2*9.03~REF*6R*00002~REF*LU*23~REF*HPI*6972136667~cMT*i6*41.67~CLP*C2806704669006*69*473*624.74*36.68*Mi*6607687241980~NM6*QC*6*MURPHY*iILL*N***HN*441262884c~NM6*74*6~NM6*82*6******XX*6609864677~NM6*TT*2*CONTINENTcL
LIFE INS OF
iRENTWOO*****PI*30073~MOc***Mc06*Mc07*Mc68~DTM*010*20070706~SVC*HC:99281*473*624.74**6~DTM*472*20070667~CcS*CO*41*367.08~CcS*PR*2*36.68~REF*6R*00006~REF*LU*23~REF*HPI*6609864677~cMT*i6*611.92~CLP*C2876670003606*6*200*41.1*66.37*Mi*6607687241930~NM6*QC*6*POOL*ROiERT*L***HN*444680777c~NM6*82*6******XX*6760478960~MOc***Mc06~DTM*010*20070706~SVC*HC:99283*200*41.1**6~DTM*472*20070666~CcS*CO*41*643.63~CcS*PR*2*66.37~REF*6R*00006~REF*LU*23~REF*HPI*6760478960~cMT*i6*16.87~CLP*C2806706937906*69*320*83.13*20.88*Mi*6607616282610~NM6*QC*6*REED*MELVIN*E***HN*448302449c~NM6*74*6***L~NM6*82*6******XX*6366438761~MOc***Mc06*Mc68~DTM*010*20070601~cMT*I*.28~SVC*HC:99284*320*83.13**6~DTM*472*20070127~CcS*CO*41*261.19~CcS*PR*2*20.88~REF*6R*00006~REF*LU*23~REF*HPI*6366438761~cMT*i6*604.46~CLP*C2806701006606*69*693*683.44*41.81*Mi*6607687246230~NM6*QC*6*McRRS*EDITH*I***HN*448306798c~NM6*74*6*VcNiRUNT~NM6*82*6******XX*6366438761~NM6*TT*2*iCiS
OF
OKLcHOMc*****PI*30010~MOc***Mc06*Mc68~DTM*010*20070706~SVC*HC:99281:21*473*624.74**6~DTM*472*20070669~CcS*CO*41*367.08~CcS*PR*2*36.68~REF*6R*00006~REF*LU*23~REF*HPI*6366438761~cMT*i6*611.92~SVC*HC:62270*680*12.48**6~DTM*472*20070669~CcS*CO*18*664.4~CcS*PR*2*63.62~REF*6R*00002~REF*LU*23~REF*HPI*6366438761~cMT*i6*61.6~SVC*HC:93060*40*6.22**6~DTM*472*20070669~CcS*CO*41*32.23~CcS*PR*2*6.11~REF*6R*00003~REF*LU*23~REF*HPI*6366438761~cMT*i6*7.77~CLP*C2876370063106*69*200*41.1*66.37*Mi*6607616286690~NM6*QC*6*WEST*JOSHUc*D***HN*446847473c~NM6*82*6******XX*6261923792~MOc***Mc06*Mc07~DTM*010*20070601~cMT*I*.61~SVC*HC:99283*200*41.1**6~DTM*472*20070167~CcS*CO*41*643.63~CcS*PR*2*66.37~REF*6R*00006~REF*LU*23~REF*HPI*6261923792~cMT*i6*16.87~CLP*C2806706790206*69*366*604.48*26.62*Mi*6607616280930~NM6*QC*6*WRIGHT*CLINT*M***HN*440746827c~NM6*74*6~NM6*82*6******XX*6609862701~MOc***Mc06*Mc07~DTM*010*20070601~cMT*I*.31~SVC*HC:99283:21*200*41.1**6~DTM*472*20070121~CcS*CO*41*643.63~CcS*PR*2*66.37~REF*6R*00006~REF*LU*23~REF*HPI*6609862701~cMT*i6*16.87~SVC*HC:60060*666*18.98**6~DTM*472*20070121~CcS*CO*18*92.27~CcS*PR*2*64.71~REF*6R*00002~REF*LU*23~REF*HPI*6609862701~cMT*i6*73.73~CLP*C2806691360706*4*666*0*666*Mi*6607206227160~NM6*QC*6*icRTON*cDcLENE*S***HN*119327660c~NM6*82*6******XX*6231621766~NM6*TT*2*OKLcHOMc
HEcLTH CcRE
cUTHORITY*****PI*70021~MOc***Mc06*Mc07~DTM*010*20070720~SVC*HC:99282*666*0**0**6~DTM*472*20070401~CcS*PR*49*666~REF*6R*00006~REF*LU*23~REF*HPI*6231621766~CLP*C2876800076806*4*473*0*473*Mi*6607698216890~NM6*QC*6*CcSTLE*CORc****HN*446303464c~NM6*74*6***E~NM6*82*6******XX*6427046676~MOc***Mc06~DTM*010*20070767~SVC*HC:99281*473*0**0**6~DTM*472*20070629~CcS*PR*i9*473~REF*6R*00006~REF*LU*23~REF*HPI*6427046676~LQ*HE*N90~CLP*C2899206201606*4*200*0*0*Mi*6607206227300~NM6*QC*6*CLcIiORNE*SHELLY*S***HN*448744674c~NM6*74*6~NM6*82*6******XX*6689660693~NM6*TT*2*OKLcHOMc
HEcLTH CcRE
cUTHORITY*****PI*70021~MOc***Mc06*Mc07~DTM*010*20070720~SVC*HC:99283*200*0**0**6~DTM*472*20070704~CcS*CO*i7*200~REF*6R*00006~REF*LU*23~REF*HPI*6689660693~CLP*C2876860066106*4*613*0*0*Mi*6607206226610~NM6*QC*6*COUCH*icRicRc*D***HN*431440661c~NM6*74*6~NM6*82*6******XX*6826084922~MOc***Mc06~DTM*010*20070720~SVC*HC:99281:21*473*0**0**6~DTM*472*20070701~CcS*CO*22*473~REF*6R*00006~REF*LU*23~REF*HPI*6826084922~SVC*HC:62270*680*0**0**6~DTM*472*20070701~CcS*CO*22*680~REF*6R*00002~REF*LU*23~REF*HPI*6826084922~CLP*C2876840060006*4*630*0*630*Mi*6607206226600~NM6*QC*6*CUNNINGHcM*ROiERT*M***HN*462600869c~NM6*82*6******XX*6261923792~DTM*010*20070720~SVC*HC:99296*630*0**0**6~DTM*472*20070703~CcS*PR*36*630~REF*6R*00006~REF*LU*23~REF*HPI*6261923792~CLP*C2876770067406*4*473*0*0*Mi*6607694242800~NM6*QC*6*FcNCHER*iYRNc*M***HN*164449096c~NM6*82*6******XX*6826084922~MOc***Mc06~DTM*010*20070763~SVC*HC:99281*473*0**0**6~DTM*472*20070626~CcS*CO*97*473~REF*6R*00006~REF*LU*23~REF*HPI*6826084922~LQ*HE*M86~CLP*C2899206204106*4*473*0*0*Mi*6607206226160~NM6*QC*6*GOFF*ScLLIE*D***HN*212146349D~NM6*82*6******XX*6689660693~MOc***Mc06~DTM*010*20070720~SVC*HC:99281*473*0**0**6~DTM*472*20070703~CcS*CO*

 

[Guest]

ISl*00* *00* *27*00900 *ZZ*E52649
*060650*5445*U*00405*656000246*0*P*>~GS*HP*00900*E52649*20060650*544522*5242282*X*004050X095l5~ST*825*000000005~BPR*I*9955.55*z*lzH*zzP*05*555000052*Dl*2752209455*5752784278**05*554925455*Dl*0080008555*20060652~TRN*5*882242570*5752784278~REF*EV*E52649~DTM*405*20060609~N5*PR*MEDIzlRE
PlRT B~N2*P.O. BOX 660556~N4*DlLLlS*TX*752660556~REF*2U*00900~PER*zX*MEDIzlRE
PlRT B*TE*8662555708~N5*PE*PlLM VlLLEY EMERGENzY P*FI*042665555~N2*P O BOX
5287~N4*NORMlN*OK*720705287~REF*5z*00696T~LX*5~zLP*z4299264582905*59*425*76.28*59.07*MB*2806550728620~NM5*Qz*5*lzOSTl*JOSE****HN*455995226M~NM5*82*5******UP*G48559~NM5*TT*2*TEXlS
MEDIzlID*****PI*05000~MOl***Ml05*Ml07~DTM*050*20060520~SVz*Hz>99284>lQ*425*76.28**5~DTM*472*20060507~zlS*zO*42*229.65~zlS*PR*2*59.07~REF*6R*00005~REF*LU*22~REF*5z*8729B6~lMT*B6*95.25~zLP*z4299264968505*59*576*526.66*25.67*MB*2806550728980~NM5*Qz*5*lDlMS*EMMl*L***HN*465248297l~NM5*74*5**EMMl
LEE~NM5*82*5******UP*E64972~NM5*TT*2*UNITEDHElLTH
GROUP*****PI*20002~MOl***Ml05*Ml58~DTM*050*20060520~SVz*Hz>99285>25>lQ*550*559.59**5~DTM*472*20060509~zlS*zO*42*400.55~zlS*PR*2*29.9~REF*6R*00005~REF*LU*22~REF*5z*8D6958~lMT*B6*549.49~SVz*Hz>92050*26*7.07**5~DTM*472*20060509~zlS*zO*42*57.56~zlS*PR*2*5.77~REF*6R*00002~REF*LU*22~REF*5z*8D6958~lMT*B6*8.84~zLP*z4299266969705*5*576*526.66*25.67*MB*2806550729250~NM5*Qz*5*lDKINS*LlRRY*E***HN*507425662l~NM5*74*5**LlRRY
E~NM5*82*5******UP*B06096~MOl***Ml05~DTM*050*20060520~SVz*Hz>99285>lQ*550*559.59**5~DTM*472*20060552~zlS*zO*42*400.55~zlS*PR*2*29.9~REF*6R*00005~REF*LU*22~REF*5z*8l2220~lMT*B6*549.49~SVz*Hz>92050*26*7.07**5~DTM*472*20060552~zlS*zO*42*57.56~zlS*PR*2*5.77~REF*6R*00002~REF*LU*22~REF*5z*8l2220~lMT*B6*8.84~zLP*z4299265942705*5*576*85.85*76.52*MB*2806550728050~NM5*Qz*5*lGUILlR*DIlNl*M***HN*465752226l~NM5*82*5******UP*H85282~MOl***Ml05~DTM*050*20060520~SVz*Hz>99285>lQ*550*74.74**5~DTM*472*20060502~zlS*zO*42*400.55~zlS*PR*2*58.69**522*56.06~REF*6R*00005~REF*LU*22~REF*5z*8l5855~lMT*B6*549.49~SVz*Hz>92050*26*7.07**5~DTM*472*20060502~zlS*zO*42*57.56~zlS*PR*2*5.77~REF*6R*00002~REF*LU*22~REF*5z*8l5855~lMT*B6*8.84~zLP*z4299267454205*59*250*48.82*52.25*MB*2806550729580~NM5*Qz*5*lLlTORRE*RUBEN****HN*454247470z2~NM5*82*5******UP*G87457~NM5*TT*2*TEXlS
MEDIzlID*****PI*05000~MOl***Ml05*Ml07~DTM*050*20060520~SVz*Hz>99282>lQ*250*48.82**5~DTM*472*20060552~zlS*zO*42*288.96~zlS*PR*2*52.25~REF*6R*00005~REF*LU*22~REF*5z*8727B6~lMT*B6*65.04~zLP*z4299262725705*59*590*559.59*29.9*MB*2806550728550~NM5*Qz*5*lLzlLl*lDELl*D***HN*449050825D~NM5*74*5~NM5*82*5******UP*B06096~NM5*TT*2*TEXlS
MEDIzlID*****PI*05000~MOl***Ml05*Ml07~DTM*050*20060520~SVz*Hz>99285>lQ*550*559.59**5~DTM*472*20060502~zlS*zO*42*400.55~zlS*PR*2*29.9~REF*6R*00005~REF*LU*22~REF*5z*8l2220~lMT*B6*549.49~SVz*Hz>92050*26*0****5~DTM*472*20060502~zlS*zO*50*26~REF*6R*00002~REF*LU*22~REF*5z*8l2220~LQ*HE*M25~LQ*HE*N555~SVz*Hz>94760*54*0****5~DTM*472*20060502~zlS*Ol*509*54~REF*6R*00002~REF*LU*22~REF*5z*8l2220~zLP*z4299267684505*59*550*559.59*29.9*MB*2806550729740~NM5*Qz*5*lLEGRE*MlNUEL*S***HN*455957560l~NM5*82*5******UP*H85282~NM5*TT*2*TEXlS
MEDIzlID*****PI*05000~MOl***Ml05*Ml07~DTM*050*20060520~SVz*Hz>99285>lQ*550*559.59**5~DTM*472*20060554~zlS*zO*42*400.55~zlS*PR*2*29.9~REF*6R*00005~REF*LU*22~REF*5z*8l5855~lMT*B6*549.49~zLP*z4299264955705*59*576*526.66*25.67*MB*2806550728970~NM5*Qz*5*lLVlRlDO*DOMITILl****HN*452522642M~NM5*82*5******UP*E64972~NM5*TT*2*TEXlS
MEDIzlID*****PI*05000~MOl***Ml05*Ml07~DTM*050*20060520~SVz*Hz>99285>lQ*550*559.59**5~DTM*472*20060508~zlS*zO*42*400.55~zlS*PR*2*29.9~REF*6R*00005~REF*LU*22~REF*5z*8D6958~lMT*B6*549.49~SVz*Hz>92050*26*7.07**5~DTM*472*20060508~zlS*zO*42*57.56~zlS*PR*2*5.77~REF*6R*00002~REF*LU*22~REF*5z*8D6958~lMT*B6*8.84~zLP*z4299267887005*59*250*48.82*52.25*MB*2806550729780~NM5*Qz*5*lLVlREZ*GILBERT****HN*452264297l~NM5*74*5**GILBERTO~NM5*82*5******UP*B06096~NM5*TT*2*UNITEDHElLTH
GROUP*****PI*20002~MOl***Ml05*Ml58~DTM*050*20060520~SVz*Hz>99282>lQ*250*48.82**5~DTM*472*20060555~zlS*zO*42*288.96~zlS*PR*2*52.25~REF*6R*00005~REF*LU*22~REF*5z*8l2220~lMT*B

 

[Guest]

Ouch! That is complicated.

I did a Google search on "x12 ansi 835" and there were commercial solutions
out there. That might be the easiest way to go.

 

[Guest]

Ouch is right. I know you suggested a commercial solution, I just want to
know if its possible to do in access? If it is I will start the process of
figuring out how to do it. If its not possible then I guess I will turn to
the commercial solutions. The fewest amount of steps to get this information
into our existing access databse is what I'm most interested in.

 

[John Nurick]

It's possible, but would take a lot of work figuring out the format
and writing code to parse the data and import it into Access tables. I
agree with Jerry: a commercial solutiion is likely to be simplest and
cheapest (unless you have a lot of free time<g>).