Export Data to Text File or Microsoft Access

This topic describes how to extract the contents of an SAP database table to a text file or Microsoft Access table without sending it to Microsoft Excel.

The option Export To Text File/MS Access Settings is available only for Data Extractor technology.

Refer to the video to understand how to export data to a text file in Process Runner.

To export data to a text file

1. Open the Data Extractor Process file whose data you want to export, select Home tab > Write to text file in the Settings section. The Export To Text File/MS Access Settings window is displayed.
   Screenshot: Export To Text File/MS Access Settings window

   
2. Select Ignore Excel and write output to Text file/MS Access table. The Text File and MS Access options are displayed.
3. To export data to a text file, perform the following steps on the Text File tab:
   1. In the Text Output File, select the folder where you want to save the text file.
   2. Select the following options depending upon how you want to overwrite the records in the text file:
      • Fixed Width: Indicates that the records are separated with an equal width regardless of field contents.
      • Delimited: Indicates that a special character will be used to delimit each field of the record. You can choose from the given set of delimiters or enter a custom character as a delimiter.
      • Overwrite output file if already exists: Indicates that the exported data will overwrite the contents of an existing output file.
      • Append to output file if already exists: Indicates that the exported data will append to an existing output file.
      • Launch text file in notepad when extraction is finished: Indicates that the exported data is launched using a notepad.
      • Write a separate field layout file in same location: Indicates that you can create a text file in the same location that contains field information such as map value, mapping type, and so on.
      • Write output fields in same sequence as Excel mapping sequence in mapper: Indicates that the fields in the output file are written in the same order in which they are mapped in Mapper. For example, if MATNR is mapped as column B and DATEUV is mapped as column A, then the DATEUV be written as first field and MATRN as the second field.
      • Add field names to same file as a header: Indicates that you can choose to have two field names or field descriptions in the first row of the output file.
   3. For text transformation, select Perform following text transformation and select the following options:
      • Bring negative sign in front: Indicates that the (–) sign for negative numbers is moved to the beginning of the figure by default.
      • Change decimal sign as per current regional settings: Indicates that you can change the decimal signs according to your current regional settings.
      • Format SAP date fields as per regional settings: Indicates that you can change the date fields in the SAP system according to your current regional settings.
      • Format SAP time fields to HH:MM:SS: Indicates that you can change the time fields in the SAP system according to HH:MM:SS.
      • Skip and write overflow data to error file: Indicates that the overflow data (data starting with an asterisk) is not written into a text file. Such records are exported into a separate error log file.
      • Remove extra space(s) for each field: Indicates that the field values in the exported data are displayed without the trailing space(s).
4. To export data to a Microsoft Access table, perform the following steps on the MS Access tab:
   1. In the MS Access database, select the database into which you want to write the data.
   2. If the database is password-protected, enter the password and select Verify.
   3. Under Table Options, enter the following:
      • MS Access Table Name: Indicates the table name to which you want to send the data.
      • Action: You can select any one from four actions to consider while writing the data into it.
        • Stop execution if table exists (default): Indicates that the data is written to a table if it does not exist; else, the run is stopped after the text file is generated.
        • Overwrite existing table: Indicates that the table is created if it does not exists; else, all records are erased and new records are written.
        • Create a new table (timestamp) if exists: Indicates that the table is created if it does not exist; else, a new table is created with name you that you have selected followed by timestamp in yyyymmdd_hhmmss format.
        • Append data to the table: Indicates that the table is created if it does not exist; else, records the records are added to the table. The structure of the existing table must match with the new data columns to be written.
   4. Under Import Text Options, select the following:
      • Use Schema Method: Indicates that the data is imported without any import specifications. It is required that you have performed text transformation.
      • Use Transfer Text Method: Indicates that the data is imported using the import specifications selected by the user. It is required that you have performed text transformation.
5. Select OK. The changes are saved.

While exporting data from a .txt file into a Microsoft Access table, ensure the following:

• The space character must not be used as delimiter.
• Decimal character, date separator, time separator must not be used as custom delimiters.
• Maximum output text file name length must not exceed 63 characters.

Also, note the following:

• You can extract large data with this option as a text output, which is not constrained by Excel row limits.
• Process Runner writes to Excel quickly in Turbo mode (default). However, writing  to text is about 600% faster as compared to writing to an Excel. In our benchmark, we were able to extract half million records to text file in 4 minutes that took 25 minutes to write to Excel 2007. (SAP extract time was about only 1:20 minute in both the cases. SE16 attempts timed out with short dump).
• If you use the Delimited option, total output length is slightly reduced. (1 character X number of fields selected).