Import excel with leading empty cells removes commas

user_13673240 0
Viewed 100

I have a simple excel-file with some data. Some specific columns can be NULL, 0 or have the length of numeric(10,5) like the following format as example: 12345.12345.

The output column from the excel-source is configured as SSIS-type DT_R8 (double-precision floating-point). The output column from the excel-source is configured as SSIS-type DT_R8 (double-precision floating-point). Click here for picture.

This works fine. Some further files got the same format but sometimes the leading rows after the headline are empty the leading rows after the headline are empty. Click here for picture.

In this case SSIS will import the following rows without commas. They are just removed. 12345.12345 will be 1234512345.

So I changed the excel-output column to SSIS-type DT_WSTR(50) (this is unicode-text) excel-output column to SSIS-type DT_WSTR(50) (this is unicode-text) and implemented a conversion-task to DT_R8 again (double precission) conversion-task to DT_R8 again (double precission). This works fine for the file with leading empty cells!

But if I import another file without leading empty rows - I will get huge results:

enter image description here 9,98994 is converted to 9,9894E+16. Another worse example (not shown in this picture): 408,452 will be converted to 408452.

So either I import excel with type DT_R8 (float) and lose commas when importing empty leadings cells or I import excel with type DT_WSTR (text) with conversion-task and get huge numbers when importing without empty leading cells.

I don't know what bug this is and how to fix it. I tried a lot of data-conversion and tried many data-types. I can't change the excel-file by itself because it is generated automatically and I can not import with a WHERE COLUMNNAME IS NOT NULL-condition, because the other columns contains also important data.

EDIT: Additional infos for the file with leading empty cells: this fileformat is somehow weird what leads to the bug. The first values with 0 are in row 65: enter image description here

Float-values just begins with line 1061: enter image description here.

I think that is just too late and SSIS interpret this column as integer-value with no commas.

etl ssis excel
1 Answers

billinkc's tips were right! I wouldn't try it without his guesses, but the problem was somehow the country. In the conversion-task I had to replace dots with commas from the input-column:

ISNULL(anspr_vk) || LEN(TRIM(anspr_vk)) == 0 ? "0,0" : REPLACE(anspr_vk,".",",")

enter image description here

This took me some time - thank you really much for let me finding the right way, billinkc!

user_136732400
Related