How to Import file which contains data with different formats 1

[srvu]

Hi

I have a flatfile which contains data in different formats.
like fixedwidth,delimited etc

How can we import the file

Thanks in advance

 

[ArtieChoke]

PC can only handle a file where every row has the same format.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[srvu]

Hi

The Flat file contains data with three different types of fiels structure or format

1 record is processor record with some field names & format(fixedwidth)
2 record is Pharmacyrecord with different field names
From record 3 the records are claim records

Now,I have to extract 1 record & sen it to one table1,
2 record to table2
from 3rd to table3

How can i extract these from a single file??

could anyone help me with this

 

[srvu]

we have 1st record as header
2nd record for each store claims(1 record per each store)
and
3rd claims for that store
and repeats for store2
store 2 details
from next claims for that store
.....


that continues
so i need to send thid data to 3 tables
processor,pharma,claims

 

[ArtieChoke]

Not in PC. You'll have to split it up to 3 files. PC requires the same format for each row in the file, unfortunately.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[srvu]

How do we Import 3 different records with different field names from single flat file

I tried to import them 3 times,each time skipping the records
Like first time import from line 1
Second import import from line2
third time import from line 3
Giving different related field names to records each time i import
Is that correct??
how do i send them to 3 different tables

 

[ArtieChoke]

If you're lucky enough to have a record type in the first field or at least in the same place in each record, you might be able to do it that way. You will need to have 3 sources, one for each record type. Then filter on the record type you want to use in the map. You will also need 3 targets and 3 maps, as you will need to use the different source definitions. You can't do it all in one map.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[srvu]

Every record is having a record identifier in the first field
For Header record RecordIdentifier=0(1 per file will be there)
For second or Pharm record RecordIdentifier=6(1 record per pharm will be there)
For Third onwards claim recor RecordIdentifier=4(claims for that particular pharm will be under each pharm)

So i tried to apply condition in Exp like IIF(RecordIdentifier='0',1,0)
1-->for insert
0-->for reject

But it didn't work
according to above condition 1 record should go to target but thats not the case values are wrong,all zeros are going to header
I even applied agg trans for header record
incorrect values are entered

 

[ArtieChoke]

How did you define the source for the file?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[srvu]

i got some sample data,which is fixedwidth, i imported that first from line one & gave field names that are to be given for processor.

Second time i imported the same file starting at line2,this time it is pharm records & field names related to pharma

third import was done on same strting from line 3 & given field names related to claims

All the 3 record types are having 1st column similar that is recordidentifier with values like mentioned above

 

[ArtieChoke]

Unfortunately, it's really hard to break this down from a graphical setup to text. Here's how I would do it:

- Make a source for each record type
- Make a target for each record type/table
- Make a map for each table
- In the source qualifier, filter on the record type (can't remember if that's possible, if not, use a filter transform)
- Map the source to the appropriate target in 3 separate maps

You do not need to do anything else. Validate that your source objects represents the data properly in each case.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[srvu]

Actually i proceeded in the same way and expected the result.
I have created 3 different tables & for now tables are not ready so i tried a sample map with flat files as target.

There the conditions are not satisfying,i suppose it is due to file target.

If it would have been Table then it might not allow rows other than the rows which satisfy the condition

Any way thanks a lot for the replies

 

[srvu]

If a column with length 1 is added to the imported file then the output is correct.(should add column with any name in source)
Its extracting the correct no.of rows according to the filter condition given.

NOTE: need not map the added column to target or filter

This should be done for Fixed width files