IMPORTING DYNAMIC 3I REPORTS INTO EXCEL

Importing data into Microsoft Excel (MS-Excel) is very simply but first we will quickly explore the different report types that Dynamic3i offers. For more detailed explanations on executing reports in Dynamic3i please refer to the support help on this matter.

Execution of a report/listing

When a report is executed in Dynamic3i you will be presented with a choice of what device to print the report on. You will also be asked if it is to be run on-line, meaning ‘Now’ or in batch, meaning ‘Later’. If ‘Later’ is selected the application will be executed on a dedicated batch process server at a time and priority that you define. The ‘Destination Format’ can also be chosen. It is with this feature that we can format the output in preparation for import into a third party tool such as MS-Excel.

The ‘Destination’ can be any defined printer device and is predefined to the system by your database administrator. It can also be set to default to any particular device based upon your user login (see your administrator). This is useful if you always intend to print too a specific printer and do not want to select it from a long list of entries each time. Dynamic3i is pre-set to default to screen as to always render the report to your display.

The ‘Destination Format’ has a choice of 6 formats.

HTML will format all pages of the report in Hyper-Text Markup Language and then render it to your display.

Streaming HTML is useful for long reports. It will execute the report on the server and present to your display say the first 50 pages of a 500-page report. If more pages are required then by selecting next page etc. the server will process the next 50 pages and render them.

RTF is ‘Rich Text Format’. This will render the report in an image format.

XML will format the information into an industry std. Grouping format called Extensible Markup Language

PDF is an industry std. Format developed by Adobe systems. It stands for Portable Document Format. This format is wide spread and easily emailed and excepted across many platforms.

The ‘Delimited’ format will prepare the data in a format that most third party applications that import data will be able to decipher.

If the ‘Delimited’ format is chosen you will be able to select, from a list, the delimiter that you would like. This delimiter is what will separate each piece of data returned in the report.

Creating a Delimited Report

For importing data into a third party tool such as MS-Excel you would execute the report and chose the ‘Delimited’ format destination.

When executed the report will look similar to the following:

(All data is separated by the chosen ‘Delimiter’, in this case a comma ‘,’)

For importing into another application you would also want to have this delimited report saved to a disk file so that you could open it using the required application. This can be done by either selecting another Dynamic3i default ‘Destination’ known as ‘disk’ which will than ask you where on your network you would like to save the file or after running to screen simply save the file as a ‘text’, ‘.txt’ file.

Importing a File into MS-Excel

Open MS Excel and using the Toolbar select the ‘Data’, ‘Get external data’ tab.

This will invoke an 'Import Wizard’, which will walk you through some simple questions about the file that is being imported. If the information and selections are OK simple click on next to proceed to the next step of the import.

Upon completion of the wizard you will be asked to return data to Microsoft Excel view or create an OLAP cube. You will also have the option to ‘Save Query’. This is useful if you plan to do the same or similar queries again as they can simply be edited.

In the ‘Delimiters’ section you must tell MS-Excel what the delimiter is in the file. In the example a comma ‘,’ was used. Simple click on comma and Excel will reset the data preview section into what the import will break into columns.

At this point each column can be individually formatted upon import by telling MS-Excel what each column holds. You can also, simply bypass this and click on ‘Finish’ to import the file first. Full formatting of columns and rows can be done after from within MS-Excel. This formatting would be based upon your desired results and expertise with MS-Excel.

When ‘Finish’ is chosen from the wizard, a location in the spreadsheet will be asked for to place the data.

Simply accept/place the location and click ‘OK’. The data will be split into the desired columns and placed into the spread sheet cells for further formatting such as deletion of unwanted columns, rows or the addition of Sums, Formulas, Totals, Report Headings etc.

At this point it is very useful to have a Dynamic3i generated report so as to identify the data that each column holds. This will make the formatting of complicated data a lot simpler.

Working with imported data and negative values When working with imported numbers in Excel you will often be left with the problem of imported negative numbers that have the negative sign to the right of the number. These are referred to as mirror negatives. This means Excel will not see your numbers as negative numbers and to make matters worse, it won't even see them as valid numbers. e.g 200- instead of -200.

Let's say you have a great long list of numbers you have just imported and some of them are those so-called negative numbers (mirror negatives). Your job is to convert these to valid negatives that Excel will recognize. For the purpose of the exercise we will make the range A1:A100. In cell B1 enter this formula;

=SUBSTITUTE(IF(RIGHT(TRIM(A1))="-",RIGHT(TRIM(A1))&A1,A1),"-","",2)+0

Copy this down as many cells as needed and then copy them and select cell A1 and go to Edit>Paste Special-Values , directly over the top of the originals.

To give you an idea on how the formula works enter this part of it in any cell where A1 has the text 200-

=RIGHT(TRIM(A1),1)&A1

You will end up with; -200- The TRIM function simply ensure there are no space characters in the cell. As we end up with -200- we next need to remove the second occurrence of the negative sign. This is what; the SUBSTITUTE function is doing. We have told it to substitute the second occurrence of "-" with "" (empty text). Now, the result returned is actually text (as that is what the SUBSTITUTE function returns) so we simply use +0 and Excel will convert it to a valid number.

If doing this is a common task you should consider a macro to make the job easier. Below is one that will do the task at hand in no time. It has been optimized for speed.

Sub ConvertMirrorNegatives()
Dim rCell As Range
Dim rRange As Range
Dim lCount As Long
Dim lLoop As Long

If Selection.Cells.Count = 1 Then
MsgBox "Please select the range to convert", vbInformation
Exit Sub
End If

On Error Resume Next
Set rRange = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)

If rRange Is Nothing Then
   MsgBox "No mirror negatives found", vbInformation
   On Error GoTo 0
   Exit Sub
End If

Count = WorksheetFunction.CountIf(Selection, "*-")
Set rCell = Selection.Cells(1, 1)

     For lLoop = 1 To lCount
        Set rCell = rRange.Find(What:="*-", After:=rCell, _
                    LookIn:=xlValues, LookAt:=xlPart, _
                    SearchOrder:=xlByRows, SearchDirection:= _
                    xlNext, MatchCase:=False)

        rCell.Replace What:="-", Replacement:=""
        rCell = rCell * -1
  Next lLoop

On Error GoTo 0
End Sub