Hi,
If in the infopackage you click on a File Type ASCII-file (CR delimiter) radiobutton and then F1, you’ll see the help:
“With an ASCII file as a data source, the data has to be available as a string in the format of the transfer structure. There are no data separators, empty characters are not ignored.”
This means that:
- you shouldn’t use any delimiters among the record fields
- all fields should have the length the system expects (length of the appropriate infoobject in transfer structure)
- each record should be separated from the next by carriage return CR.
Since I think the program that converts file from Excel format into CR-delimited would be very useful, I created such program in VBA.
So, open your Excel file (if you have csv-file you can open it using Excel).
The program assumes that the first row of the file contain field names. Data itself begin from the 2nd row. Insert as the 1st and 2nd rows the new, blank rows.
Into A1 cell insert number of the last row with data, into B1 – number of the last column. Certainly, it may be determined in the program, but I have not done it – if someone wants it, s/he can try it.
Into fields of the 2nd row insert expected length of the field.
Make sure that “Visual Bacis” toolbar is seen. (If not – tick in menu path: View/Toolbars/Visual Basic).
In this toolbar click on a ‘Design Node’ icon.
Drag from the toolbar and drop into worksheet a ‘Command button’ element.
Double click on the button. In the open window for “Private Sub CommandButton1_Click()” subroutine insert the following code:
Dim ws1 As Worksheet
Dim J As Long, I As Long, L As Long
Dim FileName As String, Out As String, formatOut As String
Dim LenCell As Long
Set ws1 = ThisWorkbook.Worksheets("SHEET1")
FileName = ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 3) & "txt"
Open FileName For Output As #1
For J = 4 To ws1.Cells(1, 1)
Out = ""
For I = 1 To ws1.Cells(1, 2)
formatOut = (ws1.Cells(J, I))
L = Len(formatOut)
LenCell = Val(ws1.Cells(2, I))
If L > LenCell Then
formatOut = Left(formatOut, LenCell)
Else
Do While L < LenCell
formatOut = formatOut & " "
L = L + 1
Loop
End If
Out = Out & formatOut
Next
Print #1, Out
Next
Close #1
Set ws1 = Nothing
MsgBox "File transformation finished!", vbOKOnly
Now return to Excel sheet, click again ‘Design Node’ icon switching into run mode.
When you press on this button now, the ASCII program will be created. It will have the same name as an input file (and in the same directory), but with TXT extention.
Remarks:
- The program works with the current worksheet named as “SHEET1”.
- Fields that are shorter than those expected by the system are padded by spaces from the right. It should not be the problem, since in transfer structure you can check symbols only flag.
- Longer fields are truncated to the length expected.
Best regards,
Eugene
Message was edited by: Eugene Khusainov
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.