Post reply

Numbers stored as text - Excel to SQL- DTS

  • 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

     

     

  • May 4, 2005 at 1:50 pm

    #556905

    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

  • May 4, 2005 at 2:41 pm

    #556911

    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

     

     

     

     

     

     

     

  • May 4, 2005 at 3:45 pm

    #556928

    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

  • May 4, 2005 at 3:55 pm

    #556931

    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!

  • May 4, 2005 at 11:19 pm

    #556984

    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

     

  • May 5, 2005 at 6:41 am

    #557031

    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.

  • May 5, 2005 at 8:48 am

    #557088

    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

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

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