CSV Fixing utility

The application intended to reformat CSV files so that they can be uploaded to the verification platform without any errors.

It should allow correcting of the following errors:

  1. Too few columns in a row: This is often caused by data being incorrectly exported from databases, the file being edited with Excel or the combining of multiple files
  2. Too many columns in a row: This is often caused by incorrect quoting of the field values
  3. No email: All rows should have an email, there is not much use for data that has no email.

During application startup, smclient verifies that you are using the most up-to-date version of smclient. If not, you will be prompted to upgrade your application. Download links for all platforms will be shown in the following dialog. You can also view version history, download older versions if needed, and see what has been changed. This file is always available by navigating to the menu bar, and selecting "smclient version"

You can also find help, and get information about smclient by pressing the "Help" button.

Select a file to process and choose processing mode

UI

UI

  1. Choose "Simple Mode" or "Advanced Mode"
  1. Click on the "file browse" button Browse to select the CSV file to process

Simple Mode

Step One: Parsing

Simple Mode

Simple Mode

If the program is being run as "Simple Mode" you will only see a progress bar and you will automatically be taken to the next step. During parsing the program is removing all rows with errors and putting them in the discards file. This includes rows with no email, too few/many columns, and encoding errors.

Step Two: Saving the results

Results Page

Results Page

Once parsing is complete, this page is show that shows statistics on how many lines were written to the discards file, fixed file, etc.

When done reviewing the statistics click on "Save Files" to go to the final page.

NOTE: This program creates an output directory for the result files. Whichever location you choose, the program will delete all the files in that folder.

Default file names are filename_part_XX.csv and filename_DISCARDED.csv in the directory that you selected

Step Three: Finish the job.

Last Page

Last Page

At this point you are done! You can choose "Open File Location" to bring up a file browser to view your resulting file. When done reviewing you can press "Exit" to exit the program or "Fix Another" to fix another CSV file and restart the program.

Advanced Mode

Step One: Choose your options

This step is skipped if you choose the "Simple Mode"

Options

Options

There are four options available:

  1. "Discard All Encoding Errors (row)": When this option is checked, correction of encoding errors is skipped and the program will take every row of the CSV file that has encoding errors and places them in the discards file _DISCARDS.csv. This allows you to do what you wish with the encoding errors.
  2. "Discard All Encoding Errors (field)": When this option is checked, correction of encoding errors is skipped and the program will simply remove fields that have characters that are not in the valid UTF8 range.
  3. Set the split value. This allows you to decide how many rows of the CSV file will go into the final files.
  4. Set the file separator. This option will replace any separator in the file to your choice. Default is comma.

Once the options are set, the 'Next' button should be clicked

Step Two: Setting Headers

Setting Headers

Setting Headers

There is an option "Do Not Discard Header" at the bottom of the widow. If the first row of the file contains "data" and not "headers" make sure to check this option.

This step allows you to identify which column should be assigned to a particular data type (header). The program does attempt to identify the column based on the header which already exists in the CSV. Make sure that all columns are correctly assigned and that any columns to be discarded are marked as 'Discard'.

Step Three: Parsing

Advanced Mode

Advanced Mode

As the file is being parsed the progress indicator at the bottom of the window will indicate parsing progress.

When the program encounters an error in the CSV it will display the parsed value columns in the "Error Row" area and the raw data in the "Edit Row" area.

You can can either click the "Discard Row" button to discard the bad data and save it to the discards file, or you can edit the data in the "Edit Row" area to fix the problem and save it.

Once the program detects that the edited data is valid, you can save the row by clicking the "Save Changes" button.

This process is repeated until the entire file has been parsed.

Step Four: Saving the results

Results Page

Results Page

Once parsing is complete, this page is show that shows statistics on how many lines were written to the discards file, fixed file, etc.

When done reviewing the statistics click on "Save Files" to go to the final page.

NOTE: This program creates an output directory for the result files. Whichever location you choose, the program will delete all the files in that folder.

Default file names are filename_part_XX.csv and filename_DISCARDED.csv in the directory that you selected

Step Five: Finish the job.

Last Page

Last Page

At this point you are done! You can choose "Open File Location" to bring up a file browser to view your resulting file. When done reviewing you can press "Exit" to exit the program or "Fix Another" to fix another CSV file and restart the program.