Numbers stored as text - Excel to SQL- DTS

Question

Numbers stored as text - Excel to SQL- DTS

THNQdigital

SSC Eights!

Points: 849
More actions
May 4, 2005 at 1:00 pm

#60561

HI
I have a column sitename in an excel sheet which i need to load to a table in SQL 2K.
sitename's can be 0010, 0007 etc.
I tried to load the values to a table using DTS, but these numbers are not loaded ( not visvible even in preview, Excel connection ->Transform Data Task - > SQL Connection )
I tried to format the cell as , general, Text, etc nothing would work.
There is one more column as Building with values like 01 , 02 etc.. whose cell's are formated as General ( actually all cels are formated as general) and that get's loaded, but somehow sitename does not get loaded.. i am wondering what am i missing here..
Please let me know.. Thanks
THNQdigital

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Are all the sitenames being populated? I seem to remember having a similar problem where not all cells in column were populated and 0 data was loaded along with IF 1st cell was numeric then character data would not load...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

THNQdigital SSC Eights! Points: 849 More actions · Answer 2

Hi AJ,

Yes, all the sitename are populated.. I actually figured out the problem.. but not sure how would i be able to overcome this one..

here is what is happenning..

as per http://support.microsoft.com/kb/236605/EN-US/

the first 8 rows in Excel determine the data type for rest of the rows in the sheet. IN my case first few rows have values for site name as 1899, 9888, 7777 and so on.. and then the values are like 0010, 0030 and so on..

so only values which are not prefixed with 00 are loaded.. for the values with zero it says ( inside excel if you see error mesage number stored as text )

if i convert all the values to be prefixed iwth zeros, it does work well.. but i am not supposed to this as site names need to be same as they come in .. like 100 is diffeernt site than 00100

So.. not sure how to fix this.. excpt that i could convert xls to .txt and then do DTS..

please let me know if there is any other workaround

Thanks

THNQdigital

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

ok. IF all the cells are populated and the NON leading zero ones are loaded. I wonder what would happen if you sorted by site number and then loaded...... i.e. the 00 sites are at the top

Good Hunting!

AJ Ahrens

webmaster@kritter.net

aMSDeveloper Right there with Babe Points: 718 More actions · Answer 4

This seems to be a common occurence for me. I think this should fix it.

Open spreadsheet, choose that column sitename (entire column, by clicking on the column name, this will select the data in the entire column)

Go to Data on the menu and choose the option 'Text to columns'. You get a wizard pop up. Select the delimiter to be tab, and select the data to be general (this is important). After you finish and exit the wizard, all your data is now in one format and that is 'General'. Save the spreadsheet.

Try importing the data now and see if this helps.

Good Luck!

Dan Dougherty SSC Enthusiast Points: 155 More actions · Answer 5

Perhaps you could have Excel concatanate a leading letter to your cells with these special numbers and put them in a separate column. (Ex. formula ="A" & C2 generates A0010, or A1899, or A0007, etc)

And then have DTS strip off the leading character using DTS as it imports the column now detected as text.

-Dan

RandyB SSCommitted Points: 1565 More actions · Answer 6

I just experienced a somewhat similar problem yesterday importing Excel into a SQL 2000 table. The SSNs that didn't have hyphens loaded properly but the the ones with hyphens (123-45-6729) came in as NULL. I tried formatting the cells to text and nothing helped.

Finally, I did a File, Save As to .txt format and everything loaded fine.

THNQdigital SSC Eights! Points: 849 More actions · Answer 7

Hi Guys,

I tried following

make an excel sheet with below values

Server1.xls

Serialnumber Sitename

1234 0010 ( formatted as text)

1235 0020

1236 0030

1237 3001

1238 3002 ( formatted as general)

1239 0040

now only site names starting with 00 are loaded.

then i tried the alternative like below

Server2.xls

Serialnumber Sitename

1234 3001 ( formatted as general)

1235 3002

1236 3006

1237 0010 (formatted as text)

1238 0020

1239 3005

now only numbers not prefixed with zeros ( 3001, 3002 etc) are loaded.

I treid to format the entire column as Balaji advised and found that this converts the site name 0010 to 10 which defeats my requirement..

and then tried AJ's advise to sort by site name, that would again like load any one of the types ( either 0010 or 3001) .. Saving to a .txt file works but that's not my requirement.. Concatinating with a Alphabet may work but how am i to concatinate only those columns and when file is big it may be tedious..

however i figured out one thing.. if i format entire column to text both 0010 amd 3001 types as text DTS works fine.. this is a good sign forme.. but right now.. if i change the format of the sitename in excel sheet that was already once used buy DTS, it does not recoznise the change, however if i make a fresh excel and try to load it works fine..

So, i was wondering if you guys know if DTS remembers anything previously loaded.. liek cache or something.. please let me know.. that would be of great help..

Thanks for your time

THNQdigital