CODEWITHSUNDEEP
c#sql-servercsvupload
JLM
JLM

Reputation: 57

Loading csv file into a SQL Server database results in error

I have this code which is trying to load a .csv file into a SQL Server database and dynamically create the table. I see it says there is a syntax error and an unclosed quotation mark, but I don't see it. Any ideas?

class DTUpload
{
    public static void CSVUploadCode()
    {
        string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
        string LogFolder = @"C:\Log";

        try
        {
            // Declare Variables and provide values
            string SourceFolderPath = @"C:\Old\";
            string FileExtension = "*.csv";
            string FileDelimiter = ",";
            string ColumnsDataType = "NVARCHAR(max)";
            string SchemaName = "dbo";

            // Get files from folder
            string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);

            foreach (string fileName in fileEntries)
            {
                // Create Connection to SQL Server in which you would like to create tables and load data
                SqlConnection SQLConnection = new SqlConnection();
                SQLConnection.ConnectionString = "Data Source = *******; Initial Catalog = *******; User id=*******;" + "Password=*********;";

                // Writing Data of File Into Table
                string TableName = "";
                int counter = 0;
                string line;
                string ColumnList = "";

                System.IO.StreamReader SourceFile = new System.IO.StreamReader(fileName);

                SQLConnection.Open();

                while ((line = SourceFile.ReadLine()) != null)
                {
                    if (counter == 0)
                    {
                        // Read the header and prepare create table statement
                        ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";
                        TableName = (((fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")).Replace("\\", ""));
                        string CreateTableStatement = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + SchemaName + "].";
                        CreateTableStatement += "[" + TableName + "]')";
                        CreateTableStatement += " AND type in (N'U'))DROP TABLE [" + SchemaName + "].";
                        CreateTableStatement += "[" + TableName + "]  Create Table " + SchemaName + ".[" + TableName + "]";
                        CreateTableStatement += "([" + line.Replace(FileDelimiter, "] " + ColumnsDataType + ",[") + "] " + ColumnsDataType + ")";
                        SqlCommand CreateTableCmd = new SqlCommand(CreateTableStatement, SQLConnection);
                        CreateTableCmd.ExecuteNonQuery();
                    }
                    else
                    {
                        // Prepare Insert Statement and execute to insert data
                        string query = "Insert into " + SchemaName + ".[" + TableName + "] (" + ColumnList + ") ";
                        query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

                        SqlCommand SQLCmd = new SqlCommand(query, SQLConnection);
                        SQLCmd.ExecuteNonQuery();
                    }

                    counter++;
                }

                SourceFile.Close();
                SQLConnection.Close();
            }
        }
        catch (Exception exception)
        {
            // Create Log File for Errors
            using (StreamWriter sw = File.CreateText(LogFolder
               + "\\" + "ErrorLog_" + datetime + ".log"))
            {
              sw.WriteLine(exception.ToString());
            }
        }
    }
}

This is the error message I get:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'Annual'.

Unclosed quotation mark after the character string '] NVARCHAR(max))'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning (TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery (TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at ParseColumnCount.DTUpload.CSVUploadCode() in C:\ParseColumnCount\DTUpload.cs:line 66

ClientConnectionId:********************
Error Number:102,State:1,Class:15

ClientConnectionId before routing:**************
Routing Destination:*****************

Made the suggested changes and now am getting

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'U'.

Unclosed quotation mark after the character string ')DROP TABLE [dbo]. [Compensation.csv] Create Table dbo.[Compensation.csv](["Associate ID"] NVARCHAR(max),["Position ID"] NVARCHAR(max),["Home Department Code [Employment Profile]"] NVARCHAR(max),["Annual Salary"] NVARCHAR(max), ["Regular Pay Rate Amount"] NVARCHAR(max),["Standard Hours"] NVARCHAR(max), ["Pay Grade Code"] NVARCHAR(max),["Pay Frequency"] NVARCHAR(max), ["Compensation Change Reason Code"] NVARCHAR(max),["Compensation Change Reason Description"] NVARCHAR(max))'.

Upvotes: 0

Views: 513

Answers (1)

John Wu
John Wu

Reputation: 52240

Take a look at this code:

query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

Imagine different values for line.

If it contains something like 

John Smith,Dick Brain,Harry Chest

Then you'll be fine.

But what if it contains

John Smith,Dick Brain,Harry O'Brian

See the problem? In this case, query will get populated with

VALUES('John Smith,Dick brain,Harry O'Brian')

As far as SQL is concerned, the quote ended with Harry O, because O is followed by a single quote. When it comes across the quote after Brian it finds an opening quote with no closing quote, hence the error.

You need to scrub the input for single quotes and escape them. In SQL server, you escape them by replacing them with double single quotes, like this:

line = line.Replace("'","''");
query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

Then query will get

VALUES('John Smith,Dick brain,Harry O''Brian')

which is valid SQL.

The other (better) option would be to use a stored procedure to insert the value, and bind the string to a parameter, rather than constructing a SQL statement on the fly. If you do it that way you won't have to escape anything.

Upvotes: 2

Related Questions