3 Replies Latest reply: Jun 6, 2014 11:22 AM by John Lifter RSS

    read custom to read .xls

      Is is possible to use the read custom operator to read a directory of .xls files?

      If so what would the field delimiter be?  Are there any examples of how to do this?

       

      Thanks Traci

        • Re: read custom to read .xls

          There us no reason to use Read Custom. The Read Excel operator should be used. Reading content from Excel is non-trivial, so you shouldn't think of trying to code it in the Read Custom.

          • Re: read custom to read .xls

            Sorry John,

              I forgot to mention I want to read an entire directory of .xls files and need to parse the payroll date from the filenames.  This is the case that I use Read Custom (for .csv) or Read Directory for other formats.

             

            Thanks Traci

              • Re: read custom to read .xls

                While you will be able to do the same sort of coding to get a listing of the files in a directory and extract the payroll date from the file name, you will not be able to actually process Excel files by writing Datascript code within the Read Custom operator.  This would require the ability to handle binary content and to perform the various type conversions.

                 

                I believe the only way you can process a collection of Excel files, extracting the payroll date from the file name and using that value someplace in the dataflow, would be to run the dataflow in a loop managed by an external script.  This is not complicated to do but it requires a purchased license for Expressor Standard.  The Desktop version of Expressor will only run dataflows from within Desktop and there is no way to kick off execution other than to open Desktop and click the run button.

                 

                If you can set up some sort of Visual Basic script that converts each of the Excel worksheets into a CSV file, then processing the collection of files could be performed as previously done.