Uploading CSV to SQL Server

Question

I'm building a system that reads 5 CSV files each month. These files are supposed to follow a certain format and ordering. I have one master table and 5 temporary tables. Each CSV file is read first and then bulk inserted into its corresponding temporary table. After bulk inserting the 5 csv files into their respective temporary tables I once again insert all the records from the temporary table to the master table. This makes sure that all files are uploaded first before inserting the data to the master table.

I built this system using ASP.net and during debugging and testing everything went fine. The problem occurs whenever I deploy the application to a production server. After I deployed the application I used the same csv files I uploaded during development and testing and the system shows a data conversion error from string to date time format.

I tried many things to fix this but it seems the problem still persist. I tried changing the collation of the production database to the same one I used during development. I also tried changing some regional settings in the production server but it still doesn't work.

I thought maybe I can handle this programmatically and instead of bulk inserting from the temporary tables to the master table I would write some kind of a for loop that would insert each record manually to the master table, but then I suppose it would create a performance issue since I'll be inserting around 100,000 records each time.

I wonder if anyone has faced a similar issue during deployment. It still seems weird to me that the behaviour of the application changed after deployment.

following is a portion of the code where it uploads the inventory.csv file to the server and then bulk inserting the csv into a temporary table TB_TEMP_INVENTORY then inserting the records from temp to the master table TB_CATTLE. this is done to 4 other files and is almost identical to this.

        OleDbConnection conn = new     OleDbConnection(ConfigurationManager.AppSettings["LivestockConnectionString"]);
        OleDbCommand comm;
        OleDbDataAdapter adapter;
        DataTable table = new DataTable();
        string file = string.Empty;
        string content = string.Empty;
        StreamReader reader;
        StreamWriter writer;
        string month = monthDropDownList.SelectedValue;
        string year = yearDropDownList.SelectedItem.Text;

        // upload inventory file
        file = System.IO.Path.GetFileName(inventoryFileUpload.PostedFile.FileName);
        inventoryFileUpload.PostedFile.SaveAs("C://LivestockCSV//" + file);

        // clean inventory file
        file = "C://LivestockCSV//" + file;
        reader = new StreamReader(file);
        content = reader.ReadToEnd();
        reader.Close();
        writer = new StreamWriter(file);
        writer.Write(content.Replace("\"", ""));        // remove quotation
        writer.Close();
        writer = new StreamWriter(file);
        writer.Write(content.Replace(",NULL,", ",,"));  // remove NULL
        writer.Close();
        writer = new StreamWriter(file);
        writer.Write(content.Replace(",0,", ",,"));     // remove 0 dates
        writer.Close();
        writer = new StreamWriter(file);
        writer.Write(content.Replace(",0", ","));       // remove 0 dates at eol
        writer.Close();

        try
        {
            conn.Open();
            comm = new OleDbCommand("TRUNCATE TABLE TB_TEMP_INVENTORY", conn);    // clear temp table
            comm.ExecuteNonQuery();

            // bulk insert from csv to temp table
            comm = new OleDbCommand(@"SET DATEFORMAT DMY;
                                    BULK INSERT TB_TEMP_INVENTORY
                                    FROM '" + file + "'" +
                                    @" WITH
                                    (
                                        FIELDTERMINATOR = ',',
                                        ROWTERMINATOR = '\n'
                                    )", conn);
            comm.ExecuteNonQuery();

            // check if data for same month exists in cattle table
            comm = new OleDbCommand(@"SELECT *
                                    FROM TB_CATTLE
                                    WHERE Report='Inventory' AND Month=" + month + " AND Year=" + year, conn);

            if (comm.ExecuteScalar() != null)
            {
                comm = new OleDbCommand(@"DELETE
                                        FROM TB_CATTLE
                                        WHERE Report='Inventory' AND Month=" + month + " AND Year=" + year, conn);
                comm.ExecuteNonQuery();
            }


            // insert into master cattle table
            comm = new OleDbCommand(@"SET DATEFORMAT MDY;
                                    INSERT INTO TB_CATTLE(ID, Sex, BirthDate, FirstCalveDate, CurrentUnit, OriginalCost, AccumulatedDepreciation, WrittenDownValue, NetRealizableValue, CapitalGainLoss, Month, Year, Report, Locked, UploadedBy, UploadedAt)
                                    SELECT DISTINCT ID, Sex, BirthDate, FirstCalveDate, CurrentUnit, 0, 0, 0, 0, 0, " + month + ", " + year + @", 'Inventory', 0, 'Admin', '" + DateTime.Now + @"'
                                    FROM TB_TEMP_INVENTORY", conn);
            comm.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {
            ClientScript.RegisterStartupScript(typeof(string), "key", "<script>alert('" + ex.Message + "');</script>");
            return;
        }

Answer 1

You don't specify how you are doing the insert, but a reasonable option here would be something like SqlBulkCopy, which can take either a DataTable or an IDataReader as input; this would give you ample opportunity to massage the data - either in-memory (DataTable), or via the streaming API (IDataReader), while still using an efficient import. CsvReader is a good option for loading the CSV.

The other option is to use a very basic insert into the staging table, and massage the data via TSQL code.

Re why has it changed between dev/production; the most likely answers are:

• the data you used in dev was not representative
• there is an environmental/configuration difference between the two

Answer 2

1) Check SQL Server LANGUAGE and DATEFORMAT settings for dev/testing & production env.:

DBCC USEROPTIONS

2) What date format is used in CSV files (source) ?

3) What data type is used for date/time field (destination) ?

DECLARE @v VARCHAR(10) = '2010-08-23';

SET DATEFORMAT mdy;
SELECT CAST(@v AS DATETIME)
        ,CAST(@v AS DATE)
        ,YEAR(CAST(@v AS DATETIME))
        ,MONTH(CAST(@v AS DATETIME))
        ,DAY(CAST(@v AS DATETIME));

SET DATEFORMAT dmy;
SELECT CAST(@v AS DATETIME)
        ,CAST(@v AS DATE);