Excluding rows on a table while importing

I'm using DTSWizard to import a table from my main database to Temp.

This is the SQL statement...

CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[bounces] int
)

What I'd like to do for example, is exclude the first 5,000 rows, and import the rest.

Should I be using something other than DTSWizard, and it there something that can be added to the statement above telling it to start at a specified row?

This is probably fairly simple, but I'm new at this and I'd sure appreciate the help.

Thanks,

Bill

Do you have to use the DTSWizard?

If not, BULK INSERT has a FIRSTROW parameter.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Hi Wesley,

I'm really new at this so I'm not sure how to use BULK INSERT, is that a statement that would be used in a Query? Maybe I'm expecting to much from an import/export tool like DTSWizard to get so particular.

Thanks,

Bill

|||

It is indeed a TSQL statement. The Books Online has excellent documentation and examples.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||YOu can also use the DTS wizard with the query option of "skip rows" Normally this is ment for skipping rows that are part of the metadata like column headers etc. But this can be also used in your situation for skipping (non-)relevant data rows.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

In the DTS wizard I don't see anything relating to "skip rows", is this something that would run in the

Create Table Query?

Thanks,

Bill

|||OK, I assumed that you are using a text provider for the insert, which format is the data of or which data source are you querying ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

You mentioned "skip rows" using DTS wizard, and now I'm a bit confused... is there a feature in the DTS wizard where I can skip rows in a table when importing to another table?

Thanks,

Bill

|||

Hi Jens,

What I'm doing in this particular step is... I'm using DTS Wizard to copy a table from my Main Database to Temp. I went thru DTS Wizard and I see that if I was importing a FlatFile into SQL Server, on top of the Preview it allows you to Skip Rows, and this is what you were probably talking about.

Being that there isn't a Skip Row feature when copying a regular table from a database to Temp, but it does have the Create Table statement under Edit Mapping/Edit SQL, is there something that can be added to that statement to Skip Rows in the source table?.

Thanks,

Bill

|||Yes, that depends on the version you are using. In Sql Server 005 you can use the ROW_NUMBER() function to filter out appropiate rows. In SQL Server 2000 you would have to use another approach.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

I'm using SQL Server 2005 Express. This is what I have currently under Edit Mapping/Edit SQL...

CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[custom1] varchar(255),
[custom2] varchar(255),
[custom3] varchar(255),
[custom4] varchar(255),
[custom5] varchar(255),
[bounces] int
)

I'm really new at this which you can probably tell... do you think you can let me know the code I need to add to the above to exclude certain rows?

Thanks,

Bill

|||

You can use this as the source of the table (instead of using the table direct)

SELECT

*

FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM SomeTable

) SubQuery

WHERE ROWCOUNTER>5000

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, thanks for responding to this. I ran your code in DTS Wizard/Edit Mapping/Edit SQL and I received the error below.

Also, I'm not sure what you meant when you said... "You can use this as the source of the table (instead of using the table direct)." Was I correct in running this in DTS Wizard?

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)
Messages
* Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Incorrect syntax near 'subscriberid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

|||Well just a comma missing

SELECT

*

FROM

(

Code Snippet

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM xlaANLsubscribers

) SubQuery

WHERE ROWCOUNTER>90091

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for getting back with me Jens.

I ran your code in DTS Wizard.

This is the error I received...

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)

Messages

Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(

SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Invalid object name 'xlaANLsubscribers'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

EXCEPTION_ACCESS_VIOLATION

hello!

i am trying to import a text file using dts into a table created by the
package. everything checks out until it is run. the table is created
but then i get an error:
"need to run the object to perform the operation. Provider generated
code execution exception EXCEPTION_ACCESS_VIOLATION"

any ideas?
this is a development box. maybe the server cant handle it?
thanks in advance!
Tom<tomcaml@.yahoo.com> wrote in message
news:1106153734.711603.163950@.z14g2000cwz.googlegr oups.com...
> hello!
> i am trying to import a text file using dts into a table created by the
> package. everything checks out until it is run. the table is created
> but then i get an error:
> "need to run the object to perform the operation. Provider generated
> code execution exception EXCEPTION_ACCESS_VIOLATION"
>
> any ideas?
> this is a development box. maybe the server cant handle it?
> thanks in advance!
> Tom

You don't mention your version of MSSQL, but here are a couple of related KB
articles:

http://support.microsoft.com/kb/268413/EN-US/
http://support.microsoft.com/kb/271889/EN-US/

Another possibility is to try setting the task to execute on the main
package thread:

http://www.sqldts.com/default.aspx?232

If that doesn't help, you might want to post to
microsoft.public.sqlserver.dts, with more details of your environment and
exactly what task is failing.

Simon

Exception trying to import data from Excel .

Hello,
I am running SQL Management Studio and was trying to import excel data into
a table on my local 2005 database. When I choose the import task then choose
Excel as the data source I get this lovely message:
TITLE: SQL Server Import and Export Wizard
An error occurred which the SQL Server Integration Services Wizard was not
prepared to handle.
ADDITIONAL INFORMATION:
Exception has been thrown by the target of an invocation. (mscorlib)
The connection type "EXCEL" specified for connection manager
"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({06C927B9-F2F2-429B-B488-591883AE4655})
The connection type "EXCEL" specified for connection manager
"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({06C927B9-F2F2-429B-B488-591883AE4655})
I have searched the net (google and groups) and have come up with nothing.
Any ideas would be greatly appreciated.
Rich
Hello Rich,
It seems there is some issues in SSIS related components are not registered
properly. You may want to try the following:
Bring up a command prompt and
-- go to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
-- type below to unregister:
regsvr32.exe -u dtsconn.dll
--Then type below to register:
regsvr32.exe dtsconn.dll
If the issue persists, please ensure the Users (machinename\Users) have
Full Control on the
HKEY_CLASSES_ROOT\CLSID\.
In fact, you should go to PERMISSION-->ADVANCE
Ensure machinename\Users have full control permission.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exception trying to import data from Excel .
>thread-index: AcY9aoUve4FjMD2oRuCRoGREQJdwgw==
>X-WBNR-Posting-Host: 143.166.226.16
>From: "=?Utf-8?B?UmljaCBEZW5pcw==?=" <cojones@.community.nospam>
>Subject: Exception trying to import data from Excel .
>Date: Wed, 1 Mar 2006 11:58:33 -0800
>Lines: 39
>Message-ID: <61A56959-4BBB-4267-BDF9-D2CACA3AF52E@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.tools:29869
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hello,
>I am running SQL Management Studio and was trying to import excel data
into
>a table on my local 2005 database. When I choose the import task then
choose
>Excel as the data source I get this lovely message:
>TITLE: SQL Server Import and Export Wizard
>--
>An error occurred which the SQL Server Integration Services Wizard was not
>prepared to handle.
>--
>ADDITIONAL INFORMATION:
>Exception has been thrown by the target of an invocation. (mscorlib)
>--
>The connection type "EXCEL" specified for connection manager
>"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({06C927B9-F2F2-429B-B488-591883AE4655})
>--
>The connection type "EXCEL" specified for connection manager
>"{2D54D28C-42CF-4614-ADB6-371E9E4F927D}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({06C927B9-F2F2-429B-B488-591883AE4655})
>I have searched the net (google and groups) and have come up with nothing.

>Any ideas would be greatly appreciated.
>--
>Rich
>
|||Peter,
Thanks for the quick response. I tried what you said (unregister and
re-register) and it yeilded no results. I then applied the registry
permisison (while SQL Management studio was open and the error was on the
screen) to the CLSID folder (giving Users full control). I then tried the
operation again and got a message saying something to the effect of Server
Name Unknown (sorry I didnt think to write it down). So I closed the SQL
Management studio and re-opened it. When I tried again, I got a similar
message to the first time I tried but it had different CLSID's. I have
attached the message below.
One other thing to note, I noticed that I also do not have the drivers to be
able to read in such things as flat files. On a co-workers computer it
appears between the first .net dirvers and the media catalog drivers. I am
not sure if te two are related but I thought I would bring it up.
Lastly before, you replied today, I uninstalled SQL Server and the tools and
then re-installed. Still nothing.
Message follows:
TITLE: SQL Server Import and Export Wizard
An error occurred which the SQL Server Integration Services Wizard was not
prepared to handle.
ADDITIONAL INFORMATION:
Exception has been thrown by the target of an invocation. (mscorlib)
The connection type "EXCEL" specified for connection manager
"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({38BF22B4-3036-4BAB-9177-4820DA4EE187})
The connection type "EXCEL" specified for connection manager
"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({38BF22B4-3036-4BAB-9177-4820DA4EE187})
BUTTONS:
OK
Rich
"Peter Yang [MSFT]" wrote:

> Hello Rich,
> It seems there is some issues in SSIS related components are not registered
> properly. You may want to try the following:
> Bring up a command prompt and
> -- go to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> -- type below to unregister:
> regsvr32.exe -u dtsconn.dll
> --Then type below to register:
> regsvr32.exe dtsconn.dll
> If the issue persists, please ensure the Users (machinename\Users) have
> Full Control on the
> HKEY_CLASSES_ROOT\CLSID\.
> In fact, you should go to PERMISSION-->ADVANCE
> Ensure machinename\Users have full control permission.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> into
> choose
>
>
|||Hello Rich,
It seems that oledb related driver has issues on this server. I suggest
that you try the following steps:
1. Reinstall MDAC by right clicking %windir%\inf\mdac.inf->Install to
reinstall MDAC. You may prompt to insert Win2003 setup CD.
2. Reinstall Jet SP8 on your server:
239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
http://support.microsoft.com/default...b;en-us;239114
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exception trying to import data from Excel .
>thread-index: AcY9rUgr1ynjYsVLQnuskA4GOXAWCw==
>X-WBNR-Posting-Host: 70.123.136.122
>From: "=?Utf-8?B?UmljaCBEZW5pcw==?=" <cojones@.community.nospam>
>References: <61A56959-4BBB-4267-BDF9-D2CACA3AF52E@.microsoft.com>
<53c43gaPGHA.8000@.TK2MSFTNGXA03.phx.gbl>
>Subject: RE: Exception trying to import data from Excel .
>Date: Wed, 1 Mar 2006 19:56:27 -0800
>Lines: 167
>Message-ID: <ADECD008-AC46-4800-B932-121744E9B779@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.tools:29881
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Peter,
>Thanks for the quick response. I tried what you said (unregister and
>re-register) and it yeilded no results. I then applied the registry
>permisison (while SQL Management studio was open and the error was on the
>screen) to the CLSID folder (giving Users full control). I then tried the
>operation again and got a message saying something to the effect of Server
>Name Unknown (sorry I didnt think to write it down). So I closed the SQL
>Management studio and re-opened it. When I tried again, I got a similar
>message to the first time I tried but it had different CLSID's. I have
>attached the message below.
>One other thing to note, I noticed that I also do not have the drivers to
be
>able to read in such things as flat files. On a co-workers computer it
>appears between the first .net dirvers and the media catalog drivers. I
am
>not sure if te two are related but I thought I would bring it up.
>Lastly before, you replied today, I uninstalled SQL Server and the tools
and[vbcol=seagreen]
>then re-installed. Still nothing.
>Message follows:
>TITLE: SQL Server Import and Export Wizard
>--
>An error occurred which the SQL Server Integration Services Wizard was not
>prepared to handle.
>--
>ADDITIONAL INFORMATION:
>Exception has been thrown by the target of an invocation. (mscorlib)
>--
>The connection type "EXCEL" specified for connection manager
>"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({38BF22B4-3036-4BAB-9177-4820DA4EE187})
>--
>The connection type "EXCEL" specified for connection manager
>"{586BE7D4-F968-4585-9DE7-DAEDAD067CF3}" is not recognized as a valid
>connection manager type. This error is returned when an attempt is made to
>create a connection manager for an unknown connection type. Check the
>spelling in the connection type name.
> ({38BF22B4-3036-4BAB-9177-4820DA4EE187})
>--
>BUTTONS:
>OK
>--
>
>--
>Rich
>
>"Peter Yang [MSFT]" wrote:
registered[vbcol=seagreen]
rights.[vbcol=seagreen]
not[vbcol=seagreen]
to[vbcol=seagreen]
to[vbcol=seagreen]
nothing.
>
|||Peter,
You are a genius. Thanks a million. I re-installed the MDAC inf and
everything started working again. Thank you so much. I have a task where I
REALLY needed to be able to import some xls/csv spreadsheets and was not
looking forward to having to write the code to do it myself.
Thanks again.
Rich
"Peter Yang [MSFT]" wrote:

> Hello Rich,
> It seems that oledb related driver has issues on this server. I suggest
> that you try the following steps:
> 1. Reinstall MDAC by right clicking %windir%\inf\mdac.inf->Install to
> reinstall MDAC. You may prompt to insert Win2003 setup CD.
> 2. Reinstall Jet SP8 on your server:
> 239114: How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
> http://support.microsoft.com/default...b;en-us;239114
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> <53c43gaPGHA.8000@.TK2MSFTNGXA03.phx.gbl>
> be
> am
> and
> registered
> rights.
> not
> to
> to
> nothing.
>
|||Hello Rich,
Welcome! Great to hear the issue is resolved. :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Exception trying to import data from Excel .
>thread-index: AcY+EDFvaKWB5xrzSHyd9a5fSbx9sA==
>X-WBNR-Posting-Host: 143.166.226.17
>From: "=?Utf-8?B?UmljaCBEZW5pcw==?=" <cojones@.community.nospam>
>References: <61A56959-4BBB-4267-BDF9-D2CACA3AF52E@.microsoft.com>
<53c43gaPGHA.8000@.TK2MSFTNGXA03.phx.gbl>
<ADECD008-AC46-4800-B932-121744E9B779@.microsoft.com>
<MZFTnFdPGHA.2528@.TK2MSFTNGXA03.phx.gbl>
>Subject: RE: Exception trying to import data from Excel .
>Date: Thu, 2 Mar 2006 07:44:29 -0800
>Lines: 247
>Message-ID: <FF059899-A260-49CC-85F8-134D3D7F75BA@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.tools:29886
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Peter,
>You are a genius. Thanks a million. I re-installed the MDAC inf and
>everything started working again. Thank you so much. I have a task where
I[vbcol=seagreen]
>REALLY needed to be able to import some xls/csv spreadsheets and was not
>looking forward to having to write the code to do it myself.
>Thanks again.
>--
>Rich
>
>"Peter Yang [MSFT]" wrote:
4.0[vbcol=seagreen]
rights.[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
Server[vbcol=seagreen]
SQL[vbcol=seagreen]
similar[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
tools[vbcol=seagreen]
not[vbcol=seagreen]
to[vbcol=seagreen]
to[vbcol=seagreen]
have[vbcol=seagreen]
so[vbcol=seagreen]
data[vbcol=seagreen]
then[vbcol=seagreen]
was[vbcol=seagreen]
valid[vbcol=seagreen]
made[vbcol=seagreen]
the[vbcol=seagreen]
valid[vbcol=seagreen]
made[vbcol=seagreen]
the
>

excel vba to generate flat file definition

What object do I reference to use SSIS from Excel. I want to generate a flat file definition based on Excel. I have a lot of fields to import and I don't feel like creating them as flat file columns. I have a few tables and I get the source file format from the vendor in an Excel format. What I would like to do is generate a flat file connection in an empty package using VBA.

I don't think this is possible. VBA is a COM based environment whereas the SSIS API is dotnet.

I stand to be corrected. If there is a way of calling dotnet APIs from VBA then I don't know about it.

-Jamie

|||As Jamie notes, our documentation is aimed at using the SSIS API through managed code. Using the native API is unsupported. You might be able to work backwards from the managed examples, though, if you're feeling adventurous. I'd suggest you start here: http://msdn2.microsoft.com/en-us/library/ms345167.aspx

Excel truncation error?

Hi,

I am getting real stressed out because while trying to import an excel file into a temporary table i get a truncation error...

The max lenght used in the origin column is about 800 characters. So how the hell can i get the column to load? Jesus... this should be easy task... :P

Anyone experiencing this kind of stuff?

Regards,

Luis Sim?es

Anyone? Please....

Regards!

Excel truncation error?

Hi,

I am getting real stressed out because while trying to import an excel file into a temporary table i get a truncation error...

The max lenght used in the origin column is about 800 characters. So how the hell can i get the column to load? Jesus... this should be easy task... :P

Anyone experiencing this kind of stuff?

Regards,

Luis Sim?es

Anyone? Please....

Regards!

Excel to SQL server 2005

I need some pointers to help me import some data from an excel sheet into a SQL Server 2005 DB. Here is the scenario:

Each row in my excel sheet maps to three tables in the DB. Lets say there are 6 columns: Col1, Col2...Col6.

Col1 and Col2 map to DBTableA.

Col3 and Col4 map to DBTableB.

Col4 and Col6 map to DBTableC.

DBTableA's primary key is used as a foreign key in DBTableB and DBTableC.

Now what would be the best way to do this using SSIS?

I have gone through transforms and data flow tasks etc, so I am not exactly a newbie in SSIS. So it will be ok to use any SSIS specific terms in your response.

Thanks.

-Faisal

I guess the post was redundant.

After searching the forums for similar questions, I found the solution.

SSIS Nugget: Splitting order detail and order header information from one file into multiple tables

http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx

Thanks Jamie.

-Faisal

Excel to SQL

Hi everyone. I am very familiar with Access but I am new to SQL Server. I am
trying to import data to a table in SQL Server that comes from an AS400.
Everything is coming through except the date.
The date field in the Excel sheet looks like 21706. When this imports into
the table it changes to 6/5/1959. Is there some kind of formula I can put in
there so the date comes through correctly? This excel sheet is being created
from an AS400 program, so I can't change the date field there.
Any information would be greatly appreciated.
Thank you!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1If you have control of the Excel spreadsheet, I have had some success
importing using DTS after setting the spreadsheet column format to
display as a date. Of course that assumes that EXCEL can translate
21706 (or whatever value) to the date you expect.
Otherwise, I always import data like this into a special table that I
name with an _Imported suffix to the name just to be able to handle
problems like this. Such a table could have the column defined as a
number, then when the data is moved to the production table the number
can be converted on the fly by applying whatever constant adjustment
factor does the job. A view over the _Imported table can simplify
that.
Roy
On Tue, 21 Feb 2006 00:52:54 GMT, "Brandy R via webservertalk.com"
<u12396@.uwe> wrote:

>Hi everyone. I am very familiar with Access but I am new to SQL Server. I a
m
>trying to import data to a table in SQL Server that comes from an AS400.
>Everything is coming through except the date.
>The date field in the Excel sheet looks like 21706. When this imports into
>the table it changes to 6/5/1959. Is there some kind of formula I can put i
n
>there so the date comes through correctly? This excel sheet is being create
d
>from an AS400 program, so I can't change the date field there.
>Any information would be greatly appreciated.
>Thank you!!!|||run this script to create this function, then call the function when you
import from the AS/400.
CREATE FUNCTION dbo.ShortDate
(@.int bigint )
RETURNS smalldatetime
BEGIN
declare @.temp smalldatetime
---
if isdate( -- this is necessary because we have some illegal dates such as
2/29/03
---
case when len(ltrim(@.int)) in (5,6) then
case when len(@.int)=6 then
left(@.int,2)
else
case when len(@.int)=5 then
left(@.int,1)
end
end
+ '/'+ left(right(@.int,4),2) + '/' +
right(@.int,2)
else
null
end
---
)=1
---
Begin
set @.Temp= case when len(ltrim(@.int)) in (5,6) then
case when len(@.int)=6 then
left(@.int,2)
else
case when len(@.int)=5 then
left(@.int,1)
end
end
+ '/'+ left(right(@.int,4),2) + '/' +
right(@.int,2)
else
null
end
end|||I'm assuming that 21706 is supposed to translate to February 17, 2006;
what does 11106 translate to? January 11, 2006 or November 1, 2006?
Just asking because that will impact how you interpret the data.
Is your data an excel file, or is it a comma-delimited text file? If
the latter, check to be sure what the dtaa really looks like. It may
be 021706, in which case a function like the one below will work, but
if not, you need to see if you can tweak the extract settings to give
you a better data format than the one you have.
HTH,
Stu|||On Mon, 20 Feb 2006 20:25:26 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:

>If you have control of the Excel spreadsheet, I have had some success
>importing using DTS after setting the spreadsheet column format to
>display as a date. Of course that assumes that EXCEL can translate
>21706 (or whatever value) to the date you expect.
Well forget that, I forgot how odd the AS/400 was with dates.
Roy|||Thank you so much for trying to help me. I am trying to create a User Define
d
Function in SQL Server, however I am getting a syntax error near the keyword
"case". Any ideas?
Pinata Brain wrote:
>run this script to create this function, then call the function when you
>import from the AS/400.
>CREATE FUNCTION dbo.ShortDate
> (@.int bigint )
> RETURNS smalldatetime
> BEGIN
>declare @.temp smalldatetime
>---
>if isdate( -- this is necessary because we have some illegal dates such as
>2/29/03
>---
> case when len(ltrim(@.int)) in (5,6) then
> case when len(@.int)=6 then
> left(@.int,2)
> else
> case when len(@.int)=5 then
> left(@.int,1)
> end
> end
> + '/'+ left(right(@.int,4),2) + '/' +
>right(@.int,2)
> else
> null
> end
>---
> )=1
>---
> Begin
>set @.Temp= case when len(ltrim(@.int)) in (5,6) then
> case when len(@.int)=6 then
> left(@.int,2)
> else
> case when len(@.int)=5 then
> left(@.int,1)
> end
> end
> + '/'+ left(right(@.int,4),2) + '/' +
>right(@.int,2)
> else
> null
> end
> end
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Hi Stu,
It is supposed to translate to Feb 17, 2006. However it translates to June 5
,
1959. I can't figure out why. I have no control over the formatting of the
Excel file as it is created out of the AS400.
Stu wrote:
>I'm assuming that 21706 is supposed to translate to February 17, 2006;
>what does 11106 translate to? January 11, 2006 or November 1, 2006?
>Just asking because that will impact how you interpret the data.
>Is your data an excel file, or is it a comma-delimited text file? If
>the latter, check to be sure what the dtaa really looks like. It may
>be 021706, in which case a function like the one below will work, but
>if not, you need to see if you can tweak the extract settings to give
>you a better data format than the one you have.
>HTH,
>Stu
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Is it an excel file or is it a csv file? if it's a csv file that is
opened in excel, excel will truncate the leading zero from a number.
Can you open the file in wordpad or notepad?|||The reason that 21706 is translate by Excel in June 5, 1959 is because it's
taked as Julian date, so I figure out from AS400 you get an CSV file. Use it
in that way no in Excel
"Brandy R via webservertalk.com" wrote:

> Hi Stu,
> It is supposed to translate to Feb 17, 2006. However it translates to June
5,
> 1959. I can't figure out why. I have no control over the formatting of the
> Excel file as it is created out of the AS400.
> Stu wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200602/1
>|||Thank you. I was able to get the file in .csv format. The field in the table
is datetime. However, when I add this function to Transformation, I get an
error
Function Main()
str = DTSSource("Col001")
iMo = CInt( Mid( str, 1, 1) )
iDay = CInt( Mid( str, 2, 2) )
iYear = CInt( Mid( str, 4, 2) )
DTSDestination("DelDate") = DateSerial( iYear, iMo, iDay)
DTSDestination("ProjectID") = DTSSource("Col002")
DTSDestination("Phase") = DTSSource("Col003")
DTSDestination("Unit") = DTSSource("Col004")
DTSDestination("Tract") = DTSSource("Col005")
DTSDestination("Release") = DTSSource("Col006")
DTSDestination("UnitPlan") = DTSSource("Col007")
DTSDestination("UnitOpt") = DTSSource("Col008")
DTSDestination("POComp") = DTSSource("Col009")
DTSDestination("PrjFrm") = DTSSource("Col010")
DTSDestination("OrderNo") = DTSSource("Col011")
DTSDestination("OrderStat") = DTSSource("Col012")
DTSDestination("Boxes") = DTSSource("Col013")
Main = DTSTransformStat_OK
End Function
The error is
Error during Transformation 'AxScriptXform' for Row number 1. Errors
encountered so far in this task: 1.
Error Code:0
Error Source = Microsoft VBScript runtime error
Error Description: Type mismatch: 'CInt'
Error Line 9
Can you help me with this?
Marco A. Pia wrote:
>The reason that 21706 is translate by Excel in June 5, 1959 is because it's
>taked as Julian date, so I figure out from AS400 you get an CSV file. Use i
t
>in that way no in Excel
>
>[quoted text clipped - 13 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1

Excel Source: not to import blank rows

I am trying to load data from an excel file, how do I use SSIS so that it won't import blank rows in the source excel file?

Thanks in advance!

As with other databases, you'll need to use a WHERE clause to exclude rows that you don't want. There is no such option available on the driver, the connection manager, or the Excel source or destination.

-Doug

|||

The easiest way ist to load all rows from thr excel file and then use in the DataFlow a Conditional Split transformation to split the blank rows.

Your condition may be ISNULL(xxxxxx).

|||

Thank both of you very much. Condition Split works.

I have another question:

SSIS is importing data from an EXCEL source file, which is on a password protected web site, like: http://www.abc.com/myexcel.xls

I always get validation error: Excel Source[649], The AcquireConnection method call to the connection manager "ExcelSource" failed with error code 0xC0202009. Please help!

Thanks again.

|||

If you mean a password-protected Excel file, the driver simply cannot open that.

If you're talking about a Web site that requires a login, the Excel Source certainly can't handle this, but I'm not certain what the solution is. One option might be to use a Script task that logged in and copied the file locally first...

-Doug

|||

Thanks for your reply, Doug.

Yes, what I meant is, the web site requires a login to access the excel file. Script task is an option, but any other suggestions is stilly highly appreciated.

Excel Source dt_ntext problems

Hi:

I import data from multiple excel files into SQL DB. I have trouble with fields that could contain >255 chars.

If I have the col type = DT_Ntext in my Data Flow, the package fails for files that do not have any values >255 chars.

If I have the external coltype=dt_wstr and the output coltype=dt_wstr(4000) the package fails if the file contains any value >255 chars.(Implicit conversion does not occur, as expected).

I worked around by adding a dummy first row with >255 chars.

Is there a way to use a cast function to solve this prob? I tried using Select dt_ntext(fieldname) from Sheet1$, but that does not work.

Is there some clean way to get around this problem?

TIA
Kar

Hi Karfast

For now this have to be done manually. I was able successfully execute getting advise from Bob Bojanic

Follw these instructions carefully:

1) Go to Mapping option at Destination, Point your mouse for each of row having your above problem and its corresponding destination (note the Datatypes types and Lengths)

2) Open Advanced Editor for the Excel Source, Under Amend the Types and Lengths accordingly for all the columns in destination to match your source Columns' type and length

Thanks

Subhash Subramanyam

|||

Metadata of Excel sheets is often hard to deal with. The dummy first row seems as a clever solution to me. It is not elegant but the entire JET provider is far from that.

Select dt_ntext(fieldname) is not going to work as dt_ntext can be recognized only by SSIS and not by the JET provider. The more adequate query would be select LongText(fieldname), but I have no idea if JET can deal with it (don't have a way to try it at the moment).

HTH.

|||

Thanks, Bob.

I tried:

Select LongText(Fieldname) , and also tried out Memo etc. All these give a Undefined Function error.

I cant even find any help on this. There is apparently something called a Jet SQL Reference, but I couldnt find it. Not even in Access 2000 Local Help.

So I guess the dirty workaround is the only way :-(

Kar

Excel problem

I am trying to import data from sql server to excel.

it creates a new worksheet with name 'mytable' and excel file also has 3 sheets (by default as well).

when package is executed, Data gets transferred first time.

When I try to execute package again it gives me an error - that Table 'mytable' already exists.

To solve this I added another task before it creates the table ('mytable' sheet in excel), where I drop this table with the statement " DROP TABLE 'mytable' " (Connectiontype is EXCEL)

it works now, but I need to have this table 'mytable' in the excel, when ever I need to execute the package.

Is there any statement like in sql where I can check whether the table exists or not - like " IF EXISTS( select * from sysobjects where name = 'mytable')
I need to check this in Excel.

also, is there a way to drop other 3 sheets in excel, which comes by default.

Thanks in advance.

Management Studio export wizard after selecting source and destination.In specify table copy or query pane select query write query click next and click mapping button at select dource tables and views pane.Configure destination excel sheet.

bye.

|||

The Excel driver respects the saved Excel setting for the default number of sheets in a new workbook. One way to avoid this is to create an empty "template" workbook configured as you want, and use a File System Task to make a copy of that template each time that you want to perform your export.

Or, you could use Execute SQL Tasks to repeat both the DROP and the CREATE each time, and configure error settings such that, if the DROP fails (because the table does not exist), the package continues.

-Doug

|||I was able to do it by the first method, by creating an template workbook.

I tried to work by second method by checking whether table (worksheet) exists in excel or not, but couldn't get it through due to syntax error. for excel database connection, I didn't get it through the corrent syntax.|||

IF EXISTS certainly won't work with Excel.

If you need to check for existing tables, the System.Data.OleDb namespace has some methods to return schema information that Jet/Excel may support. (I haven't yet tried it myself.) You could connect in a Script Task, check schema information, and set a package variable value to indicate to downstream tasks whether the intended destination table exists or not.

-Doug

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||

Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For

a couple of weeks i am struggling with an excel import and export

application to sqlserver as i have to do it through an external

application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall

any one suggest me how shall i get values from excel sheet with

datatype varchar and import into the above said table as varchar values

(with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps

Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||

Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For

a couple of weeks i am struggling with an excel import and export

application to sqlserver as i have to do it through an external

application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall

any one suggest me how shall i get values from excel sheet with

datatype varchar and import into the above said table as varchar values

(with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps

Excel Import TEXT "9760020" imports "9.76002e+006"

Hello,

I have a problem with the Import of an Excel file and hope one of you can help me out.

There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)).

So far everything works fine but some fields like "9760020" imports "9.76002e+006".

My settings so far are:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"

In addition I altered the registry entry

TypeGuessRows to 0 (ImportMixedType = Text)

Has someone got a solution?

Thankx

Hello,

I didnt solved the probleme from above, but I can recommend the following approach to get really rid of the uncertainty if there is mixed data for the Provider.

Use a fixed first line with mixed data and set typeguessrows to 1 ... so you can be shure... becouse typeguessrows to 0 scans not all but the first 16000 some lines....

The Excel Import TEXT "9760020" imports "9.76002e+006" problem remains.... If someone knows a way... I would be very grateful.

|||

JWS...

I opened a new spread sheet and set the columns format to text and then copy the data back and it should work.. or u can select all the information in the spread sheet and copy it to a text and then import the text file directly to the spread sheet and then import ur spread sheet to the database.

Hope this helps...

Regards

Karen

Excel Import TEXT "9760020" imports "9.76002e+006"

Hello,

I have a problem with the Import of an Excel file and hope one of you can help me out.

There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)).

So far everything works fine but some fields like "9760020" imports "9.76002e+006".

My settings so far are:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"

In addition I altered the registry entry

TypeGuessRows to 0 (ImportMixedType = Text)

Has someone got a solution?

Thankx

Hello,

I didnt solved the probleme from above, but I can recommend the following approach to get really rid of the uncertainty if there is mixed data for the Provider.

Use a fixed first line with mixed data and set typeguessrows to 1 ... so you can be shure... becouse typeguessrows to 0 scans not all but the first 16000 some lines....

The Excel Import TEXT "9760020" imports "9.76002e+006" problem remains.... If someone knows a way... I would be very grateful.

|||

JWS...

I opened a new spread sheet and set the columns format to text and then copy the data back and it should work.. or u can select all the information in the spread sheet and copy it to a text and then import the text file directly to the spread sheet and then import ur spread sheet to the database.

Hope this helps...

Regards

Karen

Excel Import Identity Error

I am trying to import from an Excel Worksheet in the Enterprise Manager. I run through the wizard, and in transformations I have the Enable Identity Insert box checked, but when I run the import I get the following error:
Error at Destination for Row number 13880. Errors so far in this task: 1.
The statement has been terminated.
Cannot insert the value NULL into column 'ID', table 'CCReport.ITPSG.CCSPEND'; column does not allow nulls.
INSERT fails.

There are only 13881 rows, and I don't see anything in 13880 that is any different than the ones before it. Any ideas?try importing into the database first with out enabling the identity column, once it's imported then you can debug the data as well as re-populate the original table by doing any data manupulation.
" insert into originaltable
select columns from importedRawDatatable"

Excel Import Error

Hello,

I am trying out a simple SSIS package to import data from an xls into a table. For this I am using an Excel Source and an OLEDB Destination.
One of the fields is called Notes. In the Excel Source, I have specified the Notes columns as Unicode string [DT_WSTR]. This maps to a SQL Table column which is nvarchar(max)
The error messages that I get are :

[Excel Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Source [1]] Error: There was an error with output column "Notes" (272) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".

[Excel Source [1]] Error: The "output column "Notes" (272)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Notes" (272)" specifies failure on error. An error occurred on the specified object of the specified component.

Could anyone please help me with this.

regards,

Satya

Hi Satya,

I'm no expert so this is a best guess as to what might be happening. It sounds as though there may be rows in the excel file where the Notes filed contains data which isn't compatible with the DT_WSTR data type. What i would be inclined to do first is the following:

Right click on the Excel data source object and bring up the advanced edit options. Select the Input and Output Properties tab and then the output columns folder under inputs and outputs treeview. You should (if i'm not mistaken) find a property for ErrorRowDisposition. Change this value to RD_IgnoreFailure.

If you now save and run the package does it execute as expected. If there are indeed rows which have caused problems these should have been ignored by the transformation.

Let me know if this helps.

Cheers,

Grant|||

Great workaround, I had the similar problem, but is there any way to avoid the error itself?

Thanks

Atul