Bulk insert using text file
Ok, I have the following line I have loosely ported from MySQL to MS SQL:
ExecuteNonQuery( "LOAD DATA {0}INFILE '{1}' INTO TABLE myrunuo_characters FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'", Config.DatabaseNonLocal ? "LOCAL " : "", m_MobilesPath );
ExecuteNonQuery( "bulk insert MyRUNUO..myrunuo_characters from 'C:/RunUO 1.0 RC0/myrunuodb_mobiles.txt' with (datafiletype = 'char' , fieldterminator = '\",\"' , rowterminator = '\n', maxerrors = 50 , tablock)");
"2908","Mysterion","35","35","10","1","0","NULL","NULL","Mysterion","33770","0"
"2453","Mugetsue","36","28","17","0","0","NULL","NULL","Mugetsue","33770","0"
"2679","Paulin","35","11","34","0","0","NULL","NULL","Paulin","33770","0"
MyRunUO: Exception caught in database thread
System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver]
[SQL Server]Bulk insert data conversion error (type mismatch) for row 1, column 1 (char_id).
ERROR [42000] [Microsoft][ODBC SQL Server Driver]
[SQL Server]Bulk insert data conversion error (type mismatch) for row 2, column 1 (char_id).
Now the problem I have is that I am getting conversion error (type mismatch) for a field in column 1 which is apparently an integer. Now with the bulk insert command, you specify the datafiletype for the whole file, right? Since mine is set to character, how can I make this work? I did not create the code to add the quotes around the data and I am very new to C#
Anyone have ideas how I can import the whole text file that is in this current format to SQL?