SQL*XL: Excel MS Project query

Related LIT software:

		SQL*XL online help

SQL*XL: Excel MS Project query

SQL*XL
home

The data in MS Project file can be exported to Excel using SQL. Unfortunately MS Project does not support certain common SQL features This makes it a little bit more difficult to export informtation to Excel. These restrictions are the following.

Multi-table queries are not supported: You must use a separate query for each table you wish to access.
Counting the number of records is not possible. This means that SQL*XL cannot provide how many rows are returned by your select statement.
Joins are not supported. This means that you cannot select data from more than 1 table at a time.
The ANY, LIKE, and IS NOT operators are not supported.
The aggregate functions Sum, Avg, Min, Max, Count, and StDev are not supported.
Some of the MS Project tables have more than 255 columns - the maximum allowed in Excel. This means that you won't be able to always execute a "select * from table" query. You can either select only a few columns or restrict the number of rows to be less than 255 and transpose the recordset (see Paste Options dialog).

Table Names:
The tables that can be accessed in MS Project files are:

Project: ( definition , example queries )
Provides access to the project-level settings on the Project Information (Project menu), Options (Tools menu), and Properties (File menu) dialog boxes. For the fields of the project summary task, access the Tasks table using a value of 0 for the TaskID column.
Tasks: ( definition , example queries )
Resources: ( definition , example queries )
Work values for material resources are returned in the units defined in the interface, rather than minutes * 1000.
Assignments: ( definition , example queries )
Successors: ( definition )
This table is normally used in conjunction with the Tasks table to display detailed information on the successors of a task.
Predecessors: ( definition )
This table is normally used in conjunction with the Tasks table to display detailed information on the predecessors of a task.
TaskSplits: ( definition )
BaselineTaskSplits: ( definition )
Calendars: ( definition , example queries )
CustomOutlineCodeLookupTables: ( definition )
This table is used to get a list of lookup table values for custom outline code fields.

The columns and their data types for each table are described below.

List of available columns in the Project table:

Column Name	Data Type
Project	Text
ProjectAuthor	Text
ProjectCalendarName	Text
ProjectCategory	Text
ProjectCompany	Text
ProjectCreationDate	Date
ProjectCriticalSlackLimit	Number
ProjectCurrencyDigits	Number
ProjectCurrencyPosition	Number
ProjectCurrencySymbol	Text
ProjectCurrentDate	Date
ProjectDaysPerMonth	Number
ProjectDefaultFinishTime	Number
ProjectDefaultFixedCostAccrual	Boolean
ProjectDefaultOvertimeRate	Text
ProjectDefaultStandardRate	Text
ProjectDefaultStartTime	Number
ProjectDefaultTaskType	Number
ProjectDurationFormat	Number
ProjectEditableActualCosts	Boolean
ProjectExpandTimephased	Boolean
ProjectFinishDate	Date
ProjectFYStart	Date
ProjectHonorConstraints	Boolean
ProjectInsertedProjectsLikeSummary	Boolean
ProjectIsResourcePool	Boolean
ProjectKeywords	Text
ProjectLastSaved	Date
ProjectManager	Text
ProjectMinsPerDay	Number
ProjectMinsPerWeek	Number
ProjectMultipleCriticalPaths	Boolean
ProjectNewTasksEffortDriven	Boolean
ProjectNewTasksEstimated	Boolean
ProjectPoolAttachedTo	Text
ProjectRevision	Text
ProjectSavePreviewPicture	Boolean
ProjectScheduledFromStart	Boolean
ProjectShowEstimatedDurations	Boolean
ProjectSplitInProgressTasks	Boolean
ProjectSpreadActualCosts	Boolean
ProjectSpreadPercentComplete	Boolean
ProjectStartDate	Date
ProjectStatusDate	Date
ProjectSubject	Text
ProjectTaskUpdatesResource	Boolean
ProjectTitle	Text
ProjectWorkFormat	Number

Example queries:

	All Project information:
		select from Project;*

List of available columns in the Tasks table:

Column Name	Data Type
Project	Text
TaskUniqueID	Number
TaskActualCost	Number
TaskActualDuration	Number
TaskActualFinish	Date
TaskActualOvertimeCost	Number
TaskActualOvertimeWork	Number
TaskActualStartNumber	Number
TaskACWP	Number
TaskBaselineCost	Number
TaskBaselineDuration	Number
TaskBaselineDurationEstimated	Boolean
TaskBaselineFinish	Date
TaskBaselineStart	Date
TaskBaselineWork	Number
TaskBCWP	Number
TaskBCWS	Number
TaskCalendar	Text
TaskCompleteThrough	Date
TaskConfirmed	Boolean
TaskConstraintDate	Date
TaskConstraintType	Number
TaskContact	Text
TaskCost	Number
TaskCost1-10	Number
TaskCost1-10Indicator	Number
TaskCostVariance	Number
TaskCreated	Date
TaskCritical	Boolean
TaskCV	Number
TaskDate1-10	Date
TaskDate1-10Indicator	Number
TaskDeadline	Date
TaskDuration	Number
TaskDuration1-10	Number
TaskDuration1-10Estimated	Boolean
TaskDuration1-10Indicator	Number
TaskDurationVariance	Number
TaskEarlyFinish	Date
TaskEarlyStart	Date
TaskEffortDriven	Boolean
TaskEstimated	Boolean
TaskExternalTask	Boolean
TaskFinish	Date
TaskFinish1-10	Date
TaskFinish1-10Indicator	Number
TaskFinishSlack	Number
TaskFinishVariance	Number
TaskFixedCost	Number
TaskFixedCostAccrual	Number
TaskFlag1-20	Boolean
TaskFlag1-20Indicator	Number
TaskFreeSlack	Number
TaskHideBar	Boolean
TaskHyperlink	Text
TaskHyperlinkAddress	Text
TaskHyperlinkHref	Text
TaskHyperlinkSubAddress	Text
TaskID	Number
TaskIgnoreResourceCalendar	Boolean
TaskIsNull	Boolean
TaskLateFinish	Date
TaskLateStart	Date
TaskLevelAssignments	Boolean
TaskLevelingCanSplit	Boolean
TaskLevelingDelay	Number
TaskLinkedFields	Boolean
TaskMarked	Boolean
TaskMilestone	Boolean
TaskName	Text
TaskNotes	Text
TaskNumber1-20	Number
TaskNumber1-20Indicator	Number
TaskObjects	Number
TaskOutlineCode1-10	Text
TaskOutlineLevel	Number
TaskOutlineNumber	Text
TaskOverallocated	Boolean
TaskOvertimeCost	Number
TaskOvertimeWork	Number
TaskPercentComplete	Number
TaskPercentWorkComplete	Number
TaskPredecessors	Text
TaskPreleveledFinish	Date
TaskPreleveledStart	Date
TaskPriority	Number
TaskRecurring	Boolean
TaskRegularWork	Number
TaskRemainingCost	Number
TaskRemainingDuration	Number
TaskRemainingOvertimeCost	Number
TaskRemainingOvertimeWork	Number
TaskRemainingWork	Number
TaskResourceGroup	Text
TaskResourceInitials	Text
TaskResourceNames	Text
TaskResourcePhonetics	Text
TaskResponsePending	Boolean
TaskResume	Date
TaskRollup	Boolean
TaskStart	Date
TaskStart1-10	Date
TaskStart1-10Indicator	Number
TaskStartSlack	Number
TaskStartVariance	Number
TaskStop	Date
TaskSubprojectFile	Text
TaskSubprojectReadOnly	Boolean
TaskSuccessors	Text
TaskSummary	Boolean
TaskSummaryProgress	Number
TaskSV	Number
TaskTeamStatusPending	Boolean
TaskText1-30	Text
TaskText1-30Indicator	Number
TaskTotalSlack	Number
TaskType	Number
TaskUniqueIDPredecessors	Text
TaskUniqueIDSuccessors	Text
TaskUpdateNeeded	Boolean
TaskVAC	Number
TaskWBS	Text
TaskWBSPredecessors	Text
TaskWBSSuccessors	Text
TaskWork	Number
TaskWorkVariance	Number

Example queries:

Display overview of Tasks:
	select	TaskStart
	,	TaskFinish
	,	TaskName
	,	TaskBaselineStart
	,	TaskBaselineFinish
	,	TaskCompleteThrough
	,	TaskConfirmed
	,	TaskCreated
	,	TaskEarlyStart
	,	TaskEarlyFinish
	,	TaskLateStart
	,	TaskLateFinish
	,	TaskResourceInitials
	,	TaskResourceNames
	,	TaskSuccessors
	from	Tasks;

All Task information:
	select from Tasks;* Please note that the Tasks table contains more columns that there are columns in Excel (255). Use the Transpose option in the Resultset Options dialog or be more selective in the columns you wish to see

List of available columns in the Resources table:

Column Name	Data Type
Project	Text
ResourceUniqueID	Number
ResourceAccrueAt	Number
ResourceActualCost	Number
ResourceActualOvertimeCost	Number
ResourceActualOvertimeWork	Number
ResourceActualWork	Number
ResourceACWP	Number
ResourceAvailableFrom	Date
ResourceAvailableTo	Date
ResourceBaseCalendar	Text
ResourceBaselineCost	Number
ResourceBaselineWork	Number
ResourceBCWP	Number
ResourceBCWS	Number
ResourceCanLevel	Boolean
ResourceCode	Text
ResourceConfirmed	Boolean
ResourceCost	Number
ResourceCost1-10	Number
ResourceCost1-10Indicator	Number
ResourceCostPerUse	Number
ResourceCostVariance	Number
ResourceCV	Number
ResourceDate1-10	Date
ResourceDate1-10Indicator	Number
ResourceDuration1-10	Number
ResourceDuration1-10Indicator	Number
ResourceEmailAddress	Text
ResourceFinish	Date
ResourceFinish1-10	Date
ResourceFinish1-10Indicator	Number
ResourceFlag1-20	Boolean
ResourceFlag1-20Indicator	Number
ResourceGroup	Text
ResourceHyperlink	Text
ResourceHyperlinkAddress	Text
ResourceHyperlinkHref	Text
ResourceHyperlinkSubAddress	Text
ResourceID	Number
ResourceInitials	Text
ResourceIsNull	Boolean
ResourceLinkedFields	Boolean
ResourceMaterialLabel	Text
ResourceMaxUnits	Number
ResourceName	Text
ResourceNotes	Text
ResourceNTAccount	Text
ResourceNumber1-20	Number
ResourceNumber1-20Indicator	Number
ResourceObjects	Number
ResourceOutlineCode1-10	Text
ResourceOverallocated	Boolean
ResourceOvertimeCost	Number
ResourceOvertimeRate	Text
ResourceOvertimeWork	Number
ResourcePeakUnits	Number
ResourcePercentWorkComplete	Number
ResourcePhonetics	Text
ResourceRegularWork	Number
ResourceRemainingCost	Number
ResourceRemainingOvertimeCost	Number
ResourceRemainingOvertimeWork	Number
ResourceRemainingWork	Number
ResourceResponsePending	Boolean
ResourceStandardRate	Text
ResourceStart	Date
ResourceStart1-10	Date
ResourceStart1-10Indicator	Number
ResourceSV	Number
ResourceTeamStatusPending	Boolean
ResourceText1-30	Text
ResourceText1-30Indicator	Number
ResourceType	Number
ResourceUpdateNeeded	Boolean
ResourceVAC	Number
ResourceWork	Number
ResourceWorkgroup	Text
ResourceWorkVariance	Number

Example queries:

Display overview of the Resources:
	select	ResourceStart
	,	ResourceFinish
	,	ResourceInitials
	,	ResourceName
	from	Resources;

All Resource information:
	select from Resources;* Please note that the resource table contains more columns that there are columns in Excel (255). Use the Transpose option in the Resultset Options dialog or be more selective in the columns you wish to see

List of available columns in the Assignments table:

Column Name	Data Type
Project*	Text*
ResourceUniqueID*	Number*
TaskUniqueID*	Number*
AssignmentActualCost	Number
AssignmentActualFinish	Date
AssignmentActualOvertimeCost	Number
AssignmentActualOvertimeWork	Number
AssignmentActualStart	Date
AssignmentActualWork	Number
AssignmentACWP	Number
AssignmentBaselineCost	Number
AssignmentBaselineFinish	Date
AssignmentBaselineStart	Date
AssignmentBaselineWork	Number
AssignmentBCWP	Number
AssignmentBCWS	Number
AssignmentConfirmed	Boolean
AssignmentCost	Number
AssignmentCost1-10	Number
CostRateTable	Number
AssignmentCostVariance	Number
AssignmentCV	Number
AssignmentDate1-10	Date
AssignmentDelay	Number
AssignmentDuration1-10	Number
AssignmentFinish	Date
AssignmentFinish1-10	Date
AssignmentFinishVariance	Number
AssignmentFixedMaterial	Boolean
AssignmentFlag1-20	Boolean
AssignmentHasFixedRateUnits	Boolean
AssignmentHyperlink	Text
AssignmentHyperlinkAddress	Text
AssignmentHyperlinkHref	Text
AssignmentHyperlinkSubAddress	Text
AssignmentLevelingDelay	Number
AssignmentLinkedFields	Number
AssignmentNotes	Text
AssignmentNumber1-20	Number
AssignmentOverallocated	Boolean
AssignmentOvertimeCost	Number
AssignmentOvertimeWork	Number
AssignmentPeakUnits	Number
AssignmentPercentWorkComplete	Number
AssignmentRegularWork	Number
AssignmentRemainingCost	Number
AssignmentRemainingOvertimeCost	Number
AssignmentRemainingOvertimeWork	Number
AssignmentRemainingWork	Number
AssignmentResourceID	Number
AssignmentResourceName	Text
AssignmentResourceType	Number
AssignmentResponsePending	Boolean
AssignmentStart	Date
AssignmentStart1-10	Date
AssignmentStartVariance	Number
AssignmentSV	Number
AssignmentTaskID	Number
AssignmentTaskName	Text
AssignmentTaskSummaryName	Text
AssignmentTeamStatusPending	Boolean
AssignmentText1-30	Text
AssignmentUniqueID	Number
AssignmentUnits	Number
AssignmentUpdateNeeded	Boolean
AssignmentVAC	Number
AssignmentWork	Number
AssignmentWorkContour	Number
AssignmentWorkVariance	Number

Example queries:

Display overview of the Assignments:
	select	AssignmentStart
	,	AssignmentFinish
	,	AssignmentBaselineStart
	,	AssignmentBaselineFinish
	,	AssignmentMilestone
	,	AssignmentResourceName
	,	AssignmentTaskName
	,	AssignmentTaskSummaryName
	from	Assignments;

All Assignment information:
	select from Assignments;*

List of available columns in the Successors table:

Column Name	Data Type
Project	Text
TaskUniqueID	Number
SuccessorLag	Number
SuccessorPath	Text
SuccessorTaskUniqueID	Number
SuccessorType	Number

List of available columns in the Predecessors table:

Column Name	Data Type
Project	Text
TaskUniqueID	Number
PredecessorLag	Number
PredecessorPath	Text
PredecessorTaskUniqueID	Number
PredecessorType	Number

List of available columns in the TaskSplits table:

Column Name	Data Type
Project	Text
TaskUniqueID	Number
SplitFinish	Date
SplitStart	Date

List of available columns in the BaselineTaskSplits table:

Column Name	Data Type
Project	Text
TaskUniqueID	Number
BaselineField	Number
BaselineSplitFinish	Date
BaselineSplitStart	Date

List of available columns in the Calendars table:

Column Name	Data Type
CalendarUniqueID	Number
Project	Text
ResourceUniqueID	Number
CalendarBaseCalendarUniqueID	Number
CalendarIsBaseCalendar	Boolean
CalendarName	Text

Example queries:

	Display all defined calendar names:
		select CalendarName from Calendars;

	All Calendars information:
		select from Calendars;*

List of available columns in the CustomOutlineCodeLookupTables table:

Column Name	Data Type
OutlineCode	Number
Project	Text
OutlineCodeLookupDescription	Text
OutlineCodeLookupIndex	Number
OutlineCodeLookupLevel	Number
OutlineCodeLookupParent	Number
OutlineCodeLookupValue	Text

SQL*XL online help

SQL*XL: Excel MS Project query