×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to Import file which contains data with different formats

How to Import file which contains data with different formats

How to Import file which contains data with different formats

(OP)
Hi

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

How can we import the file

Thanks in advance

RE: How to Import file which contains data with different formats

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

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

RE: How to Import file which contains data with different formats

(OP)
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



 

RE: How to Import file which contains data with different formats

(OP)
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

RE: How to Import file which contains data with different formats

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 jester

RE: How to Import file which contains data with different formats

(OP)

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


 

RE: How to Import file which contains data with different formats

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 jester

RE: How to Import file which contains data with different formats

(OP)
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
 

RE: How to Import file which contains data with different formats

How did you define the source for the file?

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

RE: How to Import file which contains data with different formats

(OP)
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

RE: How to Import file which contains data with different formats

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 jester

RE: How to Import file which contains data with different formats

(OP)
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

RE: How to Import file which contains data with different formats

(OP)
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  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close