Software tools (Requirements: 64 bit Windows Operating System)

The preparation of data in JSON (JavaScript Object Notation) and uploading the same to the DGT API link can be accomplished through the following tools:

1. Download Excel to JSON Converter tool, version 3.1
2. Download JSonEditor to Create, Open and Edit JSon file
3. Download Trainee data uploader through the DGT API, version 2.0
4. Download bulk photo converter to byte converter

Why these software utilities?

The Directorate General of Training (DGT) have mandated that admission data should be uploaded to their website only through an Application Program Interface (API) hosted at the URL http://164.100.213.37/MIS/api/traineeupload/UploadTrainees/ for uploading trainee data.

The challanges posed by the new API to the State level uploading agencies were:

1. The new API accepted data in JSON format, doing away with the Excel file based upload process.
2. The new API demanded that the photograph should also be uploaded along with other data. The photo file (typically JPG image) had to be read from the file system and each unsigned by value should be entered against the field named TraineePhoto. The bytes did not represent the RGB components of the image but the file itself (without regard to the content).
3. Client side front end was not provided by the DGT. This created a challange to each State/ UT to develop in-house software components to interface with the API.

The above challanges demanded the development of the complementary part of the API, which was to convert Excel data to the JSON format and to upload the same to the DGT API. The above software packages are meant to convert data entered in Excel file to JSON format (software named ExcelToJSONConverter) and save the same for verification. The data JSON data is saved in a text file.

The other software package named TraineeDataUploader is meant to upload the JSON data file to the DGT API (test API or Production API). It takes the login ID, password, and the text file containing the JSON data. It uploads the data to the DGT server and retrieves the response from the server. The server response is displayed at the bottom of the program window and the same may be printed for future reference.

The entire task can be divided into two parts, viz., Part 1 for converting Excel data to JSON format and Part 2 for uploading the JSON data to the DGT API.

Part 1: How to use ExcelToJSONConverter

The ExcelToJsonConverter allows the data to be entered in the more familiar and easier interface of an MS Excel document. There are 23 columns in the Excel document. Each column heading represents one JSON data parameter required by DGT API.

ExcelToJSonConverter

On Opening ExcelToJsonConverter, the first button named Download Excel Template copies an MS Excel Template file named TraineeDataTemplate.xltx to the Documents folder. After copying the file, MS Excel is invoked to open the template file. Delete the 2 rows of junk data entered in Rows 2 and 3. Start entering relavant information. The following points are to be remembered during data entry:

1. Date of Birth and Date of Admission are to be in the format YYYY-MM-DD.
2. 2. Session value should be 8YYYY (8th month of year YYYY) for newly admitted candidates.
3. 3. Trade name should include the term NSQF within brackets at the end of trade name. e.g., Electrician (NSQF), Fitter (NSQF). There is an important exception: Trade name Refrigeration And Air Condition Technician(NSQF) does not have a space between trade name and (NSQF).
4. Full path of the photo file (not the image) needs to be pasted at the 23^rd column (Column W) named TraineePhoto. This can be done by Selecting the file in File Explorer (Formerly My Computer) and Choosing Home->Copy Path or pressing Alt+H+CP. The contents of the photo file will be converted and included in the JSON output.
5. Possible values to be filled against gender, category, qualification, etc. are shown in the following figure.
   
6. Trade names are to be entered as shown in the following table:
   

   Trade

   Additive Manufacturing Technician Three D Printing (NSQF)

   Aeronautical Structure and Equipment Fitter (NSQF)

   Agro Processing (NSQF)

   Architectural Assistant (NSQF)

   Architectural Draughtsman (NE) (NSQF)

   Architectural Draughtsman (NSQF)

   Attendant Operator (Chemical Plant) (NSQF)

   Baker and Confectioner (NSQF)

   Bamboo Works (NSQF)

   Basic Cosmetology (NSQF)

   Building Maintenance (NSQF)

   Business Management (NSQF)

   Cabin/Room Attendant (NSQF)

   Cane Willow and Bamboo Worker (NSQF)

   Carpenter (NSQF)

   Catering & Hospitality Assistant (NSQF)

   Civil Engineer Assistant (NSQF)

   Computer Aided Embroidery And Designing (NSQF)

   Computer Hardware & Network Maintenance (NSQF)

   Computer Hardware & Networking Maintenance (One Year) (NSQF)

   Computer Operator & Programming Assistant (VI) (NSQF)

   Computer Operator and Programming Assistant (NSQF)

   Corporate House Keeping (NSQF)

   Craftsman Food Production (Vegetarian) (NSQF)

   Creche Management (NSQF)

   Cutting & Sewing (VI) (NSQF)

   Data Base System Assistant (NSQF)

   Data Entry Operator (NSQF)

   Data Preparation and Computer Software (NSQF)

   Dental Laboratory Equipment Technician (NSQF)

   Desk Top Publishing Operator (NSQF)

   Desktop Publishing Operator (VI) (NSQF)

   Digital Photographer (NSQF)

   Domestic House Keeping (NSQF)

   Domestic Painter (NSQF)

   Draughtsman (Civil) (DA) (NSQF)

   Draughtsman (Civil) (NSQF)

   Draughtsman (Mechanical) (NSQF)

   Dress Making (NSQF)

   Driver Cum Mechanic (NSQF)

   Early Childhood Educator(NSQF)

   Electrical-Power Distribution(NSQF)

   Electrician (DST) (NSQF)

   Electrician (NSQF)

   ELECTRICIAN POWER DISTRIBUTION (DST) (NSQF)

   Electrician Power Distribution (NSQF)

   Electronics Mechanic (NSQF)

   Electroplater (NSQF)

   Event Management Assitant (NSQF)

   Fashion Design & Technology (NSQF)

   Finance Executive (NSQF)

   Fire Technology and Industrial Safety Management (NSQF)

   Firemen (NSQF)

   Fitter (DST) (NSQF)

   Fitter (NSQF)

   Floriculture & Landscaping (NSQF)

   Food & Beverages Services Assistant (NSQF)

   Food Beverage (NSQF)

   Food Production (General) (NSQF)

   Footwear maker (NSQF)

   Foundryman (NSQF)

   Front Office Assistant (NSQF)

   Fruit and Vegetable Processor (NSQF)

   Geo Informatics Assitant (NSQF)

   Hair & Skin Care (VI) (NSQF)

   Hand Compositor (NSQF)

   Health Safety & Environment (NSQF)

   Health Sanitary Inspector (NSQF)

   Horticulture (NSQF)

   Hospital House Keeping (NSQF)

   House Keeper (NSQF)

   Human Resource Executive (NSQF)

   Industrial Painter (NSQF)

   Information Communication Technology System Maintenance (NSQF)

   Information Technology (NSQF)

   Institution House Keeping (NSQF)

   Instrument Mechanic (Chemical Plant) (NSQF)

   Instrument Mechanic (NSQF)

   Interior Design & Decoration (NSQF)

   IoT Technician (Smart Agriculture)(NSQF)

   IoT Technician (Smart City)(NSQF)

   IoT Technician (Smart healthcare)(NSQF)

   Laboratory Assistant (Chemical Plant) (NSQF)

   Laboratory Assistant-Chemical Plant (NSQF)

   Leather Goods Maker (NSQF)

   Letter Press Machine Minder (Platen & Cylinder) (NSQF)

   Library & Information Science (NSQF)

   Lift and Escalator Mechanic (NSQF)

   Litho- Offset Machine Minder (NSQF)

   Machinist (Grinder) (NSQF)

   Machinist (NSQF)

   Maintenance Mechanic (Chemical Plant) (NSQF)

   Marine Engine Fitter (NSQF)

   Marine Fitter (NSQF)

   Marketing Executive (NSQF)

   Mason (Building Constructor) (NSQF)

   Mech. Repair & Maintenance of Heavy Vehicles (NSQF)

   Mech. Repair & Maintenance of Light Vehicles (NSQF)

   Mechanic (Motor Vehicle) (NSQF)

   Mechanic (Tractor) (NSQF)

   Mechanic Agriculture Machinery (NSQF)

   Mechanic Air-conditioning Plant (NSQF)

   Mechanic Auto Body Painting (NSQF)

   Mechanic Auto Body Repair (NSQF)

   Mechanic Auto Electrical and Electronics (NSQF)

   Mechanic Communication Equipment Maintenance (NSQF)

   Mechanic Computer Hardware (NSQF)

   Mechanic Consumer Electronics (NSQF)

   Mechanic Consumer Electronics Appliances (NSQF)

   Mechanic Cum Operator Electronics Communication System (NSQF)

   Mechanic Diesel (NSQF)

   Mechanic Industrial Electronics (NSQF)

   Mechanic Lens/Prism Grinding (NSQF)

   Mechanic Machine Tool Maintenance (NSQF)

   Mechanic Medical Electronics (NSQF)

   Mechanic Mining Machinery (NSQF)

   Mechanic Motor Cycle (NSQF)

   Mechanic Radio & T.V. (NSQF)

   Mechanic Two & Three Wheeler (NSQF)

   Mechanic Watch and Clock (NSQF)

   Mechanic-cum-Operator Electronics Communication Sy (NSQF)

   Medical Transcription (NSQF)

   Metal Cutting Attendant (VI) (NSQF)

   Milk & Milk Products (NSQF)

   Moulder (NSQF)

   Multimedia Animation & Special Effects (NSQF)

   Network Technician (NSQF)

   Office Assitant cum Computer Operator (NSQF)

   Old Age Care (NSQF)

   Old Age Care Assistant (NSQF)

   Operator Advanced Machine Tools (NSQF)

   Painter General ( Two Years) (NSQF)

   Painter General (NSQF)

   Pattern Maker (NSQF)

   Photographer (NSQF)

   Physiotherapy Technician (NSQF)

   Plastic Processing Operator (NSQF)

   Plate Maker-Cum-Impositor (NSQF)

   Plumber (NSQF)

   Pre/Preparatory School Management (Assistant) (NSQF)

   Process Cameraman (NSQF)

   Pump Mechanic (NSQF)

   Pump Operator-Cum-Mechanic (NSQF)

   Radiology Technician (NSQF)

   Refractory Technician (NSQF)

   Refrigeration and Air Conditioning Technician(NSQF)

   Remotely Piloted Aircraft (RPA Drone Pilot) (NSQF)

   Rubber Technician (NSQF)

   Sanitary Hardware Fitter (NSQF)

   Secretarial Practice (English) (NSQF)

   Sewing Technology (NSQF)

   Sheet Metal Worker (DA) (NSQF)

   Sheet Metal Worker (NSQF)

   Smartphone Technician Cum App Tester (NSQF)

   Software Testing Assistant (NSQF)

   Soil Testing and Crop Technician (NSQF)

   Solar Technician (Electrical) (NSQF)

   Spa Therapy (NSQF)

   Spinning Technician (NSQF)

   Stenographer & Secretarial Assistant (English) (NSQF)

   Stenographer & Secretarial Assistant (Hindi) (NSQF)

   Stone Mining Machine Operator (NSQF)

   Stone Processing Machine Operator (NSQF)

   Surface Ornamentation Techniques (Embroidery) (NSQF)

   Surveyor (NSQF)

   Technician Mechatronics (NSQF)

   Technician Power Electronics System (NSQF)

   Textile Mechatronics (NSQF)

   Textile Wet Processing Technician (NSQF)

   Tool & Die Maker (Dies & Moulds) (NSQF)

   Tool & Die Maker (Press Tools, Jigs & Fixtures) (NSQF)

   Tourist Guide (NSQF)

   Travel & Tour Assistant (NSQF)

   Turner (NSQF)

   Vessel Navigator (NSQF)

   Weaving of Woolen Fabrics (NSQF)

   Weaving Technician (NSQF)

   Weaving Technician for Silk & Woolen Fabrics (NSQF)

   Welder (DA) (NSQF)

   Welder (Fabrication & Fitting) (NSQF)

   Welder (GMAW & GTAW) (NSQF)

   Welder (NSQF)

   Welder (Pipe) (NSQF)

   Welder (Structural) (NSQF)

   Welder (Welding & Inspection) (NSQF)

   Wireless Mechanic-cum-Operator (NSQF)

   Wireman (NSQF)

7. Remember to enter the equivalent community categories specified by the Central Government against the community categories of the State Government. Spelling mistakes in entering community may also lead to errors.
8. Trade names should match letter to letter.

Copying Excel Template File for Data Entry

Data Entry format in MS Excel

Note that DateOfBirth and DateOfAdmission are in YYYY-MM-DD format

Full Path and File Name for the Photo (Select the file in File Explorer, press Alt+H+CP to copy path)

Select the file in File Explorer, Choose Home->Copy Path press Alt+H+CP to copy path

How to copy from Excel file to ExcelToJsonConverter

To take the data from MS Excel file to the ExcelToJsonConverter, select all the rows and columns of the Excel file. (Press Ctrl+A if you prefer). Then, copy the contents to the clipboard (Press Ctrl+C).

Select All 23 Columns and All the Rows (including headings) and choose Copy (or Press Ctrl+C)

Now, switch to / open the ExcelToJsonConverter program and press Paste from Excel button or press Alt+P. The data copied from the Excel file is shown in the input text area. The data is ready for conversion.

Press Paste from Excel Button or Press Alt+P

Press the Convert to JSON series button. It seeks the folder name where converted json files are to be stored. It also takes a basename for the json file series that will be generated on conversion.

All previous files in the selected folder are deleted. The newly converted files have extension of .json. The conversion takes place. Converted results are displayed in the result area at the bottom area of the window.

Excel Data has been Converted to JSON Format.

It is not advisable (while not impossible) to edit the data in JSON format. Inadvertant mistakes might occur. It is preferable to make corrections in the MS Excel file and reconvert the Excel data to JSON format.

Make sure that each JSON file is less than 4.00 MB in size, since the present API of DGT rejects files greater than 4.00 MB in size with an error message stating Invalid data.

File Save Dialog

File Name with .json Extension. It may be opened using Notepad for Later Editing (Editing JSON is not recommended. Edit the Excel data and reconvert), if any.

About ExcelToJSonConverter

Terms of Use for ExcelToJSonConverter

Part 2: How to use TraineeDataUploader

Working with TraineeDataUploader is fairly straight forward. Enter the ID and password provided against the respective channel by the DGT for respective State/ UT. Choose the JSON data file saved during Part - 1 of the upload process by pressing the Browse button or Alt+B. The file name so selected is displayed below the browse button. There is yet another button nameed Upload Series or press Alt+S to choose the folder from which all json files will be uploaded (provided that the connection speed is reasonable).

Trainee Data Uploader

Enter your credentials

Press Browse Button (Alt+B) and Choose the JSON Data File (having .txt Extension

Ready for Pressing Upload Button (or the Shortcut Alt+U)

Ready for Pressing Upload Series Button (or the Shortcut Alt+S)

When the login credentials given and JSON data file is selected, press Upload button or Alt+U. Otherwise, press Upload Series button, select the folder containing json files. The data is transmitted to the DGT API server. Please wait patiently till the response arrives. It is advisable to ensure a steady and fast data connection during the upload process.

Response from the DGT API (An Error in the present case - Due to Wrong Mention of ITI Code. Similar Messages Popup for each error. Make corrections in the test API Excel file, export and test till there is no error.)

The response shows the number of records accepted and the number of records rejected. Detailed error message is provided against each rejected record, specifying the field name and reason for rejection. Print the response by pressing Print or Alt+P. Examine the reason and decide the correction to be made.

In case of errors in data fields, make corrections in the MS Excel data file, reconvert to JSON and again upload to the DGT API server.

It is advisable to repeat the upload process till the entire JSON file is bug free and all records are accepted. Once the DGT API server starts accepting all records in the JSON file, it is advisable to upload the data to the production channel of the DGT API.

Print the Response from DGT API for Later Reference (Press Print Button or Alt+P

PDF File Name to be Entered in Case Microsoft Print to PDF is Chosen against Printer

About TraineeDataUploader

Terms of Use for TraineeDataUploader

The author of the program has made an attempt to simplify the task of converting and uploading data relating to trainees to the DGT API. Reporting of errors for bug fixing is welcome at the email ID nagaradjanev@gmail.com.