Importing Ativity Descriptions - Activity Codes

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.)

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?)

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

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.

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.

you’re welcome

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

There is a utility on the P3 cd to do this.