Greatings!
I have a problem with importing an Excel-document (xlsx) to a database using SSIS and an ACE-provider. Some values are imported as "Null" instead of their real value.
Google quickly reveals that this is a common issue and the cause is Excel's inavability to differentiate between text and value columns.
However - the regular solution of using the "IMEX=1" property of the data-provider changes nothing and I am pretty sure that my problem is of a completely different nature.
You see, if I open the document (using office 2013) and save it again - it works perfectly!
This makes me think that the document must be corrupted somehow, and that open&safe fixes this issue.
The Question is, what may the problem be with the original document?
This document is created by in another country by people with little knowledge of excel. I do unfortunately not know the details behind how they create this document but I need to tell then what the problems are so they can fix it.
Some details:
The problem is not regarded to certain "columns", but rather to certain rows. Either all numbers in all columns of a specific row are imported as Null, or they all are imported as expected.
I cannot detect any difference at all when comparing one row that behaves normally with one row with nulls in int. They look exactly the same and the have the same formatting.
All help is greatly appreciated!
I have a problem with importing an Excel-document (xlsx) to a database using SSIS and an ACE-provider. Some values are imported as "Null" instead of their real value.
Google quickly reveals that this is a common issue and the cause is Excel's inavability to differentiate between text and value columns.
However - the regular solution of using the "IMEX=1" property of the data-provider changes nothing and I am pretty sure that my problem is of a completely different nature.
You see, if I open the document (using office 2013) and save it again - it works perfectly!
This makes me think that the document must be corrupted somehow, and that open&safe fixes this issue.
The Question is, what may the problem be with the original document?
This document is created by in another country by people with little knowledge of excel. I do unfortunately not know the details behind how they create this document but I need to tell then what the problems are so they can fix it.
Some details:
The problem is not regarded to certain "columns", but rather to certain rows. Either all numbers in all columns of a specific row are imported as Null, or they all are imported as expected.
I cannot detect any difference at all when comparing one row that behaves normally with one row with nulls in int. They look exactly the same and the have the same formatting.
All help is greatly appreciated!
