[COLOR=#804040][b]option[/b][/color] [COLOR=#804040][b]explicit[/b][/color]
[COLOR=#0000ff]' show help[/color]
[COLOR=#804040][b]call[/b][/color] help
[COLOR=#0000ff]' get file path from Excel File Open Dialog[/color]
[COLOR=#804040][b]dim[/b][/color] csv_path
csv_path [COLOR=#804040][b]=[/b][/color] get_csvPath[COLOR=#804040][b]()[/b][/color]
[COLOR=#804040][b]dim[/b][/color] fso[COLOR=#804040][b],[/b][/color] csv_file
[COLOR=#804040][b]set[/b][/color] fso [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]CreateObject[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Scripting.Filesystemobject"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]const[/b][/color] ForReading [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]1[/color][COLOR=#804040][b],[/b][/color] ForWriting [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]2[/color][COLOR=#804040][b],[/b][/color] ForAppending [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]8[/color]
[COLOR=#804040][b]set[/b][/color] csv_file [COLOR=#804040][b]=[/b][/color] fso[COLOR=#804040][b].[/b][/color]OpenTextFile[COLOR=#804040][b]([/b][/color]csv_path[COLOR=#804040][b],[/b][/color] ForReading[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]dim[/b][/color] line_num[COLOR=#804040][b],[/b][/color] csv_line
line_num [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color]
[COLOR=#804040][b]do[/b][/color] [COLOR=#804040][b]until[/b][/color] csv_file[COLOR=#804040][b].[/b][/color]AtEndOfStream
line_num [COLOR=#804040][b]=[/b][/color] line_num [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]1[/color]
csv_line [COLOR=#804040][b]=[/b][/color] csv_file[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]ReadLine[/color][COLOR=#804040][b]()[/b][/color]
[COLOR=#804040][b]if[/b][/color] line_num [COLOR=#804040][b]>[/b][/color] [COLOR=#ff00ff]1[/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#804040][b]call[/b][/color] validate_csv_line[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] csv_line[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#804040][b]loop[/b][/color]
csv_file[COLOR=#804040][b].[/b][/color][COLOR=#804040][b]close[/b][/color]
[COLOR=#0000ff]' At end: close all, release objects from memory and quit [/color]
[COLOR=#804040][b]set[/b][/color] fso [COLOR=#804040][b]=[/b][/color] [COLOR=#804040][b]nothing[/b][/color]
[COLOR=#008080]msgbox[/color] [COLOR=#ff00ff]"End of the validation "[/color] [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]VbCrLf[/color][COLOR=#804040][b], _[/b][/color]
[COLOR=#ff00ff]vbExclamation[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Warning"[/color]
[COLOR=#0000ff]'************************** Subroutines/Functions *****************************[/color]
[COLOR=#804040][b]sub[/b][/color] help
[COLOR=#804040][b]dim[/b][/color] msg
msg [COLOR=#804040][b]= _[/b][/color]
[COLOR=#ff00ff]"This program validates specific CSV-file"[/color] [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]vbCr[/color]
[COLOR=#804040][b]call[/b][/color] [COLOR=#008080]msgbox[/color] [COLOR=#804040][b]([/b][/color]msg[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]vbInformation[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Info"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]sub[/b][/color]
[COLOR=#804040][b]function[/b][/color] get_csvPath[COLOR=#804040][b]()[/b][/color]
[COLOR=#804040][b]dim[/b][/color] filename[COLOR=#804040][b],[/b][/color] objExcel
[COLOR=#0000ff] ' Create an invisible version of Excel[/color]
[COLOR=#804040][b]set[/b][/color] objExcel [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]CreateObject[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Excel.Application"[/color][COLOR=#804040][b])[/b][/color]
filename [COLOR=#804040][b]=[/b][/color] objExcel[COLOR=#804040][b].[/b][/color]GetOpenFilename [COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"All CSV-Files (*.csv),*.csv"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#0000ff] ' At end: close all, release objects from memory and quit [/color]
objExcel[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]quit[/color]
[COLOR=#804040][b]set[/b][/color] objExcel [COLOR=#804040][b]=[/b][/color] [COLOR=#804040][b]nothing[/b][/color]
[COLOR=#804040][b]if[/b][/color] filename [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]False[/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#0000ff] 'wscript.echo "'Open File Dialog' was cancelled"[/color]
[COLOR=#008080]msgbox[/color] [COLOR=#ff00ff]"No CSV-file selected !"[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]vbCritical[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Error"[/color]
wscript[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]quit[/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#0000ff] ' return result[/color]
get_csvPath [COLOR=#804040][b]=[/b][/color] filename
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]function[/b][/color]
[COLOR=#804040][b]sub[/b][/color] validate_csv_line[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] input_string[COLOR=#804040][b])[/b][/color]
[COLOR=#0000ff] ' Validation check of the individual fields[/color]
[COLOR=#804040][b]dim[/b][/color] csv_array[COLOR=#804040][b], _[/b][/color]
pers_name[COLOR=#804040][b],[/b][/color] pers_id[COLOR=#804040][b],[/b][/color] contract_no[COLOR=#804040][b],[/b][/color] confirm[COLOR=#804040][b],[/b][/color] datum[COLOR=#804040][b], _[/b][/color]
bank_code[COLOR=#804040][b],[/b][/color] created_by
csv_array [COLOR=#804040][b]=[/b][/color] [COLOR=#a020f0]split[/color][COLOR=#804040][b]([/b][/color]input_string[COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]";"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#0000ff] ' person name[/color]
pers_name [COLOR=#804040][b]=[/b][/color] csv_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]0[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]if[/b][/color] pers_name [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color] [COLOR=#804040][b]or[/b][/color] [COLOR=#008080]isNumeric[/color][COLOR=#804040][b]([/b][/color]pers_name[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#804040][b]call[/b][/color] field_error[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]1[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"person name"[/color] [COLOR=#804040][b],[/b][/color] priezvisko[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#0000ff] ' person id[/color]
pers_id [COLOR=#804040][b]=[/b][/color] csv_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]1[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]if[/b][/color] [COLOR=#008080]isNumeric[/color][COLOR=#804040][b]([/b][/color]pers_id[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]and[/b][/color] [COLOR=#804040][b]len[/b][/color][COLOR=#804040][b]([/b][/color]pers_id[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]10[/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#0000ff] 'pass[/color]
[COLOR=#804040][b]else[/b][/color]
[COLOR=#804040][b]call[/b][/color] field_error[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]2[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"person id"[/color] [COLOR=#804040][b],[/b][/color] pers_id[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#0000ff] ' contract number[/color]
contract_no [COLOR=#804040][b]=[/b][/color] csv_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]2[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]if[/b][/color] [COLOR=#008080]isNumeric[/color][COLOR=#804040][b]([/b][/color]contract_no[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]and[/b][/color] [COLOR=#804040][b]len[/b][/color][COLOR=#804040][b]([/b][/color]contract_no[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]9[/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#0000ff] 'pass[/color]
[COLOR=#804040][b]else[/b][/color]
[COLOR=#804040][b]call[/b][/color] field_error[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]3[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"contract number"[/color] [COLOR=#804040][b],[/b][/color] contract_no[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#0000ff] ' confirm[/color]
confirm [COLOR=#804040][b]=[/b][/color] csv_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]3[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]if[/b][/color] confirm [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"yes"[/color] [COLOR=#804040][b]or[/b][/color] confirm [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"no"[/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#0000ff] 'pass[/color]
[COLOR=#804040][b]else[/b][/color]
[COLOR=#804040][b]call[/b][/color] field_error[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]4[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"confirm"[/color] [COLOR=#804040][b],[/b][/color] confirm[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#0000ff] ' date in format YYYYMMDD[/color]
datum [COLOR=#804040][b]=[/b][/color] csv_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]4[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]if[/b][/color] [COLOR=#008080]isNumeric[/color][COLOR=#804040][b]([/b][/color]datum[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]and[/b][/color] [COLOR=#804040][b]([/b][/color]datum [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color] [COLOR=#804040][b]or[/b][/color] [COLOR=#804040][b]len[/b][/color][COLOR=#804040][b]([/b][/color]datum[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]8[/color][COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#0000ff] 'pass[/color]
[COLOR=#804040][b]else[/b][/color]
[COLOR=#804040][b]call[/b][/color] field_error[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]5[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"contract date"[/color] [COLOR=#804040][b],[/b][/color] datum[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
bank_code [COLOR=#804040][b]=[/b][/color] csv_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]5[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]if[/b][/color] [COLOR=#008080]isNumeric[/color][COLOR=#804040][b]([/b][/color]bank_code[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]and[/b][/color] [COLOR=#804040][b]([/b][/color]bank_code [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color] [COLOR=#804040][b]or[/b][/color] [COLOR=#804040][b]len[/b][/color][COLOR=#804040][b]([/b][/color]bank_code[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]4[/color][COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#0000ff] 'pass[/color]
[COLOR=#804040][b]else[/b][/color]
[COLOR=#804040][b]call[/b][/color] field_error[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]6[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"bank code"[/color] [COLOR=#804040][b],[/b][/color] bank_code[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#0000ff] ' created by[/color]
created_by [COLOR=#804040][b]=[/b][/color] csv_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]6[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]if[/b][/color] created_by [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]""[/color] [COLOR=#804040][b]or[/b][/color] [COLOR=#008080]isNumeric[/color][COLOR=#804040][b]([/b][/color]created_by[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#804040][b]call[/b][/color] field_error[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]7[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"created by"[/color] [COLOR=#804040][b],[/b][/color] created_by[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#0000ff] ' date in format YYYYMMDD[/color]
datum [COLOR=#804040][b]=[/b][/color] csv_array[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]7[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]if[/b][/color] [COLOR=#008080]isNumeric[/color][COLOR=#804040][b]([/b][/color]datum[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]and[/b][/color] [COLOR=#804040][b]([/b][/color]datum [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]0[/color] [COLOR=#804040][b]or[/b][/color] [COLOR=#804040][b]len[/b][/color][COLOR=#804040][b]([/b][/color]datum[COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]8[/color][COLOR=#804040][b])[/b][/color] [COLOR=#804040][b]then[/b][/color]
[COLOR=#0000ff] 'pass[/color]
[COLOR=#804040][b]else[/b][/color]
[COLOR=#804040][b]call[/b][/color] field_error[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]8[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"date created"[/color] [COLOR=#804040][b],[/b][/color] datum[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]sub[/b][/color]
[COLOR=#804040][b]sub[/b][/color] field_error[COLOR=#804040][b]([/b][/color]line_num[COLOR=#804040][b],[/b][/color] col_num[COLOR=#804040][b],[/b][/color] field_name[COLOR=#804040][b],[/b][/color] field_value[COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]dim[/b][/color] msg
msg [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"Error in CSV data:"[/color] [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]vbCr[/color] [COLOR=#804040][b]& _[/b][/color]
[COLOR=#ff00ff]" row: "[/color] [COLOR=#804040][b]&[/b][/color] line_num [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]vbCr[/color] [COLOR=#804040][b]& _[/b][/color]
[COLOR=#ff00ff]" col: "[/color] [COLOR=#804040][b]&[/b][/color] col_num [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]vbCr[/color] [COLOR=#804040][b]& _[/b][/color]
field_name [COLOR=#804040][b]&[/b][/color] [COLOR=#ff00ff]" = "[/color] [COLOR=#804040][b]&[/b][/color] field_value
[COLOR=#804040][b]call[/b][/color] [COLOR=#008080]msgbox[/color][COLOR=#804040][b]([/b][/color]msg[COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]vbCritical[/color][COLOR=#804040][b],[/b][/color] [COLOR=#ff00ff]"Error"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#0000ff] 'wscript.quit[/color]
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]sub[/b][/color]