0 / 0

Examples of writing data to Microsoft Excel files (DataStage)

Last updated: Mar 12, 2025
Examples of writing data to Microsoft Excel files (DataStage)

You can build sample jobs that write data to Microsoft Excel files.

To get the files for the examples, extract the IS_install\Clients\Samples\Connectors\UnstructuredData_Samples.zip file.

Example 1: Writing data to existing Microsoft Excel files

This example uses a text file Employee.txt as source data. The source file contains information of employees in CSV format. You write this information to a Microsoft Excel file ExcelModifySample1.xlsx. ExcelModifySample1.xlsx has a sheet with a name Employee that contains, EMP NO, FIRST NAME, MIDINIT, LAST NAME, HIRE DATE, JOB, SEX, SALARY, BONUS, and TOTAL PAYcolumns. In this job, you write to these columns except TOTAL PAY. TOTAL PAY column has a formula to calculate salary and bonus.
To write data to an existing file:
  1. Create a job that includes one Excel stage and one Sequential File stage.
  2. Double-click the Sequential File stage. On the Properties page, specify the file path to the Employee.txt file.
  3. On the Columns page, define the columns as shown in the following table. Then, click OK.
    Column name Key SQL type Extended Length Scale Nullable Description
    EMP_NO   Integer       Yes  
    FIRST_NAME   VarChar       Yes  
    MIDINIT   VarChar       Yes  
    LAST_NAME   VarChar       Yes  
    HIRE_DATE   Date       Yes  
    JOB   VarChar       Yes  
    SEX   VarChar       Yes  
    SALARY   Integer       Yes  
    BONUS   Integer       Yes  
  4. Double-click the Excel stage. From the Stage tab, select Excel from the Document type list.
  5. From the Write mode list, select Create a file, and click Configure.
  6. In the Configuration window, specify the path where you want create the output file, followed by the file prefix ExcelModifySample1.
  7. From the Column header field, select First row.
  8. In the Start writing from this row, specify 2.
  9. From the Sheet list, select Employee. Ensure that Microsoft Excel columns A to I are selected.
  10. Click Import.
  11. In the Map panel, define the mapping between Microsoft Excel column and DataStage®column. Then, click OK.
  12. Save the job. Now, you can compile and run the job.

The output data in a Microsoft Excel sheet should match the information that is in the source text file.

Example 2: Writing data to multiple spreadsheets of a Microsoft Excel file

This example uses 7 text files, DEPT_A00.txt, DEPT_B01.txt, DEPT_C01.txt, DEPT_D11.txt, DEPT_D21.txt, DEPT_E11.txt, and DEPT_E21.txt as source data. Each source file contains information of employees of the corresponding department in CSV format. You write information from each source file to each spreadsheet. Created Microsoft Excel file has multiple spreadsheets.
To write data to multiple files:
  1. Create a job that includes one Excel stage and seven Sequential File stages. You can position them in a vertical line.
  2. Name the links so that they match the corresponding department name (A00, B01, C01, D11, D21, E11, E21).
  3. Double-click the Excel stage. From the Stage tab, select Excel from the Document type list.
  4. From the Write mode list, select Create a file.
  5. In the Configuration window, specify the full file path where you want to create the output file, followed by the file name OutputOfSample5.xls.
    1. From the File update mode, select Overwrite.
    2. From the Write method, select Specific file. Ensure you select Set for all links option.
    3. From the Column header field, select Column names.
    4. From the Auto size columns field on the Property tab, select Yes.
    5. In Sheet order pane, order links to A00, B01, C01, D11, D21, E11, E21 by using Up and Down buttons.
    6. In the Sheet order pane, specify sheet names so that they match the corresponding link names.
  6. Click OK.
  7. Open the Sequential File stage:
    1. On the Properties page, specify the file path to the DEPT_A00.txt file.
    2. On the Columns page, define the columns as shown in the following figure.
      This figure displays the defined columns.
    3. Repeat six more times for the remaining source files, DEPT_B01.txt, DEPT_C01.txt, DEPT_D11.txt, DEPT_D21.txt, DEPT_E11.txt, and DEPT_E21.txt. All Sequential File stages have the same column definition on their output link.
  8. Save the job. Now, you can compile and run the job.

After the job runs, open the OutputOfExample5.xlsx file to view the result.

The output data in a Microsoft Excel sheet should match the information that is in the source text file. For example, the Microsoft Excel file Sheet A00 should match the source data in DEPT_A00.txt file, and the Microsoft Excel file Sheet B01 should match the source data in DEPT_B01.txt file.

Example 3: Writing data to multiple Microsoft Excel files

This example uses a text file Employee.txt as source data. The source file contains information of 42 employees in CSV format. You write this information to multiple Microsoft Excel files divided by specified maximum number of rows in a sheet option.

To write data to multiple files:
  1. Create a job that includes one Sequential File stage and one Excel stage.
  2. Double-click the Excel stage. On the Stage tab, select Excel from the Document type list.
  3. From the Write mode list, select Create a file.
  4. In the Configuration window, specify the path where you want create the output file, followed by the file prefix OutputOfSample6_. When files are generated, three digits sequential number and extension .xlsx are added to this prefix. For example, 001.xlsx, 002.xlsx.
  5. From the File update mode, select Overwrite.
  6. From the Write method, select Generate multiple files.
  7. From the Column header field, select Column names.
  8. From the Auto size columns field on the Property tab, select Yes.
  9. In Maximum number of rows in a sheet, specify 10.
  10. In the Sheet order pane, specify Employee as Sheet name.
  11. Open the Sequential File stage. On the Properties page, specify the file path to the Employee.txt file.
  12. On the Columns page, define the columns as shown in the following figure.
    This figure displays the defined columns.
  13. Save the job. Now, you can compile and run the job.

After the job runs, open the output file to view the result.

The output data in a Microsoft Excel sheet should match the information that is in the source text file. Since the specified maximum number of rows in a sheet is 10 and it includes 1 row for column header, each output file includes the maximum 9 records from the input file. The input file has 42 records and hence 5 files are generated. The OutputOfExample6_001.xlsx, OutputOfExample6_002.xlsx, OutputOfExample6_003.xlsx, OutputOfExample6_004.xlsx, OutputOfExample6_005.xlsx files are generated.