phone # field weirdness 2

[sqlcasey]

Help.

SQL Server 2005

I imported an Excel spreadsheet to a table using the Import tool.

One of the fields in the spreadsheet was phone number. No big deal.

I looked at the imported table, and the phone number field appears correctly, eg) 5055551234

However, I then had to copy the information in the import table to another table. However, in the target table, the phone number is now all messed up and appears as:

7.19549e+009

The import table phone number field is a varchar. However, in the initial import process, it creates the field as a float, which I then changed to a varchar. The target table phone field is also a varchar.

I have done this import process a zillion times before, and I have never seen this. In an attempt to fix the problem, I created a cursor to loop thru the import table (where the phone # field appears correctly) and update the target table. However, a print statement of the cursor's
phone variable shows it as 7.19549e+009.

That is, if I run a SELECT on the import table the phone # appears normal. If I select then print the phone field with a cursor, it shows up as 7.19549e+009.

This really doesn't make sense to me.

Any ideas???

Thanks

 

[harebrain]

Before you do your first import, open your spreadsheet in Excel. Select the column with the phone numbers in it, and (right-click) Format Cells... [choose] Text.

Now when you import, the datatype of the column should already be varchar, and you won't get any floating-point nonsense.

 

[Catadmin]

Actually, I've seen this in the Import Wizard a lot. If you don't tell it to transform your column to varchar, then it will and can change the datatype on you. Especially if it has the wrong datatype in the table to begin with.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

catadmin,

yes, that did it - for some reason i did not remember to map the field, i guess because i've never had to do this before on the phone field

 

[SQLSister]

This is one reason why we always convert any excel spreadsheet to a txt file before importing. Txt is just a more reliable way to avoid it converting text numbers to floating number and removing leading zeros as well as this kind of problem.

Questions about posting. See faq183-874

 

[Catadmin]

It really gets nasty when importing from an ACCESS db. Nvarchars and Floats are pretty much all I ever see when doing that, so I have to map each and every single field. The wizard even gets the Ints wrong sometimes.

Glad I could help. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"