How to create ASCII text file for upload to SAP BW

[Former Member]

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:

• 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.
Private Sub CommandButton2_Click()Dim sFileIn As String, sFileOut As String, sRow As StringDim ws As Worksheet, sDelim As StringDim iLen() As Integer, sIn() As String, sOut As String, sChar As StringDim iNum As Integer, iIgnore As IntegerDim J As Integer, I As Integer, iFirstPos As Integer, iLastPos As IntegerDim sBlankssBlanks = array(" ", "  ", "   ", "    ", "     ", "      ", "       ", "        ", "         ", "          ")Set ws = ThisWorkbook.Worksheets("SHEET1")iNum = ws.Cells(1, 2)sFileIn = ws.Cells(2, 2)sFileOut = ws.Cells(3, 2)sDelim = ws.Cells(4, 2)iIgnore = ws.Cells(5, 2)' If delimiter is blank then it equals to TABIf sDelim = "" Then    sDelim = Chr(9)End IfReDim iLen(iNum), sIn(iNum)For J = 1 To iNum    iLen(J) = ws.Cells(6, J)Next JOn Error Resume NextErr.ClearOpen sFileIn For Input As #1If Err.Number <> 0 Then    Msg = "Error # " & Str(Err.Number) & " was generated by " _            & Err.Source & Chr(13) & Err.Description    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext    EndEnd IfOpen sFileOut For Binary Access Write As #2If Err.Number <> 0 Then    Msg = "Error # " & Str(Err.Number) & " was generated by " _            & Err.Source & Chr(13) & Err.Description    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext    EndEnd IfOn Error GoTo 0'Skip rows to ignoreFor J = 1 To iIgnore    Line Input #1, sRowNext J'Read a rowDo While Not EOF(1)    Line Input #1, sRow'Clear Array    For J = 1 To iNum        sIn(J) = ""    Next J'Parse the row    I = 1    iFirstPos = 1    sChar = ""    For J = 1 To Len(sRow)        sChar = Mid(sRow, J, 1)        If sChar = sDelim Then            iLastPos = J - 1            sIn(I) = Mid(sRow, iFirstPos, iLastPos - iFirstPos + 1)            iFirstPos = J + 1            I = I + 1        End If    Next J    sIn(I) = Mid(sRow, iFirstPos)'Create Output row    sOut = ""    For J = 1 To iNum        I = iLen(J) - Len(sIn(J))'Add missing spaces        Do While I > 10'Add spaces from the left            sIn(J) = sBlanks(9) & sIn(J)            I = I - 10        Loop        If I > 0 Then            sIn(J) = sBlanks(I - 1) & sIn(J)        Else'If a string is too long, leave just required number of symbols            sIn(J) = Left(sIn(J), iLen(J))        End If        sOut = sOut & sIn(J)    Next J    sOut = sOut & vbCrLf        Put #2, , sOutLoopClose #1Close #2MsgBox "Finished!", vbOKOnlyEnd Sub