Working with CSV Files

Anyone who works with CSV files will know this issue. The CSV file was generated by a machine or exported from a database. But it was opened and saved as a CSV file in Excel by the user to check the contents and in doing so the number value is saved as an exponential value and the required data is lost.

You can open a CSV file correctly in Excel by first opening excel and then using the data option and select “From Text” on the final step select text for each column to preserve the format. This link will detail the steps.

However your users will probably just double click on the CSV file which will open the file in excel and the damage is done. I have created a solution to this issue with the following steps.

1. Create a batch file which will open the Excel Macro and pass the name of the file as a parameter. To ensure the user always uses this method you can right click on a csv file and select open with – then choose default program and point to your batch file.
2. In Excel I designed a userform to give the user some choices on the filetype and location.
3. In the workbook I added a macro to load the file into an array and then format the array size as text in excel and paste the values.
4. Non Standard Characters – I added another function to the userform to allow you to find non standard characters in the file, these characters caused issues when the files were uploaded to other system. This function is based on my character set but you can amend or add to the characters as required.

You can download a copy of the excel file and a sample batch file form this link.

All the source for the macro is in the workbook feel free to customise it for your own environment.