I'm trying to figure out what happened with an import from excel to sql 2005. I have an excel spreadsheet with typical data such as name, address, city, state, and zip but I have one extra column with a combination of text and true/false values. So the extra column looks something like the following:
So you can see the combination of values when I did the import. I used the import wizard in MS SQL 2005 and used nvarchar(255) as the data type for all fields. For my extra column, it imported all the text except the true/false values where it actually but a NULL value in place of the correct true/false values. I cannot find anything that would cause this or any reason. Any advice would be very helpful for future imports.Thanks!
Excel examines first 10 rows in the spreadsheet to determine the actual data types, which occasionally may lead it to make wrong assumptions. One of the ways to fix the problem is to open Excel, select the column in question, right-click and choose "Format Cells..." from the popup menu. Under Number tab, select Text as Category. After this is done, please check how the words true and false are spelled in the cells of that column. If they are still upper-cased and center-aligned then it is a problem, change all occurences of the word TRUE to true and all occurences of the word FALSE to false. This should automatically change the alignment of the affected cells to left thus giving you a clue that Excel finally understands that the words true and false in the cells are actually text values.
After this is done, you can then import the data.Oleg
answered Nov 02, 2010 at 03:52 PM