Data Conversion problem.

Question

Data Conversion problem.

pvong

SSC Eights!

Points: 832
More actions
April 18, 2016 at 12:47 pm

#326571

SQL 2014
I have an Excel file where column 3 (F3) will have all decimal values but the ones that are missing will come over as (?) and I want to replace all the ? with a 0 value. So I am doing a Derived Column to a Data Conversion back to Decimal values so I can import it into SQL where that column is set up as Decimal type with 2 decimal precision.
Here is my Derived Column. This works just fine because if I test it into a String Type column, I see that it did replace all the ? with the 0 value.
Here is my Data Conversion. Looks pretty straight forward.
As you can see, it bombs out during the conversion.
Here is the error message.
What am I doing wrong? Not every Excel file will have the ? values. Those without the ? will convert to decimal just fine and imports directly to sql decimal type column without any problems.
I was thinking maybe because some of the values are blank, but that can not be the case either because every excel file has values that are blank and I don't have any problems with the converstion + import. It's only the ones with the ? characters.
Any help would be greatly appreciated.
Thanks!
Phil

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

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

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

Have you tried putting a number in the Precision column to represent the maximum, err, precision of the incoming data?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pvong SSC Eights! Points: 832 More actions · Answer 2

Phil - I'm sorta new so thanks for your help.

In both the derived col and conversion, I can not change the value in the Precision box. For the conversion, I can only change the Scale.

Phil Parkin SSC Guru Points: 246961 More actions · Answer 3

pvong (4/18/2016)
Phil - I'm sorta new so thanks for your help.
In both the derived col and conversion, I can not change the value in the Precision box. For the conversion, I can only change the Scale.

No problem. Should work. I suspect that your problem may be with the numbers rather than the question marks. Are there any which are very large or which have a lot of numbers after the decimal point?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

Further on that, you could just configure the error output of the conversion task to redirect the rows on error. Just send that to a flat file or destination of your choosing and see what rows get redirected there.

Cheers!

pvong SSC Eights! Points: 832 More actions · Answer 5

Phil, thanks for your help.

Jacob, I marked you as the answer because I learned how to do an error output file thanks to you suggesting it. Remember, I'm a newbie.

Anyway, I found that it wasn't have a problem replacing ? to 0 or converting it to decimal type. It had to do with the fact that i forgot to make sure Excel skipped the first 3 rows because the 2nd row had all ---------- and you can not convert that to decimal. The error output file told me that so I learned something new.

Learned to skip X rows from here:

Thanks again for all you help guys!!

Phil Parkin SSC Guru Points: 246961 More actions · Answer 6

pvong (4/19/2016)
Phil, thanks for your help.
Jacob, I marked you as the answer because I learned how to do an error output file thanks to you suggesting it. Remember, I'm a newbie.
Anyway, I found that it wasn't have a problem replacing ? to 0 or converting it to decimal type. It had to do with the fact that i forgot to make sure Excel skipped the first 3 rows because the 2nd row had all ---------- and you can not convert that to decimal. The error output file told me that so I learned something new.
Learned to skip X rows from here:
Thanks again for all you help guys!!

Well done on figuring it all out. I wish that others posting on these forums had the same initiative.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

Phil Parkin (4/19/2016)
pvong (4/19/2016)
Phil, thanks for your help.
Jacob, I marked you as the answer because I learned how to do an error output file thanks to you suggesting it. Remember, I'm a newbie.
Anyway, I found that it wasn't have a problem replacing ? to 0 or converting it to decimal type. It had to do with the fact that i forgot to make sure Excel skipped the first 3 rows because the 2nd row had all ---------- and you can not convert that to decimal. The error output file told me that so I learned something new.
Learned to skip X rows from here:
Thanks again for all you help guys!!
Well done on figuring it all out. I wish that others posting on these forums had the same initiative.

+1

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Bark Stuff SSC Enthusiast Points: 120 More actions · Answer 8

Whilst it looks good to take a Excel file and convert it into he final product in a single dataflow you may find it much easier, quicker and better to simply read it as is into a temporary table and then process it in a second step with the appropriate tool - SQL or c#.