otmfaqForumsBlogsRegister
FAQMembers ListCalendarToday's PostsSearch


 Subscribe Blogs:RSS
 Subscribe Forums:RSS
 Follow New Posts:Twitter
OTMFAQ Home
OTMFAQ Blogs
OTMFAQ Forums
OTM Wiki

OTM SIG
OTM Wiki
MavenWire


Data Loading Loading data into OTM / G-Log through CSVs, XML, and the UI.

Reply
 
Submit Tools LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old August 20th, 2008, 07:09
Junior Member
 
Join Date: Sep 2007
Posts: 21
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 4
Thanked 2 Times in 2 Posts
Rep Power: 0
Dave Lee is on a distinguished road
Tables Requires for data upload via CSV

Hi,

I have compile a list of tables require when uploading configuration data into OTM.

Location

· LOCATION
· CORPORATION
· LOCATION_REFNUM
· LOCATION_CORPORATION
· LOCATION_ADDRESS
· LOCATION_ROLE_PROFILE
· CONTACT

THU

· SHIP_UNIT_SPEC

Equipment

· EQUIPMENT_GROUP
· EQUIPMENT_GROUP_PROFILE
· EQUIPMENT_GROUP_PROFILE_D


Rates

· HNAME_SET
· HNAME_SET_MEMBER
· X_LANE
· LANE
· RATE_SERVICE
· SERVICE_TIME
· RATE_VERSION

Does anyone have the tables for Rate Offerring, Rate Record and Itineries.

Thanks

Regards,
Dave Lee
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following User Says Thank You to Dave Lee For This Useful Post:
chrisplough (May 18th, 2009)
  #2 (permalink)  
Old August 20th, 2008, 18:50
Member
 
Join Date: Jun 2007
Posts: 32
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 0
Thanked 7 Times in 7 Posts
Rep Power: 0
oliver is on a distinguished road
Send a message via AIM to oliver Send a message via Skype™ to oliver
Re: Tables Requires for data upload via CSV

Hi Dave,

i guess the ERD gives you the info that you need, like for itinerary :
- itinerary
- itinerary_detail
- leg
- transport_mode
- region
- etc......

Rgds
Oliver
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following User Says Thank You to oliver For This Useful Post:
chrisplough (May 18th, 2009)
  #3 (permalink)  
Old August 21st, 2008, 01:29
Junior Member
 
Join Date: Sep 2007
Posts: 21
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 4
Thanked 2 Times in 2 Posts
Rep Power: 0
Dave Lee is on a distinguished road
Re: Tables Requires for data upload via CSV

Thanks. Do you know where can i get the ERD from? One more question, for migration between UAT environment to Production environment, is there other better methods beside data upload via CSV in OTM?
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following User Says Thank You to Dave Lee For This Useful Post:
jeff.depaul (August 21st, 2008)
  #4 (permalink)  
Old August 21st, 2008, 03:17
Member
 
Join Date: Jun 2007
Posts: 37
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 1
Thanked 8 Times in 8 Posts
Rep Power: 0
jeff.depaul is on a distinguished road
Send a message via Yahoo to jeff.depaul
Re: Tables Requires for data upload via CSV

Dave,
There is a better way, you can post the data load to the CSVUtil servlet. The call would look something like this:
"java glog.database.admin.CSVUtil -command iu -maxError 1 -connectionId <<db_name>> -dataDir $FILEDIR/ -dataFileName $TABLENAME -runsqlloader -debug -xvalidate Y"

We wrapped the call to this in a shell script which reads the tables in order from another file recursively (similar to what you mentioned above) - specifically we used for loading rates. The program takes some time to get setup just right but once set, you can use it for loading just about any data that you would load csv from the front end.

-Jeff
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following User Says Thank You to jeff.depaul For This Useful Post:
Dave Lee (August 21st, 2008)
  #5 (permalink)  
Old August 21st, 2008, 06:23
Member
 
Join Date: Nov 2007
Location: Chennai, India
Posts: 90
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 10
Thanked 3 Times in 3 Posts
Rep Power: 3
arunk is on a distinguished road
Re: Tables Requires for data upload via CSV

Quote:
Originally Posted by Dave Lee View Post
Thanks. Do you know where can i get the ERD from? One more question, for migration between UAT environment to Production environment, is there other better methods beside data upload via CSV in OTM?
Dave,

FYI - a related post on ERD.
http://www.otmfaq.com/forums/f63/otm-erwin-diagram-702/ (OTM Erwin Diagram) (OTM Erwin Diagram)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following User Says Thank You to arunk For This Useful Post:
Dave Lee (August 21st, 2008)
  #6 (permalink)  
Old March 23rd, 2009, 04:16
Junior Member
 
Join Date: Sep 2007
Posts: 21
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 4
Thanked 2 Times in 2 Posts
Rep Power: 0
Dave Lee is on a distinguished road
Re: Tables Requires for data upload via CSV

I have encountered a problem when i upload Region data.

There are two tables to upload

-REGION
-REGION_DETAIL

Region table is quite straightforward. But Region Detail has a SEQUENCE_NUM column which is giving me a headache. I have tried to leave this field empty and let OTM generate the number but i notice that doing so will result in the sequence_num being assign duplicate. If i manually assign the next seq number in the excel before upload, i am unable to add location to that region thru OTM online screen as it will throw an exception.

Anyone know how to upload data if that table has sequence number? Thanks
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #7 (permalink)  
Old April 3rd, 2009, 13:50
Junior Member
 
Join Date: Apr 2008
Posts: 21
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 0
Thanked 1 Time in 1 Post
Rep Power: 0
kumar is on a distinguished road
Re: Tables Requires for data upload via CSV

Hi all,

While uploading data into OTM via XML, data will go through via I_transmission or I_transaction table like wise when we upload data into OTM via CSV Is there any intermediatry tables are present in OTM? If so, please tell me table names....

Thanks in advance!!!
Kumar
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #8 (permalink)  
Old April 3rd, 2009, 15:17
Senior Member and Blogger
 
Join Date: May 2007
Posts: 200
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 4
Thanked 36 Times in 35 Posts
Rep Power: 3
bmj_23 will become famous soon enough
Re: Tables Requires for data upload via CSV

Hi - when you are uploading data from CSV format in to OTM, there are no intermediary tables that are involved. Either the data gets loaded or it does not (because of PK, FK errors etc) - the data does not get staged (as is the case with i_transmissions and i_transaction) and then processed, it is simply loaded or not.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #9 (permalink)  
Old April 6th, 2009, 03:43
Junior Member
 
Join Date: Sep 2007
Posts: 21
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 4
Thanked 2 Times in 2 Posts
Rep Power: 0
Dave Lee is on a distinguished road
Re: Tables Requires for data upload via CSV

Quote:
Originally Posted by bmj_23 View Post
Hi - when you are uploading data from CSV format in to OTM, there are no intermediary tables that are involved. Either the data gets loaded or it does not (because of PK, FK errors etc) - the data does not get staged (as is the case with i_transmissions and i_transaction) and then processed, it is simply loaded or not.
Yes, this is what i notice also. But what i dun understand is why OTM behave differently when upload CSV vs manually enter it in OTM for tables that uses sequence number. For example, when i tried to upload region via CSV, i notice that the sequence number in region detail table is not the next running number of the last region detail record. When i manually enter it in OTM, the sequence number is the next running number of the last region detail record.
So if i upload region via CSV, i will may have the same sequence number in region details table. OTM can still insert the data because the primary key to this table are
REGION_GID and SEQUENCE_NUM. I am not sure whether having same sequence number will impact any downstream flow?
Thanks


Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #10 (permalink)  
Old April 6th, 2009, 22:45
Senior Member and Blogger
 
Join Date: May 2007
Posts: 200
Groans: 0
Groaned at 0 Times in 0 Posts
Thanks: 4
Thanked 36 Times in 35 Posts
Rep Power: 3
bmj_23 will become famous soon enough
Re: Tables Requires for data upload via CSV

It has been a while since I looked at those actual tables involved, but as far as I remember, you can assign whatever sequence number you like to data that you upload via CSV. If the standard sequence that is being generated when you create records via the UI is 1,2,3 etc, I would use a sequence starting with 91, 92, 93 etc, for data you are uploading.

The basic issue as I understand it is that when you create a record via the UI, OTM is using an oracle sequence number to generate the corresponding sequence number - you are bypassing that logic when uploading via CSV. I am not aware of any downstream issues.

Anyone else with experience of this ?
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VPD on Custom Tables MURTHYKO Workflow and Agents 1 February 16th, 2009 13:36
OTM 5.5: Safe to purge OBJECT_LOCK, BN_SEQUENCE and S_EQUIPMENT tables? ianlo SysAdmin and Management 0 September 22nd, 2008 10:04
Issue with 'Upload an XML/CSV Transmission' sgshah Data Loading 0 February 18th, 2008 23:05
Document Upload Vinod Data Loading 2 November 14th, 2007 04:17
Data upload error engyeowkee Data Loading 2 July 2nd, 2007 09:50



All times are GMT. The time now is 23:26.
Copyright © 2006-2009, Open Book Solutions LLC. All rights reserved.


Inactive Reminders By Icora Web Design

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40