IMPORTANT. This project needs to be completed this week before the end of the weekend. If you cannot guarantee this please do not bid as it is needed for a demonstration on Sunday evening.
Requirement is for a small utility which will load and parse files in either Microsoft Excel (newst formats), OpenOffice Calc, .TXT or .CSV file formats, allow limited manipulation, and then attempt to import them row by row into a Microsoft SQL database table.
There will be three options initially: Import, Replace and Update. The choice here will determine how the utility later actions the updates and which columns are required to be supplied.
There will be an option to type an input file into a box, or click 'browse' to select one using the standard windows file selection window. Once the opened the utility will present a grid containing the rows of data. In the case of .TXT files it may be necessary to have a field at the top to allow the user to define the delimiter in case this is not a comma.
The user will be able to modify data in the fields in the grid, sort the grid by any of the column headings and (most importantly) easily delete rows.
The grid's column headings will be named F1, F2 etc.
The user will specify what field in the data file represents which of the required columns. There will be only two columns required to be defined if 'update' is selected: VendorSKU and VendorCode. If 'Import' is selected, the required column names are: VendorSku, VendorCode, Description, List, Cost, Retail. This will be done by picking, for each potential column name (List given) which of F1, F2, F3 contains this information.
Once the user has designated the columns, they will click a button labelled either 'import' or 'update' depending on the option selected above.
The utility will then perform the procedure chosen.
1) Update: UPDATE rows with the filled information. This must include VendorSKU and VendorCode and can optionally include one, two or three of List, Cost, Retail. Any rows with the mandatory fields filled but none of List, Cost or Retail will be supressed/skipped by the utility.
2) Insert: INSERT rows into the table with rows taking information from the grid and filling the gaps with default values for any non-completed columns. Details of the schema will be supplied, but there are currently 48 columns in total, some are NOT NULL so default values need to be supplied - others can be passed NULL. There needs to be a method by which these defaults are stored, so that when the schema changes and more fields are added, I can simply update the 'template' so that the utlity knows how to handle the extra fields.
This needs to be completed line by line. The purpose here is that some of the data is poor quality, contains duplicates or invalid values etc. We need to separate out the rows that were not successfully imported.
If the row is inserted OK, keep a count, and move to the next one.
If the row is not inserted OK, we append it to a new file which is to be created in the same folder as the import file, and named the same but with a prefix of 'ERRORs' The first field should be the error returned when attempting to import the row, followed by the fields exactly as they were given in the grid.
Keep a count of the failed records too.
At the end of the process we have: Number of successful rows, number of failed rows, and a data file created with the failed data. The numbers of succesful/unsuccessful entries should be presented to the user at the bottom of the form, and a button should become visible to wipe the grid so they can start over on a new speadsheet.
I have a simple template for how the GUI should look which I will submit to the programmer as we wish to maintain a similar style as other tools we have written.