Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
THNQdigital
Starting Member
15 Posts |
 Posted - 2005-05-04 : 14:47:25
|
| 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.. ThanksTHNQdigital |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-05-04 : 15:38:31
|
| Is that column blank for the first dozen, or so, rows in the XLS table?DTS tends to base its conversion on the data in the first few rows in the XLS. If that's the case you could try putting a dummy row early on [in the XLS] to influence the data conversion.Kristen |
 |
|
|
THNQdigital
Starting Member
15 Posts |
Posted - 2005-05-04 : 16:21:44
|
| Hi Kristen,The column in the first few rows has values like 1899, 9877, 7777 etc and then it starts with 0010, 0012 and so on.. i refred to thishttp://support.microsoft.com/kb/236605/EN-US/I tried format entire column to text an dprepfix all the values in the column with Zero's and then the DTS is able to detect all the values..So as you have pointe dout it looks at first 8 rows or something to determine the data type for rest of the rows.. Point is i can make it work by altering the columns values like i said before.. but i am not supposed to do that as those are site names and they need to be like as they come in to us.. Like a site 100 is different than site 0100.. etc..So ia m just wondering if this is a limitation or still there is a way out..or i will have to conver the .xls to a text file and load. ( i have a constraint here too as our clients standards is to use only excel as the input soource file..) .So.. just don't know at this point in time.. please let me know if you have anything in your mind.. thanks for your time..ThanksTHNQdigital |
|
|
|
THNQdigital
Starting Member
15 Posts |
Posted - 2005-05-05 : 10:37:51
|
| Hi just an update..I tried followingmake an excel sheet with below valuesServer1.xlsSerialnumber Sitename1234 0010 ( formatted as text)1235 00201236 00301237 30011238 3002 ( formatted as general)1239 0040 now only site names starting with 00 are loaded.then i tried the alternative like belowServer2.xlsSerialnumber Sitename1234 3001 ( formatted as general)1235 30021236 30061237 0010 (formatted as text)1238 00201239 3005now only numbers not prefixed with zeros ( 3001, 3002 etc) are loaded.i tried 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 recognize 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 timeTHNQdigital |
|
|
|
|
|
|
|
|
Numbers stored as text - Excel to SQL using DTS