Website Upgrade Incoming - we're working on a new look (and speed!) standby while we deliver the project

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Importing Ativity Descriptions - Activity Codes

11 replies [Last post]
Wed, 2007-09-12 02:42
Jonathan Ward
User offline. Last seen 12 years 47 weeks ago. Offline
Joined: 23 Jan 2007
Posts: 114
Groups: Petro-Chem / Oil & Gas
Good morning all,
i wonder if anyone could shine some light on this for me,, i have approx 3k activities that i need to import into my P3 Schedule,, the import i run will not have an issue adding the new, ’unlisted’ activity Values into the Activity Codes,, my question is,, is there a method of importing the Values and Activity Descriptions into P3,, the Values will import into my activity codes but i could really do without writing 3k Activity Descriptions.
many thanks
Top

Replies

Thu, 2007-09-20 03:54
#1
James Barnes
User offline. Last seen 1 year 35 weeks ago. Offline
Joined: 6 Sep 2007
Posts: 243
Groups: Petro-Chem / Oil & Gas
John

I think it’s the manipulation that makes Excel think "gosh, that should be a date" and change the formatting. Probably this is inconsistent between MS office releases aswell.

Anyhoo, each to their own. I was terrified of access to start with but I’m using it more and more (just as it gets obsolete, we move to P3e and I’ll have to learn SQL. Bugger.)
Top
Thu, 2007-09-20 03:48
#2
John Raper
User offline. Last seen 9 years 5 weeks ago. Offline
Joined: 17 May 2004
Posts: 44
Groups: None
I’m not into Access, but I’ve never had a problem with Excel reformatting DBF files.
Then again I’ve never tried it with 20k activities!

(I thought the dates came in as text?)
Top
Thu, 2007-09-20 03:27
#3
James Barnes
User offline. Last seen 1 year 35 weeks ago. Offline
Joined: 6 Sep 2007
Posts: 243
Groups: Petro-Chem / Oil & Gas
John

I found that opening the data in excel and saving off as DBF, often lines of data dissappeared and I couldn’t work out why. I know, though, that excel will often add formatting based upon an implied need (for example entering a date will cause Excel to interpret the cell number format that you want and change it to suit) so I assume that it is this. This is why I started using access to effectively clean this formatting out and ensure the consistency of the import.

It works for me and I typically handle >20k activities on a daily basis with it.

OTOH, if you’re good enough at Access, then better still would be to do the manipulations you need directly in the tables using update queries etc. and on the third hand... I am trying to get ODBC link working to remove the need to use export at all, but I have some sort of login error at the moment that I can’t clear.

/edit; oh, and I played with the Excel utilities files on the P3 CD. Couldn’t work them out and there’s too much hidden coding in there that I don’t understand (my fault, not the developers) to trust my plan to it
Top
Thu, 2007-09-20 03:11
#4
John Raper
User offline. Last seen 9 years 5 weeks ago. Offline
Joined: 17 May 2004
Posts: 44
Groups: None
OOPS. Just read the question again (got carried away with James’ reply)

Yes, there are Excel utilities on the install CD that will help manage the data dictionary. Not the easiest to use mind. And not much in the way of a help file either.
Top
Thu, 2007-09-20 03:08
#5
John Raper
User offline. Last seen 9 years 5 weeks ago. Offline
Joined: 17 May 2004
Posts: 44
Groups: None
James

By using Access you are adding to the complexity. I agree with you regarding exporting existing data first to create the format. However:

With 3000 activities you MUST use .DBF format. .WK1 only works with 2400 or less (can’t remember the exact number).

OPEN THE .DBF FILE IN EXCEL. Delete all the data, copy and paste your new data and save the file for import. DON’T CHANGE ANY FORMATTING.

Import to P3, job done.
Top
Wed, 2007-09-12 06:40
#6
James Barnes
User offline. Last seen 1 year 35 weeks ago. Offline
Joined: 6 Sep 2007
Posts: 243
Groups: Petro-Chem / Oil & Gas
you’re welcome
Top
Wed, 2007-09-12 06:17
#7
Jonathan Ward
User offline. Last seen 12 years 47 weeks ago. Offline
Joined: 23 Jan 2007
Posts: 114
Groups: Petro-Chem / Oil & Gas
James,,
that is wicked,, thanks alot,, currrently in the throws of the import having merged the data tables in Access
once again,
thnka you very much for your help
cheers
jon
Top
Wed, 2007-09-12 03:47
#8
James Barnes
User offline. Last seen 1 year 35 weeks ago. Offline
Joined: 6 Sep 2007
Posts: 243
Groups: Petro-Chem / Oil & Gas
what I do;

run an export from any P3 plan (doesn’t matter which one) to DBF with the fields in it that you want to import. One column must obviously be the ACT ID as this is the indexing field and is how P3 knows where to put the data when you re-import. Import this DBF table into Access, which will save the field formatting for you. In Access, delete the records from your example plan, as you won’t be needing them. Now you have a correctly formatted blank table in Access with the correct headers.

Next, open the same DBF export file in Excel and enter your data. Excel has myriad ways of importing/inserting, data which I’ll assume you are familiar with. Once you have your data in the correct columns, save it as XLS (you can try to save as DBF, but I find that data often disappears and I’m not clever / bothered enough to work out why)

Now for the clever bit...

In Access, use the import table wizard to bring in the xls file and dump the data into the DBF table you imported earlier. This will clean the excel data of any incompatible formatting. Sometimes you will get errors, for example if you exceed 48 chars in the act description (P3 constraint). It may take a couple of tries, but soon enough you’ll have all the data in one table in Access.

If you have reall problems with this (if you’ve used formulae to manipulate data in your excel file, this can create some deep formatting nonsense that will be tough to clear), you can also export the excel file to a CSV file then reopen it in excel and save again, or indeed import the CSV file direct to Access.

Once you are convinced that teh data is safely in your Access table, export this table to DBF. Open it and check it again if you want.

Back up your P3 plan <--- do not forget this. things can go wrong

Import the DBF file into P3 using the import dialog box. Each record will be imported and whatever fields will over write those fields in P3. Fields undefined in your table will not be affected (unless the P3 fairy is angry with you for some reason, then all bets are off)

There may well be more direct methods and know that there are several very clever things one can do with ODBC databases but I don’t know how to do them and I know, through a lot of experimentation, that this works quite well.

Hope this is useful

James
Top
Wed, 2007-09-12 03:36
#9
Steven Oliver
User offline. Last seen 12 years 1 week ago. Offline
Joined: 8 Nov 2002
Posts: 313
Groups: None
There is a utility on the P3 cd to do this.
Top
Wed, 2007-09-12 02:50
#10
Jonathan Ward
User offline. Last seen 12 years 47 weeks ago. Offline
Joined: 23 Jan 2007
Posts: 114
Groups: Petro-Chem / Oil & Gas
Hello,
thank for the reply, i normally import/export via excel or .dbf files
cheers
jon
Top
Wed, 2007-09-12 02:47
#11
Brad Lord
User offline. Last seen 10 years 25 weeks ago. Offline
Joined: 27 May 2003
Posts: 256
Groups: None
Hi Jonathan

What are you importing from?? re MSP excel etc,

regards

bradley
Top