| Author |
Topic |
|
koln5
Starting Member
29 Posts |
 Posted - 2004-09-14 : 17:43:36
|
| I have a Excel 2000 column which looks like this:Column A23456234-672-56354899865When I create a DTS package to import this column, only the values without a hyphen get imported correctly..and a null value will show for the numbers that have a hyphen in it.I've set my datatype to varchar, float, nvarchar, text, etc in SQL Server 2000...but nothing seems to work. I have also changed the datatype in my excel spreadsheet to text, general, etc.I've tried so many combinations, I forget which ones I've tested...Anybody have an idea what I should try ?Thank you |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-09-14 : 23:07:07
|
| Hi kolnI created an excel sheet, copied in the values as above, created a basic DTS package (making sure I created a new table with the field as varchar(255)) and it worked OK for me...Try recreating the transform data task once last time, creating a new table and check that the field is set to varchar(255)Let me know how you go--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
koln5
Starting Member
29 Posts |
Posted - 2004-09-15 : 14:32:33
|
| Believe me, I've tried that 142 times, but I tried it one more time...same result, the numbers with the dashes do not get imported.What was your excel data type befor importing ?Im starting to believe that I have to change some of the Excel OLEDB default settings...just not sure how.Any ohter ideas ?thanks |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-09-15 : 19:02:15
|
| my excel data was text, general.OK you've got me interested. Here's some things I'd try:* Inserting a single quote (like we used to have to do in Old Excel) in each cell (should be able to do that with a macro)* Selecting the column and pasting into a blank Access table* Copying your excel column into Notepad and then try and import it as its own tableIs this an import you're going to have to do regularly or a one off?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-09-15 : 19:07:24
|
| Don't panic!!!A registry fix may need to be applied for an issue with the Jet Engine. Excel Regedit Fix [url]http://support.microsoft.com/?id=281517[/url]What are the odds that this works? I'll give 2 to 1!DavidM"Always pre-heat the oven" |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-09-15 : 19:10:43
|
| Wow Dave! Awesome...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
koln5
Starting Member
29 Posts |
Posted - 2004-09-17 : 11:36:19
|
| I'll try this, but it sounds a problem fix for data that is bigger then 255 characters...as you can see in my example data, I only have 3-9 charactersSo far I've only got SQL Server to work when inserting a ' infront of the numbers that dont have a hyphen, then export it to a .csv file, then import into SQL ServerI appreciate your time |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-09-21 : 19:13:29
|
| can you send me your excel data sheet (don't worry if you've solved this and moved on)--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
Importing heterogeneous data from Excel into SQL