1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

'
' Convert GL Data from Ultipro to SAP
' ===================================
'
' Author:       Markus Diersbock <markus@swingnote.com>
'
' Input:        Save as "export.csv" in comma-delimited format (with header)			
'               Clock,Codes,PSID,New GL,Old GL,Reg-Hol+OT+DT+Other,Date
' 
' Output:       Formatted data with increasing numeric filename 
'
' Revisions:    2012/01/04 Created
'               2012/02/01 Augmented error routine on invalid column check
'

Dim this_line, ary_this_line
Dim i 
Dim fso_in, file_in
Dim fso_out, file_out
dim wsh

' Consts
Const TXT_IN = "export.csv"
Const NUM_COLS = 6
Const TITLE_VER = "Ultipro 2 SAP v1.31"

' output file
txt_out = (DateDiff("s", "1/1/1970", Now())) & ".txt"

Set fso = CreateObject("Scripting.Filesystemobject")
Set wsh = Createobject("WScript.Shell")

' check if input file exists
If fso.FileExists(txt_in) Then
	' open input file, and create output
    Set file_in = fso.OpenTextFile(txt_in, 1)
	Set file_out = fso.OpenTextFile(txt_out, 2, True)
	
	' interate through input file skipping header
    Do While Not file_in.AtEndOfStream
        this_line = file_in.readline
        i = i + 1

        ' if not header, process
        If i > 1 Then
            ary_this_line = Split(this_line, ",")

            ' validate array size
            If UBound(ary_this_line) = num_cols Then
                ' format string
                file_out.WriteLine ary_this_line(0) _
                    & Space(2) _
                    & get_fixed_string(ary_this_line(1),4) _
                    & ary_this_line(2) _
                    & ary_this_line(3) _
                    & Space(10) _
                    & ary_this_line(4) _
                    & get_fixed_string(ary_this_line(5), 21) _
                    & ary_this_line(6)
            Else		
                MsgBox "Line " & i & " in " & txt_in & " has invalid number of columns" & chr(13) & chr(13) & "Click to Exit", 48, title_ver
                ' delete output file
                file_out.close
                fso.deletefile(txt_out)
                ' quit script
                WScript.Quit
            End If
        End If
    Loop
    
    ' close files and rename input file
    file_in.close
    file_out.close
    fso.MoveFile txt_in, "export_" & txt_out

    ' completed
    MsgBox "Done!", 64, title_ver

    ' open file to view
    wsh.Run txt_out
Else
    MsgBox "File " & txt_in & " does not exist", 48, title_ver
End If

Function get_fixed_string(str, length)
    ' pad and parse
    get_fixed_string = Left(str & Space(length), length)
End Function