Hi,

We are trying to use a Data Transform Task for inserting
records into the database from an Excel file. In the Data
Transformation task - Source Tab - Preview - Only some of
the records are getting displayed.

If the first record contains number for a column in the
Excel file then all the rows which has got alphabets for
that column are shown to contain NULL values in the
preview window and the DTS is also failing to execute as
expected.Its trying to insert NULL for those records for
that particular column.

Any suggestions / ideas.

Cheers,
Sanka

Does this article help

Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Sanka" <loonysan (AT) mailcity (DOT) com> wrote

Hi Allan,

Fantastic... It worked like a magic....
Thanks.

Cheers,
Sanka

We encountered some problem when the numbers present in
the Excel are huge. They are getting converted to
Exponential format.

So currently we are thinking about appending some TEXT to
all the columns in the Excel... so the DTS will not
encounter any Numbers and export properly.

We will then extract the additional TEXT string which we
had added from the relational database.

Now the problem is how do we intelligently add TEXT to the
different columns in the Excel File. We want to append the
TEXT only if the Data present in that Cell is not NULL /
Blank.

Any suggestions.

Cheers,
Sanka

We found a good solution....
The Fixes present in http://www.sqldts.com/default.aspx?
254 unfortunately didn't help us much.

Solution: We just selected the columns and changed the
format of the cell to General and the DTS Uploaded the
data without any problem

Cheers,
Sanka