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:15ClientConnectionId 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))'.
