InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 18:16:45 GMT20http://www.sqlservercentral.com/Forums/Topic1423393-1292-1.aspxFrom your BULK INSERT statement, you're not actually importing from an Excel file, but from a CSV file.This is an important distinction because it's been my experience that you have a much better shot at getting your input file standardized when using CSV. Excel interprets lots of values such as leading zeros, dates, etc. that make it virtually impossible to get things standardized. If this is going to be a repeated process that runs on a server, Excel can also have VBA code associated with it and you probably don't want to take a chance by running it on your server, especially if it comes from a third party.I've found that for repeated loads from consistent data files, using BULK INSERT with a format file is very reliable. It's a bit of work to set up the XML format file, but it is ultra-fast and very consistent. [url]http://msdn.microsoft.com/en-us/library/ms178129.aspx[/url] A point to consider is that both the file with the source data and the format file need to be on the server itself.HTHMon, 25 Feb 2013 12:54:39 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1423393-1292-1.aspxYou'll need to standardize the input source before even trying the BULK INSERT.SQL Server is a great tool but it cannot "guess" what the final result should look like from your personal perspective.You need to provide a field terminator that is not part of the values you're trying to insert.Maybe you could use a pipe separator or something like this.Sun, 24 Feb 2013 03:13:51 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1423393-1292-1.aspxHi AllI am trying to do a large import with the below code for many thousands of rows from an excel spreadsheet.the table is made up as followscol1, col2, col3 col4, col5sun microsystems inc, test,test,test,testsun microsystems, inc, test, test, test, testadobe inc, test,test,test,testmicrosoft,test,test,test,testmicrosoft",test,test,test,test"adobe, inc",test,test,,testI am having problems for example with rows 2, 5 and 6all rows are inserting but for instance row 2 is finding the comma after sun microsystems, then putting inc in the next column alongsame for row 6 with adobe onesrow 5 is still inserting which is similar two the other rows but i think it is doing the same because of the the double quotesalso the last row is showing no value so is there anyway to put some wording into the row if it is empty say something like 'Empty Value'here is the import I am usingBULKINSERT The_Big_KahunaFROM 'c:\users\alynch\Desktop\The_Big_Kahuna.csv'WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '')GOSELECT *FROM dbo.The_Big_KahunaGOthanksSat, 23 Feb 2013 22:59:39 GMTalan_lynch