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?

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

spgilmore22y ago
When you specify the CHAR DATAFILETYPE, it expects no quoting. In this case, the field types in the database are irrelevant. I suggest removing the quotes and changing the fieldterminator to "\". Otherwise, you're inserting values like this "2908 into an integer field. It is obviously not an integer. The last value would be 0" which is also not an integer, and will probably also fail. If you cannot easily remove the quotes, consider using a format file that you create with BCP.EXE. See BCP in the SQL server books online for more information. Specify the format file with the FORMATFILE parameter.