import data from Excel failed?

Hi,
I needed to import data from an Excel file to a table. The error I got was:
Error during Transformation 'DirectCopyXform' for Row number 4. Errors
encountered so far in this task: 1. TransformCopy
'DirectCopyXform' conversion error: Destination does not allow NULL on
column pair 1 (source column 'University ID' (DBTYPE_WSTR),
destination column 'universityid' (DBTYPE_STR)).
The University ID is of txt type in Excel. The universityid in SQL is of
varchar(9) and cannot by NULL.
Here is what I did. In EM, I selected All Tasks->Import Data..., then
selected Microsoft Excel 97-2000 as data source and MS SQL server as
destination. But when I clicked Preview to see how data in Excel was mapped
to the table, I noticed the universityid column is empty for some entries.
But their University ID cell is not empty in Excel. They just don't have
that tiny green arrow at the left upper coner of the cell as other University
IDs that are mapped fine in the import data preview. What might be the
problem and how should I fix?
Thanks in advance for any help.
BingI don't know Why it happens. It happens to me all the time. But you can
work around it.
Save the file as "Text (Tab delimited)" in execl then load the text file to
SQL server.
Regards,
Saravanan
"bing" <bing@.discussions.microsoft.com> wrote in message
news:BBBB27AB-8F68-45C6-812F-30386273CB44@.microsoft.com...
> Hi,
> I needed to import data from an Excel file to a table. The error I got
> was:
> Error during Transformation 'DirectCopyXform' for Row number 4. Errors
> encountered so far in this task: 1. TransformCopy
> 'DirectCopyXform' conversion error: Destination does not allow NULL on
> column pair 1 (source column 'University ID' (DBTYPE_WSTR),
> destination column 'universityid' (DBTYPE_STR)).
> The University ID is of txt type in Excel. The universityid in SQL is of
> varchar(9) and cannot by NULL.
> Here is what I did. In EM, I selected All Tasks->Import Data..., then
> selected Microsoft Excel 97-2000 as data source and MS SQL server as
> destination. But when I clicked Preview to see how data in Excel was
> mapped
> to the table, I noticed the universityid column is empty for some
> entries.
> But their University ID cell is not empty in Excel. They just don't have
> that tiny green arrow at the left upper coner of the cell as other
> University
> IDs that are mapped fine in the import data preview. What might be the
> problem and how should I fix?
> Thanks in advance for any help.
> Bing|||"Saravanan Palanivelu" wrote:
> I don't know Why it happens. It happens to me all the time. But you can
> work around it.
> Save the file as "Text (Tab delimited)" in execl then load the text file to
> SQL server.
>
Thanks very much for the quick response and most import the tip, Saravanan.
It works great. The only inconvenience is the wizard for importing data from
text does not provide a way to skip, say, the last <number> of lines. For
some reason, the last several lines in my input text file have all 'NULL's
which I want them to be skipped. It can only skip if you know the line
number first.
Bing