i have a spreadsheet that contains monthly labels (cols) "Oct-07" and data (rows) "VA"  1,234 counts.

i'm trying to find a way to automate this - moving the monthly labels over to next column and inputing the data counts down the new column, but using the spreadsheet itself doesn't seem to work. i have just the basic tools from Pervasive: Map Desinger, Extract Schema, etc., but not sure what to use.

i used some tools successfully pulling out line styles from a text file and entering into a Access db. 

You may want consider using the Type Libraries functionality (see Map Designer Help) to call an ActiveX object to essentially perform an Excel macro operation, inserting a column (presumably B) and shifting the previous months data to the right.

For each of your source records, you could then do an AfterEveryRecord/Execute action to set the content of each desired Excel field (B2, B3....B1235).  Something like:

xlApp.Application.Cells(Sources(0).RecordCount,2).Value = Sources(0).Fields(1)

You probably wouldn't want ot actually connect to your Excel file as the Target, as it will replace the file (you can't append to Excel under normal Target connections) - consider using a NULL target type, or a dummy txt or excel file for a temporary target which would be overwritten with each run of the map.

The Type Libraries syntax is similar to, but not quite exactly like that which is generated by an Excel macro...but, if you record a macro first, you can get a feel for what the code should look like.

I don't do much with the Integration products any more, and only periodically look at the forum, but I'll see if I can come up with a more suitable working example if needed.

 Hope this helps!

Type Library

A file (or component within another file) that contains Automation standard descriptions of exposed objects, properties and methods. The Type Libraries listing can be accessed in the RIFL Script Editor in Map Designer. See Also: RIFL Script Editor

Thanks,  i found this in the help screens but my version 8.10 doesn't show me these.  just yesteday a co-worker showed me his type libs but he has version 8.14.

we'll look into what you said as soon as i get all the parts. 

I have something that may work...I've also been able to incorporate other, post transformation XLS formatting (bold headers, calcualted totals using EXCEL functions, freeze panes, auto-filter) etc using similar approach.  I've been able to get this to work in the old DJ 7.5.5 as well as Cosmos 8.10

Source:  Your desired Source file

Target: Null (or, any text or excel file if you need to see something)

Global Variables:

arrMyValues(Variant, LBound 0, UBound 9999)

Source/R1 - AfterEveryRecord(Execute)

arrMyValues(Sources(0).RecordNumber) = Sources(0).Fields(1) 'assuming that your desired data is found in field 1 of each source record

Source/General Event Handlers - OnEOF(Execute)

'Define the Excel Application Object
Dim xlApp As Object
 
'Create the Excel Object
Set xlApp = CreateObject("Excel.Application")    
 
objTargetFile = "c:\temp\example.xls" 'your desired file here
 
'Open the desired Excel File
xlApp.Workbooks.Open objTargetFile
                                           
'Show Excel spreadsheet
xlApp.visible = TRUE   
For i = 0 To UBound(arrMyValues)
If i = 0 Then
  'Insert new column in Column B, shifts data to right xlApp.Application.Worksheets(1).Columns("B:B").Select
    xlApp.Selection.Insert
  'Insert new column header label    
  xlApp.Application.Worksheets(1).Range("B1").Select
    xlApp.Application.Cells(1,2).Value = "YOUR LABEL HERE" 'or reference variable containing your desired month/year header value
 Else                     
  'Insert content of array element
  xlApp.Application.Cells(1 + i, 2).Value = arrMyValues(i)
 End If           
    'Sleep(1000) 'For debug purposes, so that you can watch progress
Next i

'Suppress warning message that file already exists
xlApp.Application.DisplayAlerts = FALSE

'Save file with formatted changes
xlApp.Application.ActiveWorkbook.Save

'Re-enable warning messages (if applicable)
'xlApp.Application.DisplayAlerts = TRUE

'Quit the Excel Application
xlApp.Quit

'Destroy xlApp object
Set xlApp = Nothing