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 |
|
aniko
Starting Member
25 Posts |
 Posted - 2010-12-16 : 04:27:35
|
| Hi guys,I have a CSV file that I'm trying to import into a table. It contains a column containing values anywhere from 0 to 100.I need to import this as an integer so I can perform some calculations on the column. For example, I would like to bring back all records that are less than say 80.I can't seem to do this using a varchar. Any suggestions on how to achieve this?This is rather urgent unfortunately! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-12-16 : 04:32:43
|
If there are really always values from 0 to 100 and not any character data then you set that column to an numeric datatype while creating the import.Another way if you are not able to setup your import the right way is to do this:select * from YourImportTable where convert(int,YourColumn) = 80
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
aniko
Starting Member
25 Posts |
Posted - 2010-12-16 : 04:46:27
|
| I think I may have padding on each side of the column. How would I use Ltrim and Rtrim to remove the padding, export, then reimport using Int?Unfortunately I've tried converting to Int during import and get data conversion errors. Although, there is not alpha characters!Thoughts? |
|
|
|
aniko
Starting Member
25 Posts |
Posted - 2010-12-16 : 05:04:56
|
| I'll try an export using ltrim and rtrim, then reimport. |
|
|
|
aniko
Starting Member
25 Posts |
Posted - 2010-12-16 : 05:12:35
|
| Still not luck. I get this error.Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "coln1" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". |
|
|
|
yavvie
Starting Member
46 Posts |
Posted - 2010-12-17 : 11:01:49
|
| you can try importing into varchar column and then running the command isnumeric (column) to check if SQL is able to interpret your values as numbersthe error you posted means that you are trying to insert a larger value than the field is able to acceptof course it is always wise to add error files to your import dtsx and send all failing rows there, then you can see if the problem is specific to some rows or to the whole import |
|
|
|
|
|
|
Import conversion problem