Importing into the database but..

[denissedonnelly]

When importing the x12 (837 for example) into the database is there the ability to export and recreate the x12 that was imported?

Thanks!

Denisse

[nwlynch]

Yes there is.
What language did you want to use to generate the X12?

I have done it with Excel VBA.

[denissedonnelly]

What I'm trying to accomplish is importing the data into a SQL database and later recreating the same 837 x12 file by exporting it.

I will have to double check but I thought I also wasn't able to successfully import into my SQL database that I was testing with.

[nwlynch]

Yes, once you get it into the DB, you can extract specific X12 claims by using a transaction number that ties the segments all together.

I'd offer some example code if I knew what you were coding the extract application in.

[denissedonnelly]

There were a few different applications I was trying to test but I think with this I didn't have success getting the claims into the database.

I will try again in a couple of hours when I'm done around the house. With the extraction part are you able to do it based on file and not the transaction number?

Thanks for your help as it's much appreciated!

[nwlynch]

Hi,
If you keep track of the file used to create the transaction set that the claim is comprosed of, you can easily pick what you need. I'll check it again for more specific help tomorrow.

Meanwhile, good luck with the importing which should work almost magically if you have adequate rights to the DB.

Cheers,
Neville

[denissedonnelly]

It's my personal database so i have dba rights. I don't recall there being any specifications that can be added to the config file or does the server/database name info need to be added in the command line?

[denissedonnelly]

Please forgive me but I'm honestly not that familiar with Visual Studio. Maybe if I also explain what I'm trying to do that will be helpful as well.

1. I want to import 837s (P, I, and D) into a database to pull some of the data from it and along with adding a couple of segments.
2. Once the additional segments are added I would want to recreate that file with the additional segment.

I was able to import some test files along with identifying the various tables/columns I need to extract some of the data.

My next question is how would I create a 837 export from what was imported and also it is possible to keep the same file name? The import process was very quick.

I do wish i happened to have a file table which also lists the total claims per file as that would be helpful. I'm happy for how this works though. Just wanted try to figure out how to actually create a file from what's in there now.

I believe that was a table created from MDMS17/837Parser's application. Anyway, not making a comparison as this is great too. Just gotta figure out how to export it.

I need to get ready for work but I will check to see if I get any responses later today.

Thanks so much.

[nwlynch]

Hi,
There are stored procedures created by the code that let you grab a whole file worth of segments that can be written out to the file system with whatever name you like.

Unfortunately I don't have the exact information for you right now but you need the Transaction Set ID from when you imported the file and then get the line terminator record with this:
sql = "SELECT TOP 1 SegmentTerminator FROM dbo.Interchange WHERE Id = " & TransactionSetID - 2 & ";"
And get the rest of the segments that were imported from that file with this:
sql = "SELECT [Segment] from dbo.GetTransactionSetSegments(" & TransactionSetID & ",1,1);"

Then I write the records out line by line and add the terminator. I did this because the stored segments don't have the terminator.

Good luck and let me know how you go.

[denissedonnelly]

Was the stored procedure included with it? I don't believe I got any stored procedures with the zip file. I didn't see that. I will look around to see if I can find anything else.

Thanks.

[nwlynch]

Hi,
Sorry, it was a function, not a Stored Procedure and yes it is created when the first Import is run.

Hope that helps.

[nwlynch]

Also, the filename is stored in dbo.Interchange.FileName so you can retrieve the Transaction Set ID as:
select ID from dbo.Interchange where FileName="YourFile.edi";

Then use the Transaction Set ID to retrieve the segments as per my earlier message.

[nwlynch]

If you use SELECT SEGMENT FROM GetTransactionSetSegments(3,1,1);
You will get all of the segments for the file loaded with transaction ID of 3.
Just push them out to a file and you will have a new file from the DB.
If you want to load them into a system, you may have to change service dates etc. to prevent rejections due to duplication.

[denissedonnelly]

That's helpful. I tested it and it pulled the one claim record I tested. What if I wanted to pull the whole claim file? What would I do to replace the first value in the ().

Also have you ever added a segment? The additional segment I was going to add is a REF*LU which would go under the REF table. Is there a feature that will recalculate the values if you add a value?

Thanks again for your help.

[denissedonnelly]

Have you ever had to deal with a file that didnt have st/se lines per claim and instead just one st and one se for the complete file?

Thx

[nwlynch]

Regarding the addition of a REF segment, I've added segments manually and recalculated the line count value on the footer but not programmatically yet. As LU is situational, I'm curious as to how often would you need it?

For the data with one ST/SE set, that just sounds like a set of claims for one provider. You can include or exclude the transaction set control lines with one of the parameters of the getTransactionSetSegments function when you recreate the file.

You can also take segments from a file with one of the programs in the project. I only played with it once though and will have to take a look at it again.

What system are you loading into?

[nwlynch]

It's the UnbundleX12 package that lets you grab segments from a file.

[denissedonnelly]

Database: MS SQL Server
Claim Processing System: LuminX

The ref segment would be added to every claim with regards to being either an identifier or an error message so in theory every claim record would have them. I was able to insert the segment but obviously the positioninchange and parentloopid are not correct as I'm just copying the values from the EI value.

Are you recalculating the line value on both the individual claim record and the total file line count as well?

With the file with only one ST/SE i wish it was for just one provider however it's not. It's actually for one trading partner and that's how they send the data. Basically 4k claims all in between one ST/SE. I wonder if there is a way to add the individual ST/SE piece to each claim record.

[denissedonnelly]

I might have mistated on yesterday when I was asking about pulling a complete file. I would setup the process to pull one file at a time so the interchange value would always be the same techincally so pulling all the claims from that one file. Let me gather my thoughts a little after messing with that function and I will try to ask some better questions to see if you have some additional guidance. Thanks.

[denissedonnelly]

I did by the way figure out how to extract the file using GetTransactionSetSegments successfully.

[nwlynch]

Now, as you extract the data from the DB, add the new segment as you write the file, recalculate the line count and update the footer.

And if you save it with the name you want, you have everything you need for now.

[denissedonnelly]

With GetTransactionSetSegments the only issue I see and I'm not sure if I missed something but it's missing the ~ on each line. Not sure if I did something incorrectly or not.

I tested 11 files and 10 seemed fine except they were all missing the ~ as mentioned above.

On the 11th file very oddly it mixed up the segments somehow.

The original started off like this:
ISA**2002041652^005010035196050P*>~
GS*
ST*
BHT*
PER*
NM1*
HL*
NM1*
N3*
N4*
REF*
PER*
NM1*
N3*
N4*
HL*
SBR*
NM1*
NM1*
HL*
PAT*
NM1*
N3*
N4*
DMG*
CLM*
REF*
HI*
NM1*
NM1*
PRV*
NM1*
N3*
N4*
LX*
SV1*
DTP*
REF*
SE*

The recreated one is like this:
NM1*
ST*
GS*
NM1*
N3*
SBR*
PER*
PAT*
N4*
ISA*
NM1*
HL*
N3*
REF*
NM1*
BHT*
REF*
N3*
N4*
NM1*
HL*
NM11
NM140
N3*
CLM*
PER*
N3*
N4*
NM1*
SBR*
NM1*
SV1*
SV1*
REF*
N4*
REF*
LX*
HL*
NM1*
HL*
SE*

Appears there is also an additional value in there somewhere. I'm not that worried about that though as I was able to get the other 10 to process fine for the most part.

These are the parts I'm really stuck on:

1. Adding the ~ when creating the extract GetTransactionSetSegments
   The query I'm using is "select segment from GetTransactionSetSegments(1,2,0)"

2. Adding the REF*LU segment to every claim record and also updating the REF table to where all the values are numbered properly. From there updating the segment table properly so that when GetTransactionSetSegments is executed it will pull the new information as well with the claim/file trailer counts being correct.

3. The claim file with just one ST/SE. I tried to start with updating the TransactionSet first by inserting an additional line for every claim. In the table CLM, the value [01] will always be unique so I was using that as my basis but it didn't since aligning all of the other tables didn't seem to pan out.

Hopefully the name time you respond I will be around so I can respond back quicker as well.

Thanks

[nwlynch]

For #1 you have to either append a hard coded Terminator or retrieve the Terminator from the DB for the transaction set.

I use this:
"SELECT TOP 1 SegmentTerminator FROM dbo.Interchange WHERE Id = " & TransactionSetID - 2 & ";"
 And get the rest of the segments that were imported from that file with this:
 sql = "SELECT [Segment] from dbo.GetTransactionSetSegments(" & TransactionSetID & ",1,1);"
Then I write the records out line by line and add the terminator. I did this because the stored segments don't have the terminator.

I'll look at your other points shortly.

[denissedonnelly]

Where do you place that in the function and does it matter?

I didn't try it yet as I'm away from the PC at the moment. Once again thanks so much for looking at my questions.

[nwlynch]

I just grab a record from the DB, append then Terminator, then write the file line by line. It's primitive right now but met my purpose.

[nwlynch]

Actually, for #1 I create a recordset with the GetTransactionSetSegments function and add the terminator character to each record before saving it in a file.

[nwlynch]

For #2, you could either copy the TransactionSet and add the new segments by inserting them into the TransactionSet Table with new "PositionInInterchange" values so that they are considered part of the TransactionSet, then generate your file from the new table. You will also have to add a ParentLoop value to the segments.

Can you give me a masked example fo the REF*LU segment you want to add and the source of the information that goes into it?

For #3, can you send a masked copy of the X12 file and a hard coded example of an expected result? I may be able to throw something helpful together to get you moving.

[denissedonnelly]

Yep, sure thing. I will send you some samples later today.

Thanks!

[denissedonnelly]

Attached are the sample data.

1. Original file

2. Modified File which includes REF*LU

3. File with only one ST/SE and multiple claims

Sample_20200420.zip

[nwlynch]

Hi,
The file with multiple transaction sets (> 1 ST/SE section) would need to be created by appending one to the other when restoring the DB data to a new file.

Instead of your statement:
"The query I'm using is "select segment from GetTransactionSetSegments(1,2,0)"

select segment from GetTransactionSetSegments(1,0,0)

That would give the set without the envelope ISA header and footer, but I don't have an immediate answer on how to generate those wih any functions that come with the code because i have never thought of doing that before.

For your files, in my DB the Interchange values are like this:

and the TransactionSet values are:

So you would need to combine the transaction sets for the multi ST/SE files if you really need the file to be the same name after you modify the REF*LU. I'll see what I can figure out for that a little later.

[denissedonnelly]

Are you referring to the one labeled as example without ST/SE? I will keep messing around with them myself to see if I can come up with anything as well.

What would really be helpful with the files which have multiple claims between a ST/SE segment would be if I could convert the file to having the ST/SE segments for each claim in the file.

Thanks for your assistance.

[ngupta-bh]

Hi @denissedonnelly, I have a question related to the database which you used to import the incoming SQL. Was it installed thru some tool or you created it manually?

I have a scenario where I need to do data modeling of the X12 data into the database, so if you can share some information regarding it, that will be really helpful.

[denissedonnelly]

Hi @denissedonnelly, I have a question related to the database which you used to import the incoming SQL. Was it installed thru some tool or you created it manually?

I have a scenario where I need to do data modeling of the X12 data into the database, so if you can share some information regarding it, that will be really helpful.

@ngupta-bh , Hi there. Honestly I don't even have the information any longer and not even sure what I ended up doing off the top of my head. Since I needed this for the ability to parse a 837 (I, P, and D) into SQL server I ended up using https://github.com/MDMS17/837Parser which has an importer and exporter. I modified both with a little work to handle what I needed. Works like a charm and still using on a daily basis.