CSV files processing

Hello, I need someone who is very familiar with csv files, texts processing read/write, regular expressions, C# programming and Visual Studio, to develop a simple desktop based application with 7 functions handling with csv files as defined below. The desktop application GUI has a TabPage for each function. The platform to run the application is Microsoft Windows Server 2008 but could be runnign on any Windows platform. The csv files could have 5000 rows or more, so the application should be robust enough to execute the functions properly. All the sourcing codes of the application should be sent to me. 1) First TabPage with name "New Titles" : 1.1) The format of the csv file is using standard csv files rules as per this link [login to view URL] The first line of the csv file has column headers. CSV is a delimited data format that has fields/columns separated by the comma character and records/rows terminated by newlines. Fields that contain a special character (comma, newline, or double quote), are enclosed in double quotes. If a line contains a single entry which is the empty string, it is enclosed in double quotes. Fields with embedded double-quote characters must be enclosed within double-quote characters, and each of the embedded double-quote characters must be represented by a pair of double-quote characters. These rules wil help you to parse the csv file, I think there are already many existing and available C# librairies functions you can use to read/parse csv files with these rules above. Is is important that the saved result csv files should copy exactly the fields value of the input csv file AS-IS, except of course for the fields which are modified as per the functions defined below. It means that for example if any fields values are within double-quotes, the result csv file should have the same field value within double-quotes. See attached the sample format of csv file for this TabPage. The format of the added csv file of this TabPage is : - Name,Audio,Text,Image,Text,Image,Text,Image,Text,Image,Text,Image,Text,Title,Title2,Title3,Title4,Category,Tag,Description,Username,Password,DeveloperKey,ApplicationID,AmazonCategory or - Name,Audio,Image,Text,Image,Text,Image,Text,Image,Text,Image,Text,Title,Title2,Title3,Title4,Category,Tag,Description,Username,Password,DeveloperKey,ApplicationID,AmazonCategory Just notice that in the second format, just the first Text column is removed compared to the first format. The application should only consider the columns which are used and modified in this function, and should ignore all the other columns and simply copy them AS-IS to the result csv file. 1.2) GUI design : 1.2.1) The name of the TabPage is New Titles, create a datagrid in the Keyword TabPage, the datagrid has 4 columns, Select column, Filename column, Path column, Status column. The "Add Files" button add a csv file into the datagrid, the "Clear List" button remove all the csv files of the datagrid. The "Run" button executes the functions. The users can add multiple csv files into the datagrid, each line of the datagrid has the 4 columns, Select column, Filename column, Path column and Status column. The column Select is like the checkbox with the sign view when a csv file is added to the datagrid. By default the csv file is selected when added to the datagrid. The column Filename is the filename of the added csv file. The column Path is the full path repertory of the added csv file. The column Status is : OPEN when the csv file is added to the datagrid, PROCESSING when the application executes the function, OK when the function is executed successfully on the csv file, and FAILED if something is wrong with the execution. 1.2.2) Concerning the column Titles of the csv file, create in the GUI a group and name it Titles (just draw a rectangular to clearly identify this group) : - create a text Title and a field value set by default to 10, the Title is mandatory so there is no checkbox - create a text Title2, a checkbox selected by default and a field value set to 6 which can be changed - create a text Title3, a checkbox selected by default and a field value set to 3 which can be changed - create a text Title4, a checkbox selected by default and a field value set to 1 which can be changed - create a text Title5, a checkbox selected by default, a text field set by default with the word Review and the users can modify the text as wished, a field value set to 1 which can be changed - create a text Title6, a checkbox selected by default, a text field set by default with the word Video and the users can modify the text as wished, and a field value set to 1 which can be changed - create a text Title7, a checkbox selected by default, a text label Amazon Category, and a field value set to 1 which can be changed - create a button Add Titles which create a text Title8, a checkbox selected by default, an empty text field and the users can type the text as wished, and a field value set to 1 which can be changed. The button Add Titles can add Title9, Title10 and so on 1.2.3) Create another group with a rectangular draw lines with the name Credentials, add in this group a checkbox selected by default, a text label CSV files and a windows browse option to locate the csv file which has a default path/filename value set to D:\Project\Amzn\ProductionData\Credential\[login to view URL] 1.2.4) Create a check box selected by default, a text label "First Text" and a text field with the default text set to : Free Review! Product Title 1.2.5) Create a check box unselected by default, a text label "Category" and a windows browse to load the csv file. 1.2.6) Create a "Save Options" button which save all the default values of all the options defined above into a config file, so for each runninng process of the application, all the saved default values in the config file are loaded into the application. 1.3) The functions of the button "Run" is defined as following : 1.3.1) The first step is to replace the column Title with the Title, Title2, Title3, Title4, Title5, Title6, Title7 For all the added csv file altogether, the app creates a new data structure to group all the rows of all the csv files which have the same Title column values case insensitive. You must use an appropriate datastructure to handle the process properly as the csv files could have 5000 rows or more. For each group having the same Title value, the application overwrites the Title column value with the value of Title, Title2, Title3, Title4, Title5, Title6, Title7 with the ratio defined under the group Title as defined above. For example the default ratio values of Title, Title2, Title3, Title4, Title5, Title6, Title7 are 10, 6, 3, 1, 1, 1, 1 So the application takes the first 10 rows of the group of duplicated Title and assign the value of the Title column (it is already correctly assigned as the Title is already correct). The next 6 rows have the Title value set to the value of the Title2 column The next 3 rows have the Title value set to the value of the Title3 column The next 1 row has the Title value set to the value of the Title4 column The next 1 row have the Title value set to the value of the Title5 column The next 1 row have the Title value set to the value of the Title6 column The next 1 row have the Title value set to the value of the Title7 column. Repeat the assignement process until all duplicated items of the group are completed, and repeat the same process for all the groups of duplicated items. If there are not enough duplicated items, just use the sequence order of Title, Title2, Title3, Title4, Title5, Title6, Title7 as 10, 6, 3, 1, 1, 1, 1 The app uses only the different Titles which have a checkbox selected (except for the Title label which is mandatory). The Title, Title1. Title2. Title3 and Title4 columns have already the values from the input csv files. The Title5 is build as follow, take the value of the Title column, add a space and add the word Review (by default) or the text written by the users in the text field The Title6 is build as follow, take the value of the Title column, add a space and add the word Video (by default) or the text written by the users in the text field The Title7 is build as follow, take the value of the Title column, add a space and add the value of it's associated AmazonCategory column If there are new added Titles, take the value of the Title column, add a space and add the text written by the users in the text field All the Titles should have maximum 100 bytes or less, if they are above the max 100 bytes truncate the last necessary word(s) of the Titles to match the size. Obviously the datastructure must record which row belongs to which csv file, for the save to the corresponding result csv file. 1.3.2) When all the rows of all groups of duplicated items are assigned with the Title column following the specifications of point 1.3.1) above, here are the next functions : 1.3.2.1) If the checkbox of the Credential is selected, takes the credential csv file added and assign the Username,Password,DeveloperKey,ApplicationID columns of the first row of the datastructure with the first row of the Credential csv file, the second row of the datastructure with the second row of the Credential csv file, and so on, until all the total rows of the datastruture are completed. In most cases the Credential csv files have much less rows than all the rows of all csv files, so the application must loop on the credential csv file for the assignement. The format column headers of the Crendial csv file is : Username,Password,ApplicationID,DeveloperKey So take the values of these columns and assign to the corresponding column headers Username,Password,DeveloperKey,ApplicationID of the rows (be careful order of column headers might not be the same but the colum name is identical). If the checkbox is not selected, no change is done for the Username,Password,DeveloperKey,ApplicationID columns 1.3.2.2) For the first format csv file : if the checkbox of the text label "First Text" is selected replace the first Text column with the text of the text label "First Text" as defined above. The word Title in this text is just used to simplify the implementation, and it should be replaced as follow : For all the rows of the datastructure which have the value of the Title column with Title2, Title3, Title4 replace the word Title with the value of the Title column For all the other rows of the datastructure which have the value of the Title column with Title, Tile5, Title6, Title7 and so on, do nothing For the second format csv file : either the checkbox of the text label "First Text" is selected or not, nothing is done 1.3.2.3) For the Tag column : all the rows of the datastructure which have the value of the Title column with Title2, Title3, Title4, replace the value of the Tag column as follow : The value of the Tag column is a sequence of keywords, the first keyword is the value of the Title column, then add a comma, then add each word of the Title value with each word separated by a comma, add the value of the AmazonCategory column, add a comma, add each word of the value of the AmazonCategory column with each word separated by a comma. The value of the Tag is embedded between double-quotes (to be safe with csv rule see above). So the Tag has the sequence of keywords like keyword1,keyword2,keyword3 and so on where each keyword can have one word or multiple words, and each keyword is separated by a comma. The Tag should comply to these following rules : a) each keyword of the Tag should have at least 2 bytes and maximum 30 bytes (non-including the double-quotes). If a keyword is less than 2 bytes, it is removed. If the keyword is higher than 30 bytes, truncate the necessary last word(s) so the truncated keyword is less or equal to 30 bytes. The maximum size length of the Tag value should be less or equal to 500 bytes (non including the double-quotes), if it is above 500 bytes truncate the necessary last keyword(s) to match the size of 500 bytes. b) if there is any duplicated keyword in the Tag value, remove the duplicated keyword. 1.3.2.4) For the Description column : For the rows of the datastructure which have the value of the Title column with Title2, Title3, Title4 the application parses the value of the Description column, bypass the first line which have texts, bypass 2 following Break Lines, and should find the next lines which starts with text, it could have 1 or 2 lines with texts, and then 2 Break Lines. If there is one line of text before the 2 Break Lines, replace the text of this line with the value of the Title column value. If there are 2 text lines before the 2 Break Lines, replace the second text line with the value of the Title column. The value of the Description column has a maximum length of 5000 bytes, if it is over 5000 bytes truncate the necessary last word(s) to match the size. 1.3.2.5) For the Category column : If the checkbox of the text lable Category is selected, takes the Category csv file added and assign the Category of the first row of the datastructure with the first row of the Category csv file, the second row of the datastructure with the second row of the Credential csv file, and so on, until all the total rows of the datastruture are completed. In most cases the Category csv files have much less rows than all the rows of all csv files, so the application must loop on the Category csv file for the assignement. The format of the Category csv file has one column header : Category If the checkbox is not selected, no change is done for the Category column. 1.3.3) At the end of the whole process, for each csv file added to the datagrid, an associated result csv file is automatically generated and saved after the functions "Run" is executed and completed successfully. The columns Title2, Title3, Title4, and AmazonCategory are removed and not saved to the result csv file. If there is something wrong during the execution there is no created result csv file. The column headers of the result csv file is : Name,Audio,Text,Image,Text,Image,Text,Image,Text,Image,Text,Image,Text,Title,Category,Tag,Description,Username,Password,DeveloperKey,ApplicationID or - Name,Audio,Image,Text,Image,Text,Image,Text,Image,Text,Image,Text,Title,Category,Tag,Description,Username,Password,DeveloperKey,ApplicationIDThe result csv file is stored on the same repertory of the input csv file added, the filename is input csv file + _newtitle, for example if the input csv file is [login to view URL], the result csv file is [login to view URL] (if the result csv filename already exists in the repertory just overwrite it). As the application records the row of it's associated csv file into the datastructure, each row of the input csv file will be saved to it's asscociated result csv file. It means that, the result csv file will have the same rows number as it's associated input csv file. 2) Second TabPage with name "CSV Extract" : Separate and Extract rows with the Status column equal to OK and rows with the Status column equal to FAILED. 2.1) GUI Similar to Tabpage 1, with the button "Add Files", "Clear List", a Datagrid and a button "Run". The user could add one or multiple csv files into the datagrid. For each csv file added the application separates and extracts rows with the Status column equal to OK and rows with the Status column equal to FAILED. 2.2) Save automatically to result csv file : 2.2.1) Save automatically all the rows with Status equal to OK to the result csv file. If there is something wrong during the execution there is no created result csv file. The result csv file is stored on the same repertory as the csv file added, the filename is the added csv file + _extractok, for example if the first csv file is [login to view URL], the result csv file is [login to view URL] (if the result csv filename already exists in the repertory just overwrite it). 2.2.2) Save automatically all the rows with Status equal to FAILED to the result csv file. If there is something wrong during the execution there is no created result csv file. The result csv file is stored on the same repertory as the csv file added, the filename is the added csv file + _extractfailed, for example if the first csv file is [login to view URL], the result csv file is [login to view URL] (if the result csv filename already exists in the repertory just overwrite it). 3) Third Tabpage with name "CSV Compare" : Compare Name column or Status column of the first added csv file and the second csv file, and save to result csv file. 3.1) GUI Similar to Tabpage 1, with the button "Add Files", "Clear List", a Datagrid and a button "Run". The user adds a pair of 2 csv files into datagrid for each line of the datagrid. Multiple pairs of csv files can be added, one pair of csv file for each line of the datagrid. Some pairs of csv files have a Status column and some pairs of csv files do not have the Status column. In all cases there is always the column Name, all the columns of the first or second csv files should by copied AS-IS to the result csv file. For information, each pair of the first and second added csv files are always on the same repertory, and could not have the same column headers : 3.2) Case csv files having the Status column : 3.2.1) The application separates and selects all rows of the second added csv file which have the Status column equal to FAILED, separate and unselect all rows of the second added csv file which have the Status column equal to OK 3.2.2) The application selects all rows of the first added csv file whose value of the Name column is not in the second csv file. 3.2.3) Save to result csv file : 3.2.3.1) Save automatically all the selected rows of point 2.1.1 and 2.1.2 to the result csv file. If there is something wrong during the execution there is no created result csv file. The result csv file is stored on the same repertory of the first csv file added, the filename is the first csv file + _bis, for example if the first csv file is [login to view URL], the result csv file is [login to view URL] (if the result csv filename already exists in the repertory just overwrite it). 3.2.3.2) Save automatically all the unselected rows of point 2.1.1 to a result csv file (actually all rows having Status column equal to OK). If there is something wrong during the execution there is no created result csv file. The result csv file is stored on the same repertory of the second csv file added, the filename is the second csv file + _compareok, for example if the second csv file is [login to view URL], the result csv file is [login to view URL] (if the result csv filename already exists in the repertory just overwrite it). 3.3) Case of csv files not having the Status column : 3.3.1) The application selects all rows of the first added csv file whose value of the Name column is not in the second csv file. 3.3.2) Save automatically all the selected rows to the result csv file. If there is something wrong during the execution there is no created result csv file. The result csv file is stored on the same repertory of the first csv file added, the filename is the first csv file + _bis, for example if the first csv file is [login to view URL], the result csv file is [login to view URL] (if the result csv filename already exists in the repertory just overwrite it). 4) Fourth TabPage with name "Audio&Category&Credential" : Change the Audio, Category and Username, Password, DeveloperKey, ApplicationID columns 4.1) GUI Similar to TabPage 1, with the button "Add Files", "Clear List", a Datagrid, a checkbox unselected by default with a text label "Audio" and a windows browse to load the csv file, a checkbox unselected by default with a text label "Category" and a windows browse to load the csv file, a checkbox unselected by default with a text label "Credential" and a windows browse to load the csv file, and a button "Run". 4.2) For the Audio column : If the checkbox of the text lable Audio is selected, takes the Audio csv file added and replace the value of the Audio column of the first row of the added csv file with the value of the Audio column of the first row of the Audio csv file, the second row of the added csv file with the value of the Audio column of the second row of the Audio csv file, and so on, until all the rows of the added csv file are completed. In most cases the Audio csv files have much less rows than all the rows of the added csv file, so the application must loop on the Audio csv file for the assignement. The format of the Audio csv file has one column header : Audio If the checkbox is not selected, no change is done for the Audio column. 4.3) For the Category column : If the checkbox of the text lable Category is selected, takes the Category csv file added and replace the value of the Category column of the first row of the added csv file with the value of the Category column of the first row of the Category csv file, the second row of the added csv file with the value of the Category column of the second row of the Category csv file, and so on, until all the rows of the added csv file are completed. In most cases the Category csv file has much less rows than all the rows of the added csv file, so the application must loop on the Category csv file for the assignement. The format of the Category csv file has one column header : Category If the checkbox is not selected, no change is done for the Category column. 4.4) For the Username, Password, DeveloperKey, ApplicationID columns : If the checkbox of the text lable Credential is selected, takes the Credential csv file added and replace the value of the Username, Password, DeveloperKey, ApplicationID columns of the first row of the added csv file with the value of the Username, Password, DeveloperKey, ApplicationID columns of the first row of the Credential csv file, the second row of the added csv file with the value of the Username, Password, DeveloperKey, ApplicationID columns of the second row of the Credential csv file, and so on, until all the rows of the added csv file are completed. In most cases the Category csv file has much less rows than all the rows of the added csv file, so the application must loop on the Category csv file for the assignement. The format of the Credentail csv file has 4 column headers : Username,Password,ApplicationID,DeveloperKey If the checkbox is not selected, no change is done for the Username, Password, DeveloperKey, ApplicationID columns. 4.5) Save automatically all the rows to the result csv file. If there is something wrong during the execution there is no created result csv file. The result csv file is stored on the same repertory of the added csv file added, the filename is the added csv file + _acc, for example if the first csv file is [login to view URL], the result csv file is [login to view URL] (if the result csv filename already exists in the repertory just overwrite it). 5) Fifth TabPage with name "CSV Merge Images&Keyword" : Merge some columns of the first added csv file into the second csv file. For information each pair of csv files are stored on the same directory, they have the same row numbers and the values of the Name column of the first csv file are identical to the value of the Name column of the second csv file. 5.1) GUI Similar to TabPage 1, with the button "Add Files", "Clear List", a Datagrid and a button "Run". The user adds a pair of 2 csv files into datagrid for each line of the datagrid. Multiple pairs of csv files can be added, one pair of csv file for each line of the datagrid. For information, each pair of the first and second added csv files are on the same repertory, and does not have the same column headers. 5.2) For each pair of csv files added to the datagrid and all rows of the second csv file the application copy all the columns of the second csv file and find the matching value of the Name column of the first csv file, for the value of the Name column matching between both csv files, copy all the associated value of the Image columns of the first csv file respectively to the Images columns of the second csv file. 5.3) Save automatically all the rows to the result csv file. If there is something wrong during the execution there is no created result csv file. The result csv file is stored on the same repertory of the second csv file added, the filename is the second csv file + _ready, for example if the first csv file is [login to view URL], the result csv file is [login to view URL] (if the result csv filename already exists in the repertory just overwrite it). 6) Sixth TabPage with name "CSV Merge" : Merge all rows of all csv files added into a result csv file. For information, all the added csv files have the same column headers and are stored on the same repertory. 6.1) Similar to TabPage 1, with the button "Add Files", "Clear List", a Datagrid and a button "Run". The user adds multiple csv files into the datagrid. The application simply merge all the rows of all the added csv file. Actually the application appends all the rows of the first csv added, and then the second csv file and so on. 6.2) Save automatically all the merged rows of point 3.1 to a result csv file. If there is something wrong during the execution there is no created result csv file. The result csv file is stored on the same repertory of the first csv file added, the filename is the first csv file + _merge, for example if the first csv file is [login to view URL], the result csv file is [login to view URL] (if the result csv filename already exists in the repertory just overwrite it). 7) Seventh TabPage with name "CSV Split" : Split the added csv file into smaller result csv files. 7.1) Similar to Tabpage 1, with the button "Add Files", "Clear List", a Datagrid, a button "Run" and a text label "Rows Number" with a field where the users can type the value number. The user can adds multiple csv files into the datagrid. For each csv file added the application splits the total rows of the csv file with the value number. For example, if an added csv files has 3800 rows, the user type the value number 500, the program generate 7 csv files of 500 rows and the last csv file has 300 rows. The first result csv file has the first 500 rows of the added csv file, the second result csv file has the next 500 rows of the added csv file, and so on. 7.2) Save automatically all the rows of point 3.1 to the result csv files. If there is something wrong during the execution there is no created result csv [login to view URL] first result csv file has the filename of the added csv file + _1, the second csv file has the added csv file + _2, and so on. The result csv file is stored on the same repertory of the added csv file added, for example if the first csv file is [login to view URL], the result csv files are [login to view URL], [login to view URL] and so on (if the result csv filename already exists in the repertory just overwrite it). Thanks