Import from Excel with IMEX=1 still gives probs

Hi:

Am trying to import XLS data into SQL 2005 SP2 thro a SSIS Data Flow task. My Excel Connection string has IMEX=1, ImportMixedTypes is set to Text and the typeguessrows is set to 0.

Import works fine for cells of Format Text, but when I have a large number (in a general Format cell) it gets converted into scientific notation(e.g. 3.234175e+7) in the table.

What am I doing wrong?

TIA

Kar

What's the type of the column in the data flow?|||

The column is of type DT_WSTR(255) in the data flow. There's no prob with cells that are textual, or are numeric with Text Format. Am only having a prob with cells with General Format I think. These cells look normal in excel, but when I import to Ole DB, I get Scientific Notation.

I have tried to save Excel as Text, and that works, but that will introduce unnecessary additional layers to build, test and maintain. Hope to find a solution within the box.

TIA

Kar

import from Excel file

I have some difficulties with import xls file to sql server. The problem
is I try to import excel-html file but not ordinary excel-binary file.
I use to use query like that :
select 1,2,3,4,5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;
DATABASE=c:\zeszyt1.xls;HDR=YES', 'Select * from [a$]')
but it works only with binary files. With html files in return a message:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: An External table has unexpected format.]
So , how to import excel-html files ?
thanks inadvance
K.you can't because it doesnot have.xls extension.
convert back to .xls and use it
Regards
R.D
--Knowledge gets doubled when shared
"krzys[wawa]" wrote:

> I have some difficulties with import xls file to sql server. The problem
> is I try to import excel-html file but not ordinary excel-binary file.
> I use to use query like that :
>
> select 1,2,3,4,5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;
> DATABASE=c:\zeszyt1.xls;HDR=YES', 'Select * from [a$]')
> but it works only with binary files. With html files in return a message:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: An External table has unexpected format
.]
>
> So , how to import excel-html files ?
> thanks inadvance
> K.
>|||R.D napisa?(a):
> you can't because it doesnot have.xls extension.
> convert back to .xls and use it
>
no no, this file looks like ordinary excel file. Has an extension xls
and also xls icon.|||If my guess is correct, you have converted excel sheet to webpage. then it i
s
.htm page though a small X icon is there. Right click and check once again
the extension.
Is must have .htm extension
Regards
R.D
--
Regards
R.D
--Knowledge gets doubled when shared
"krzys[wawa]" wrote:

> R.D napisa?(a):
>
> no no, this file looks like ordinary excel file. Has an extension xls
> and also xls icon.
>|||R.D napisa?(a):
> If my guess is correct, you have converted excel sheet to webpage. then it
is
> .htm page though a small X icon is there. Right click and check once again
> the extension.
> Is must have .htm extension
> Regards
> R.D
well, in fact this file has a structure of html file (or very similiar).
This file was generated by web application and it has extension xls -
believe me I check it in text mode :)|||Now you can open it in excel and save as .xls file and you should be able to
import.
Regards
R.D
--Knowledge gets doubled when shared
"krzys[wawa]" wrote:

> R.D napisa?(a):
>
> well, in fact this file has a structure of html file (or very similiar).
> This file was generated by web application and it has extension xls -
> believe me I check it in text mode :)
>

Import fails on text field - help

Hi,
I am trying to import an xls file into SQL Server 2005. It usually
works fine, except for when I try to import the "Notes" column. The
notes column is just a really long text field. The table I'm attempting
to import to has Notes defined as a varchar(4000) - I changed it from
the default 255, which is more than sufficient.
I don't recall having this problem in 2000. So how I can get this to
work?
Thanks!!
Error message:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column
"Notes" (78) on output "Excel Source Output" (9). The column status
returned was: "Text was truncated or one or more characters had no
match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "Notes" (78)"
failed because truncation occurred, and the truncation row disposition
on "output column "Notes" (78)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified
component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - HCMembers_Load$" (1) returned error code 0xC020902A. The
component returned a failure code when the pipeline engine called
PrimeOutput(). The meaning of the failure code is defined by the
component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
shutdown signal and is terminating. The user requested a shutdown, or
an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
error code 0xC0047039.
(SQL Server Import and Export Wizard)
Hi
Have you tried varchar(MAX) or nvarchar(MAX) instead?
John
"tootsuite@.gmail.com" wrote:

> Hi,
> I am trying to import an xls file into SQL Server 2005. It usually
> works fine, except for when I try to import the "Notes" column. The
> notes column is just a really long text field. The table I'm attempting
> to import to has Notes defined as a varchar(4000) - I changed it from
> the default 255, which is more than sufficient.
> I don't recall having this problem in 2000. So how I can get this to
> work?
> Thanks!!
> Error message:
> - Executing (Error)
> Messages
> Error 0xc020901c: Data Flow Task: There was an error with output column
> "Notes" (78) on output "Excel Source Output" (9). The column status
> returned was: "Text was truncated or one or more characters had no
> match in the target code page.".
> (SQL Server Import and Export Wizard)
> Error 0xc020902a: Data Flow Task: The "output column "Notes" (78)"
> failed because truncation occurred, and the truncation row disposition
> on "output column "Notes" (78)" specifies failure on truncation. A
> truncation error occurred on the specified object of the specified
> component.
> (SQL Server Import and Export Wizard)
> Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
> "Source - HCMembers_Load$" (1) returned error code 0xC020902A. The
> component returned a failure code when the pipeline engine called
> PrimeOutput(). The meaning of the failure code is defined by the
> component, but the error is fatal and the pipeline stopped executing.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
> with error code 0xC0047038.
> (SQL Server Import and Export Wizard)
> Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
> shutdown signal and is terminating. The user requested a shutdown, or
> an error in another thread is causing the pipeline to shutdown.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
> error code 0xC0047039.
> (SQL Server Import and Export Wizard)
>

Import fails on text field - help

Hi,
I am trying to import an xls file into SQL Server 2005. It usually
works fine, except for when I try to import the "Notes" column. The
notes column is just a really long text field. The table I'm attempting
to import to has Notes defined as a varchar(4000) - I changed it from
the default 255, which is more than sufficient.
I don't recall having this problem in 2000. So how I can get this to
work'
Thanks!!
Error message:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column
"Notes" (78) on output "Excel Source Output" (9). The column status
returned was: "Text was truncated or one or more characters had no
match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "Notes" (78)"
failed because truncation occurred, and the truncation row disposition
on "output column "Notes" (78)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified
component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - HCMembers_Load$" (1) returned error code 0xC020902A. The
component returned a failure code when the pipeline engine called
PrimeOutput(). The meaning of the failure code is defined by the
component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
shutdown signal and is terminating. The user requested a shutdown, or
an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
error code 0xC0047039.
(SQL Server Import and Export Wizard)Hi
Have you tried varchar(MAX) or nvarchar(MAX) instead?
John
"tootsuite@.gmail.com" wrote:
> Hi,
> I am trying to import an xls file into SQL Server 2005. It usually
> works fine, except for when I try to import the "Notes" column. The
> notes column is just a really long text field. The table I'm attempting
> to import to has Notes defined as a varchar(4000) - I changed it from
> the default 255, which is more than sufficient.
> I don't recall having this problem in 2000. So how I can get this to
> work'
> Thanks!!
> Error message:
> - Executing (Error)
> Messages
> Error 0xc020901c: Data Flow Task: There was an error with output column
> "Notes" (78) on output "Excel Source Output" (9). The column status
> returned was: "Text was truncated or one or more characters had no
> match in the target code page.".
> (SQL Server Import and Export Wizard)
> Error 0xc020902a: Data Flow Task: The "output column "Notes" (78)"
> failed because truncation occurred, and the truncation row disposition
> on "output column "Notes" (78)" specifies failure on truncation. A
> truncation error occurred on the specified object of the specified
> component.
> (SQL Server Import and Export Wizard)
> Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
> "Source - HCMembers_Load$" (1) returned error code 0xC020902A. The
> component returned a failure code when the pipeline engine called
> PrimeOutput(). The meaning of the failure code is defined by the
> component, but the error is fatal and the pipeline stopped executing.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
> with error code 0xC0047038.
> (SQL Server Import and Export Wizard)
> Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
> shutdown signal and is terminating. The user requested a shutdown, or
> an error in another thread is causing the pipeline to shutdown.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
> error code 0xC0047039.
> (SQL Server Import and Export Wizard)
>sql

Import fails on text field - help

Hi,
I am trying to import an xls file into SQL Server 2005. It usually
works fine, except for when I try to import the "Notes" column. The
notes column is just a really long text field. The table I'm attempting
to import to has Notes defined as a varchar(4000) - I changed it from
the default 255, which is more than sufficient.
I don't recall having this problem in 2000. So how I can get this to
work'
Thanks!!
Error message:
- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column
"Notes" (78) on output "Excel Source Output" (9). The column status
returned was: "Text was truncated or one or more characters had no
match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "Notes" (78)"
failed because truncation occurred, and the truncation row disposition
on "output column "Notes" (78)" specifies failure on truncation. A
truncation error occurred on the specified object of the specified
component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
"Source - HCMembers_Load$" (1) returned error code 0xC020902A. The
component returned a failure code when the pipeline engine called
PrimeOutput(). The meaning of the failure code is defined by the
component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
shutdown signal and is terminating. The user requested a shutdown, or
an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
error code 0xC0047039.
(SQL Server Import and Export Wizard)Hi
Have you tried varchar(MAX) or nvarchar(MAX) instead?
John
"tootsuite@.gmail.com" wrote:

> Hi,
> I am trying to import an xls file into SQL Server 2005. It usually
> works fine, except for when I try to import the "Notes" column. The
> notes column is just a really long text field. The table I'm attempting
> to import to has Notes defined as a varchar(4000) - I changed it from
> the default 255, which is more than sufficient.
> I don't recall having this problem in 2000. So how I can get this to
> work'
> Thanks!!
> Error message:
> - Executing (Error)
> Messages
> Error 0xc020901c: Data Flow Task: There was an error with output column
> "Notes" (78) on output "Excel Source Output" (9). The column status
> returned was: "Text was truncated or one or more characters had no
> match in the target code page.".
> (SQL Server Import and Export Wizard)
> Error 0xc020902a: Data Flow Task: The "output column "Notes" (78)"
> failed because truncation occurred, and the truncation row disposition
> on "output column "Notes" (78)" specifies failure on truncation. A
> truncation error occurred on the specified object of the specified
> component.
> (SQL Server Import and Export Wizard)
> Error 0xc0047038: Data Flow Task: The PrimeOutput method on component
> "Source - HCMembers_Load$" (1) returned error code 0xC020902A. The
> component returned a failure code when the pipeline engine called
> PrimeOutput(). The meaning of the failure code is defined by the
> component, but the error is fatal and the pipeline stopped executing.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited
> with error code 0xC0047038.
> (SQL Server Import and Export Wizard)
> Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a
> shutdown signal and is terminating. The user requested a shutdown, or
> an error in another thread is causing the pipeline to shutdown.
> (SQL Server Import and Export Wizard)
> Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with
> error code 0xC0047039.
> (SQL Server Import and Export Wizard)
>

Import cell data from XLS into SQL table

I'm trying to use DTS to import data from an XLS into a SQL table.

It works fine in that it INSERT's the data. However, I need it to
UPDATE the table, based upon a ProjectID. Can this be done?

Can a DTS package be fired from a SP using parameters?
Eg UPDATE tProjects SET MyField1=XLS.Sheet1.CellA1,
MyField2=XLS.Sheet2.CellA1 WHERE ProjectID = @.ProjectID.

Also, it must handle dynamic XLS file names, eg 981-Budget.xls,
513-Budget.xls, xyz-Budget.xls

Is this the best way to go? Other suggestions most welcome?

Thanks everyone in advance!Sorry, you also has another question:

"Can a DTS package be fired from a SP using parameters? "

Yes, you have to run the DTSRUn in a cmdshell with XP_cmdshell and hand
over the paramters to global paramerters defined in the DTS package.
(consider the /A switch and look in the BOL for more syntax
information)

HTH, Jens Suessmeyer.|||I would do the querying once and put the data in a temporary table:

<SQLCode>
DECLARE @.Folder varchar(200)
DECLARE @.Filename varchar(200)
DECLARE @.Workbook varchar(200)

DECLARE @.Sqlstring varchar(4000)

SET @.FileName = 'SomeSheet.xls'
SET @.Folder = 'C:\SomeFolder\'
Set @.Workbook = 'SomeWorkbook'

SET @.SQLString = 'SELECT * FROM OpenDataSource(
''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @.Folder + @.FileName +
'";User ID=Admin;Password=;Extended properties=Excel 5.0'')...' +
@.Workbook

Create Table #SomeTable
(
<YourTableDefinitionhere>
)

INSERt INTO #SomeTable
EXEC(@.SQLString)

</Do anything with the data
</SQLCode
HTH, Jens Suessmeyer.