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 Universit
y
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 t
o
> 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 fro
m
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

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

import data from Excel

Hi:

I imported data from Excel to a table on SQL Server throught Data Transformation Services.

All data with a under score(like 1_5, 7_5) were replaced by Nulls. Is there any comment

about that?

Thanks a lot!

Regards,

Kevin JinWhat data type did you have the column that 1_5 was going into?|||you can change the field in sql server table.
data will remain same|||Slynch:

Thank you for your response. I did use nvarchar.

Regards,

Kevin Jin|||Mateenmohd:

Thank you for your response. Could you please let me know the detail about your idea. I'm interested in that.

Regards,

Kevin Jin|||Importing from Excel is a pain in the neck As far as I can tell, Excel decides what type your data is and that's the final deal. In Excel, save it as a csv text file, then import the text file. I've tried importing from Excel a million different ways and it always deletes data due to some wacky reason. I knew a guy who knew a guy who was on the Excel team and basically, just export from Excel to text and forget about trying to use Excel. In my opinion, they tried to make Excel work like a database so accountants wouldn't have to use Access or learn anything about databases. Unfortunately, Excel's a rotten database.|||PDraigh:

Thank you for your suggestion.

Regards,

Kevin Jin

Import data from Active Directory

Is there a way to import data from Active Directory using Data
Transformation Service (or maybe using other way) to SQL Server? I want to
import their username, first/last name, title, phone, etc.. and schedule
the import every night.
Any suggestions?http://www.windowsitpro.com/Article/ArticleID/14328/14328.html
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Zean Smith" wrote:
> Is there a way to import data from Active Directory using Data
> Transformation Service (or maybe using other way) to SQL Server? I want to
> import their username, first/last name, title, phone, etc.. and schedule
> the import every night.
> Any suggestions?
>
>

Import Column Transformation

I have a SQL Server table that has a "Text" type column. RTF files are stored in this column. On a monthly basis, new or updated RTF files are dropped into a shared folder. I need to upload these into the SQL Server table. Reviewing some of the entries in this forum, I know I probably should be using the Import Column Transformation, but for the last week I can't get it to work successfully.

Another column in the table contains the unique file name(varchar). At runtime the user inputs the fully qualified path to the RTF files which I store in an SSIS variable. Within the package I use a Derived Column Transformation to concatenate these two elements and then pass them as input to the Import Column. I'm expecting the RTF file to come out as output.

How does the Import Column match up the file with the proper table row? Do I need to provide the looping mechanism or does the Import Column handle that?

I've been searching for a working example with no luck. Can someone provide or point to a good sample?

Thanks.

It sounds like you are close to having it working.

The Import Column transform doesn't handle looping itself... it will process multiple rows, though. Each row can specify a different file name. That file will be read and the contents placed in the output column for that row. So, if you have all the file names at once in a table, you can process them in one shot with the Import Column.

However, you may need to be using a foreach loop container in the control flow to get your file names one at a time. In that case, the data flow with your derived column and import column transforms would need to be within the loop as well.

Let me know if this helps.

Thanks
Mark

|||Thanks Mark, that got me further along. I got passed some of the errors I was gettting. I think I'm getting closer after realizing that I need to manually set the "FileDataColumnID". Still having problems with the looping though. Have you done or run across any working examples? I think that would really help. Tom