Skip to Content
ASCII text files with fixed length of fields are beneficial from the performance point of view (comparing with CSV files).

From time to time we have a request in SDN asking about such files creation.

The last one was recently:

Re: loading  data from ASCII file

Here is a small VBA program that creates such ASCII file from CSV file.

I assume that a user can set up Excel security, create buttons in Excel and place code into subroutines. — if not — refer to any manual/book dedicated to Excel or look at my previous blog:
Long Texts in SAP BW: Displaying in BEx Analyzer. Introduction to Excel Workbooks Formatting. Part I.

My code takes some parameters from the first Excel sheet:

image

  • Number of fields in the file
  • Source File Name
  • Target File Name
  • Delimiter of fields in the source file
  • Number of rows in the source file to ignore
  • Fixed length of fields in the ASCII file – the same as field lengths in the transfer structure.

The code expects values of these parameters exactly as it is shown in the picture above (in the same cells).
Certainly, one can create one or two buttons with a code that will allow to browse to the source file and choose a target file. It’s up to you.

The program may work with long files (more than 65535 rows).

So, here is a code attached to the “Create ASCII file” button.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Dirk Herzog
    Hi Eugene,
    quite a useful tool. If I only had the time to convert it to ABAP. IMHO an ABAP program would have the big advantage to be able to store the data into the application server, something that Excel cannot (should not be able to) do.
    But it there really any reason to convert CSV to ASCII (apart from the typical well-meant but work increasing guidelines)? When I need to start another program to convert the file to ASCII I would guess that it takes longer than the time you save by uploading ASCII instead of CSV.

    Best regards
    Dirk

    (0) 
    1. Hi Dirk,

      The business reason is well known: better performance for a file with fixed fields length. Especially if you can place the file on the server.

      The practical reason is the following:
      Saving as txt file in, for example, Excel, WON’T HELP!
      ASCII text files for upload to BW are special. They are not those that contain fields separated by carriage return or by another delimiter. They are just opposite – all field in one row are to be contained in one record without any (even CR or LF) delimiters.

      So, each record in the file to be uploaded should contain records with the structure like this:

      11111122222222222333333333333344444
      Here, the digits show different fields in the record.

      The main problem here that the length of the fields in the upload file may differ from that that is expected by BW. Hence, we need to pad each field with spaces. Since you cannot know beforehand to which field in BW the incoming from flat file field may go, the padding depends on particular transfer structure.

      Best regards,
      Eugene

      (0) 
      1. Dirk Herzog
        I know the difference. My point is that the performance advantage disappears if you run the translation to turn it into an ASCII file because the time for translation is probably as high as the additional time the BW needs.

        Best regards
        Dirk

        (0) 
        1. Yes, it might be.

          I see the following moments when creation of ASCII file is, anyway, preferrable:

          1. In case of huge files, when upload may fail or be too slow.

          2. In case of files with number of rows greater than 65535 rows.

          Best regards,
          Eugene

          (0) 

Leave a Reply