x

importing True/False values to nvarchar datatype

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:

not signed
true
signed
false  

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!
more ▼

asked Nov 02, 2010 at 12:50 PM in Default

FLSchmidtK gravatar image

FLSchmidtK
31 3 3 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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
more ▼

answered Nov 02, 2010 at 03:52 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x729
x115
x66

asked: Nov 02, 2010 at 12:50 PM

Seen: 1592 times

Last Updated: Nov 03, 2010 at 05:19 AM