Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

Related LIT software: litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe

Home Products SQL*XL Help Features Query

Buy now

Download now

SQL*XL online help

 

SQL*XL: Excel MS Project query

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:

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




See also:

SQL*XL ribbon in Excel