XL2SAS 1.3 Help

This help document is best viewed with Microsoft Internet Explorer.

XL2SAS was created in order to convert Excel input files from QBI's Cleveland Chemical Screening unit to an Excel format closer to SAS, for QBI's Israeli Statistics unit. It can process selected files, or it can process all the sub-folders of a chosen directory.

XL2SAS also remembers keys: You can define which columns in the input file will be key columns. Key columns are columns who's values are checked during processing. If any key should re-appear in the same document, an error is generated. If a key repeats itself in another document, the data will be stored in the same output row as the previous key, without overwriting the data (further explained in this document in the "Replication Number" section).

    Input Format specifications

  • Sample File - this a link to the sample file attached. This represents the standard format of input files to be given.
  • Filename Format - Filename format should be <Plate Num>-<Day Num>-<Replication Num>.xls . Example: 001-1-1.xls
  • "Data" Sheet - In every file there should be a sheet named "Data". Only this sheet will be processed.

  • Starting Row - The way I find the starting row, is by going to the last (bottom-right) cell of the sheet; then I scan back on the FIRST column of that last row, until I reach a non-numeric value; That's the column header as far as I'm concerned. Then I scan the columns to the right, on that first-row, until I find an empty value; These are all the column headers I present in the "Available Columns" list (see "Available Columns list" section).

  • Date Format - I extract the date by taking all character up to the first space from cell "A1". It is advised that no changes be made to the date format or location.
  • Replication Number - Replication Number, or Repeat number, is the times this experiment was repeated. The program extracts the replication number from the filename. Any changes to filename format can render file processing inoperative (see Filename Format).

    The replication number is used to place data in data columns called typically "DataA", "DataB", etc… If replication number was 1, the data in the output file will be placed in column "DataA". If replication number was 2 - "DataB", and so on.


    If the exact same key was found, in a different data sheet (which means data will be placed in the same output row as other input rows with same key), and the same replication number is found - an error will be generated in order that data will not be overwritten, and program execution will stop.
  • Processing Sub-Folders - In the option to process the sub-folders of the selected folder, you can organize a directory structure similar to the one shown below;

    • Selecting (double-clicking) the "Mblue" folder and running the process will process the 2 sub-folders of "Mblue", the "Mblue1" and the "Mblue2" folders. It will NOT process any files in the "Mblue" directory itself, only files in the sub-folders of "Mblue".
    • It does not process folders recursively (i.e. any sub-sub-folders).
    • Please do not mix different kinds of experiments and file structures in one process, or in same folder.
      Column headings should be the same in each file.

    The Program


  • Menu Items - The "File" menu has the following items:
    • Run - same as pressing run on the main dialog box.
    • Options - brings up the options dialog box.
    • Delete Reg Settings - Is good for uninstall of the program. It doesn't do anything harmful, so don't be afraid if it doesn't succeed in deleting all settings, it will only attemt to delete settings that belong to this specific program.
  • Filename Pattern option - on the main dialog box, you have the option to change the type of files it presents in the "FileChooser" box above it. If you want to see all files, enter "*". If only Excel files - "*.xls".
  • Switch between Folders mode and Files mode - Pressing the "Process sub folders of this folder" option will enable you to process all the sub-folders of the directory shown below that option. (This is called "Folders Mode"). Pressing the "Process the selected files" option will enable you to process only selected files from the "FileChooser" above (this is called working in "Files Mode").
  • Options -
    • General Tab:
      • You can choose whether to output the results to a specific filename and location ("c:\temp\output.xls") each time a process is run ("Output to this file");
      Or
      You can output the results to a changing location, according to where you process the file (in the option "Always output to current…"). In the last case, you only have to supply a filename ("my_output.xls"), not a full path ("c:\outputs\…"). In the example of the directory structure above, processing the "Mblue" directory will output the file to the "Mblue" directory. If you change the directory being processed, file location will change accordingly.

      All other options on the "General" tab are rather self-explanatory.
    • Structure Tab:
      • "Available Columns" list - This is a list of columns available for use in the current process. You can use each one of these columns for Key columns, and output columns. The list is comprised of columns taken from the sample input file, (click the "Launch Sample File" to view it), and custom added columns for XL2SAS.
      • "Columns for the output file" list - This is a list of columns that will be in the output file after process.
      • "Columns that comprise the key" list - Explained in the introduction.
      • "Data Column" - Is the column from which you take data for the "DataA", "DataB" columns (usually "OD" or "Delta OD" columns).
Press the "OK" button to save the seetings and close the dialog box. Closing the window with the top-left "x" will not change anything, and all changes will lost (except a few unimportant ones, like - if to launch files or not, but not anything to do with file-structure and format).

    Tips

  • When error messages are given, you can copy most, if not all filenames supplied in the error message by simply highlighting the text and copying it.
  • Double-click on any filename from the "FileChooser" in order to open that file.
  • Double-Click on names of folders in order to select them, or view their contents. One Click is not enough.
  • If there is a need to open an Excel file after an error has occurred, it is best to open it only after clicking the "OK" button on the error messages. This is due to a faulty error handling scheme done by the developer (me), in which the resources (including the invisible Excel in the background) are only freed after the message was given. Apart from that, if any trouble is given while trying to open Excel files, even after closing the error messages, then it might be due to an unresolved bug. In this case, make sure there are no open Excel processes, and free any remaining processes in the Task-Manager's "Processes" tab (Ctrl+Alt+Del). In any case, I tried to do my best to resolve these issues and prevent such things from happening.


  • When in "Folders" mode, the program will only process sub-folders of the selected directory, and not the files in the selected directory (see "Processing Sub-Folders"). In the example below, any files in "Mblue" will not be processed, but all files in sub-folders "Mblue1" and "Mblue2" will be processed.

  • It is necessary to choose a file (when in "Files" mode) before opening the options dialog box. This file will be used as a sample file for determining the structure of files to process, and their header columns. In "Folders" mode, point to the parent directory (of the sub-folders you want to process) and then open the options dialog box. In this case, the program will use the first file in the first sub-folder as a sample file.

    More Info

    This software was created by Michael Nissim for Q.B.I. . All credits should go to my Lord and Saviour, Yeshua the Messiah. Any bug reports and addition requests - should be sent to Michael Nissim .