(Advance knowledge of c++ and sql is required)
Say you had a requirement to create a c sharp program to upload a csv file to a SQL database table and manipulate the data and put it into another table. The client gives you a sample csv file. You create the program to upload the csv file to table into a SQL database based on the file name (The dynamic table name creating is beyond this topic). If the csv file name was helloworld.csv then in the database the SQL table will be helloworld. Then take that table and manipulate the data and put it into another table by creating a stored procedure to use that file name table in the select statement. After running the application in a live environment, the client has been using it for over a year. Then one day the client said it’s not working. The client gives you a sample of the file. You run the program and you get the error. So you investigate and found that the original file has double quotes in the csv file. The original sample file used for building the program did not have double quotes. You ask the client why the csv file has double quotes. The client said when he gave the original file the first time to create the program. He gave the file in a modified format. But the client say when they get the file from the third party vendor they open it to review the data and make changes to it if needed. So this one particular file did not need any changes and it was uploaded. After investigating the issue, the problem was that the client was always modifying the data and saving it before uploading which did not have a problem when the stored procedure was looking for the field names [<fieldname>] in the select statement. But now it contains double quotes in the field names [“<fieldname>”] which the select statement could not find in the table. To resolve this, we added this line of code right before it builds
line = line.Replace(“\””, “”); the array of rows to be uploaded to the SQL table. Therefore if the client decides to modify the file or not it will remove the double quotes before uploading to SQL and the stored procedure executes and process the tables without error in finding the column names.
Below are sample files for scenario.
Sample file csv files.
File from third party vendor before opened and modified.
File opened, modified and saved.
Sample tables in sql
Table with double quotes in the data fields including the header names.
Table without double quotes in the data fields including the header names.
Modified program using the same table with double quotes.
Table without double quotes in the data fields including the header names.
