February 17, 2009 at 1:36 pm
We receive files from our clients that have, in addition to header and trailer records, what I can best call sub-header and sub-trailer records. This is from an NCPDP spec if anyone has experience with that. The data is pharmacy claims data and is sub totaled by each pharmacy it would look something like this:
0 52 X 89389sk header record describing client
2 3 90883939 x iiw (this record would be the sub header
4 20090210 009884838 030003 (this record would be claims record I need)
4 20090210 009884838 030004 (another claim record)
6 2 28343k (sub traile record for this pharmacy)
9 x7892 09909 Trailer record for client
The header, sub header, claims, sub trailer and trailer records are of varying lengths. I would like to import only rows starting with '4'. There is some valuable data in the header and trailer records so if I could write those to another table that would be cool but I really need to get to the claims records.
Thanks in advance for any guidance you can provide
February 18, 2009 at 12:03 am
can u pls tell that all those data comes in which file means in txt or in excel....
Raj Acharya
February 18, 2009 at 7:30 am
Can't you just use varchar(max) and import the records into a single column. If you import the first char to a different column you could use that to select out all the type 4s for processing and do what you like with the 0, 2, 6 and 9 records
Something like
create table testimport (tid int identity(1,1),trec char(1), tdata varchar(max))
-- Import text file using import wizard
select tid, trec, tdata from testimport
Gives
tid trec tdata
----------- ---- --------------------------------------------------------------------------
1 0 52 X 89389sk header record describing client
2 2 3 90883939 x iiw (this record would be the sub header
3 4 20090210 009884838 030003 (this record would be claims record I need)
4 4 20090210 009884838 030004 (another claim record)
5 6 2 28343k (sub traile record for this pharmacy)
6 9 x7892 09909 Trailer record for client
regards
Tony
February 18, 2009 at 7:34 am
the data is coming in a fixed width text file
February 18, 2009 at 7:36 am
Tony,
Thanks for taking the time to respond
That is basically how I am handling this now. I import in one char (first char) & one big column then delete the rows that don't have a 4 in the first column. Then I export and then reimport using a template.
I was hoping I could find a single step solution.
February 18, 2009 at 8:16 am
Once all the data is in the staging table I don't understand the need to delete records and then export/re-import using a template. Are you doing this just to unpick the data items for your type 4 records? Can't you just pick the data items out of single large column where the record type is a 4?
eg
select substring(tdata,2,8) as adate,
substring(tdata,11,9) as data1,
substring(tdata,21,6) as data2
from testimport
where trec = 4
I'm guessing that real volumes and real record complexities might make this more of a chore than I'm seeing.
February 18, 2009 at 8:39 am
Tony,
Good point, I really don't need to delete those records, I can select out. I never thought of using a select substring to get the data in the format. We usually get 200k records and occasionally up to 25 million records. Depending on the vendor the record is between 350 to 850 characters wide. I have a template built in a DTS package that makes quick work of this. Again, I wanted to use one step and didn't know if this could be done in DTS. Always looking for performance gains!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply