Importing excel to sql server....

Dear All
I am trying to import data from excel sheet to sql server database, by using method 2, it creates a table on fly but it never shows any table in database tables' list but when i execute the code again, system throws an exception that table already exists.
On the other hand method two assumes that there is an existing table in db, but after execution, it never shows data, table remains empty. Here is code, please tell me whats wrong with this code.
Regards
<code>
Dim ExcelConnectionAsNew System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\annie\anna.xls;Extended Properties=Excel 8.0;")

ExcelConnection.Open()
'For existing Table.............METHOD 1
Dim ExcelCommandAsNew System.Data.OleDb.OleDbCommand("INSERT INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] SELECT * FROM [Sheet1$];", ExcelConnection)

'For new Table.................METHOD 2
Dim ExcelCommandAsNew System.Data.OleDb.OleDbCommand("SELECT * INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] FROM [Sheet1$];", ExcelConnection)

ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()
</code>

You are missing linked server because Excel only knows Access SQL not T-SQL. Try the code below. Hope this helps.

/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

|||Hi
Thanx for reply but i dont want to create tables own my own because their are more than five hundred excel sheets to import. I need fastest mechanism to imort excel data to sql server!!!
And second problem is that when i export data from sql server to excel then excel is deformating the data e.g. i have a string "000001" in sql server table but excel sheet used to make it 1 (numeric). I am using clipboard to copy data from sql server to excel sheet (It is the only fastest way to export data from sql server to excel).
Please help.|||Execl is not a RDBMS it is a flat file application to do what you want you have two choices DTS on DTSRUN exe or DTS on xp_cmdshell and Sharepoint Portal. Hope this helps.

Importing Excel data that spans rows

Hi,

I need to import and transform data from an Excel spread sheet where the information spans two rows. The file layout is something like:

Row1Product1 QTY Store1 Store2 Store3 ...
Row2Product1 AMT Store1 Store2 Store3
Row3Product2 QTY Store1 Store2 Store3
Row4Product2 AMT Store1 Store2 Store3

The output would look like

Product1 Store1 QTY AMT
Product1 Store2 QTY AMT
...
Product2 Store1 QTY AMT
Product2 Store2 QTY AMT
...

We currently use a VB6 program using Office Tools to handle this. Is there a way to handle this with the out-of-the-box SSIS?

Thanks in advance,

John

Script Data Flow Component seem like a good candidate. You can probably reuse a lot of your VB code.

Thanks.

|||

Bob,

Do you need Visual Tools for Office 2005 to acess the worksheet from the script task?

jOHN

|||

Not necessarily.

You may get the data as it is using the SSIS Excel Source, and then manipulate the loaded data in the Script Component.

Thanks.

|||Hi John,

As you've already discovered, SSIS does not provide an out-of-the box solution to your problem. I think you may find DataDefractor a useful tool to do just that. It is a custom SSIS data source component designed to extract and normalize data captured in semi-structured data sources such as Excel and CSV data reports. With the help of its example-driven user interface you can map out a flexible schema to extract data straight out of your data source without writing one line of code.

You can download a free trial of DataDefractor at http://www.datadefractor.com

Cheers,
Vassil Kovatchev

Importing Excel data that spans rows

Hi,

I need to import and transform data from an Excel spread sheet where the information spans two rows. The file layout is something like:

Row1Product1 QTY Store1 Store2 Store3 ...
Row2Product1 AMT Store1 Store2 Store3
Row3Product2 QTY Store1 Store2 Store3
Row4Product2 AMT Store1 Store2 Store3

The output would look like

Product1 Store1 QTY AMT
Product1 Store2 QTY AMT
...
Product2 Store1 QTY AMT
Product2 Store2 QTY AMT
...

We currently use a VB6 program using Office Tools to handle this. Is there a way to handle this with the out-of-the-box SSIS?

Thanks in advance,

John

Script Data Flow Component seem like a good candidate. You can probably reuse a lot of your VB code.

Thanks.

|||

Bob,

Do you need Visual Tools for Office 2005 to acess the worksheet from the script task?

jOHN

|||

Not necessarily.

You may get the data as it is using the SSIS Excel Source, and then manipulate the loaded data in the Script Component.

Thanks.

|||Hi John,

As you've already discovered, SSIS does not provide an out-of-the box solution to your problem. I think you may find DataDefractor a useful tool to do just that. It is a custom SSIS data source component designed to extract and normalize data captured in semi-structured data sources such as Excel and CSV data reports. With the help of its example-driven user interface you can map out a flexible schema to extract data straight out of your data source without writing one line of code.

You can download a free trial of DataDefractor at http://www.datadefractor.com

Cheers,
Vassil Kovatchev

Importing data from various data sources with non standard formats

Hi all :)

I'm wondering if SSIS will be the solution to the problem I'm working on.

Some of our customers give us an Excel sheet with data they want to insert or update in the database.

I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.

This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.

But now I’ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.

Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I’ve looked at programmatically creating the package but I’ve got to say that’s quit hard to do… It would be easier to write the whole thing myself than to create the package trough code ;)

If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.

So how should I solve this problem? Use SSIS or not?

Thnx :)

Wouter de Kort

A question and comment:

1. Why are you using SCD for insert/updates on non-dimension data? It doesn't sound like you have a warehouse and a simple insert/update scenario should work (lookup data, if row not found, then insert, else update). But as long as it's working for you, that's ok.

2. You will have various problems with non-standard columns or file formats. You need to get your customer to send a defined format. One way around this is to create a staging package, loading the data into staging tables and then running the insert/updates on it from a standardized interface. There are numerous ways to set up staging, and I use VIEWS for mine as they are easy to replicate.

Wes|||

1. I already thought that SCD was a little bit overkill for this problem but it has the logic build in to check if a row is already present -> update it and otherwise it will delete it and that was just the thing I need. Would it be easier to 'duplicate' this functionality by using a lookup?

2. Off course it would be easiest to let the customer send a defined format... but we're not in the position the demand that from them (maybe in the future :))

I've started building code to convert the format the user sent to a standard xml format which I can feed to my package and the user can supply the mapping between the data and the XML schema. So they can send in whatever they want... the only ′problem′ is that I will have to build code that manually reads an Excel/Text/.... file and parse that to create the XML structure so I replicate some things that are already in SSIS :(

Could you tell me somewhat more about your staging package? Is that just an Insert into a custom build table that will hold the data? And then use a View to map it to the structure the SSIS package wants?

Wouter :)

|||

The lookup method I'm talking about does a similar function as the SCD. It just sounds like you are doing constant updates which violates the idea of "slowly" changing dimensions. I'm not too keen on the SCD stage in SSIS so I prefer to build my own lookup/update mechanism unless it truly is a type 2 SCD, but like I said whatever is working for you that meets the requirements is sufficient. (look at the sticky at the top of the forums referencing Jamie Thompson's blog and how he handles this)

Before I explain my staging, how often do the clients change their file formats? I don't want to lead you on the wrong track.

Thanks.

|||

Some of our clients send in an Excel sheet once a week with approximately 600 records. The strange thing is there system doesn't tell them if some stores are new or some are updated... it just gives the whole list.

But as I already said this could be changing in the future. Some other clients have requested Import functionality so they can connect their system/data to ours.

But what do you understand as slowly changing? My data has some modifications, some new records on each update so I thought this would be handy because it nicely encapsulates the logic?

Wouter :)

|||

Razorblade wrote:

Some

of our clients send in an Excel sheet once a week with approximately

600 records. The strange thing is there system doesn't tell them if

some stores are new or some are updated... it just gives the whole list.

But as I

already said this could be changing in the future. Some other clients

have requested Import functionality so they can connect their

system/data to ours.

But what

do you understand as slowly changing? My data has some modifications,

some new records on each update so I thought this would be handy

because it nicely encapsulates the logic?

Wouter :)

Your input list is fine if it's mixed data. As stated, you can

use the lookup function to process the list and sort into new/inserted

rows, and update or insert accordingly.

Nothing you have sounds slowly changing dimension(al), which is why I

questioned your use of the SCD wizard. SCD (in theory) is

for dimensions that don't change much, say a customers' home address or

an employees' position. But if the SCD wizard is working for you,

use it.

Go ahead and mark your question as answered if you are satisfied.

Thanks.

importing data from excel to SQL server real time

hi,
how can i take data from an Excel spread sheet and populate
a SQL-server database . I have 4-5 spread sheets which are
constantly updated real time. All updates,deletes,inserts in Excel
must be reflected in SQL server concurrently.
thanks
aparThe equivalent of SQL Server trigger in Excel can be handled with Worksheet_Change() event. So, write your vba to connect to SQL Server and update the changes.

Importing data from excel spread sheet to SQL 2005

I'm attempting to import data from an Excel spread sheet into an SQL 2005
this is what I try
Select *
Into SQLServerTable
From OPENROWSET(‘Micorsoft.jet.OLEDB.4.0’,’Excel 5.0, Database =
C:\example.xls;HDR = YES’,’Select * From [example])
I got error message abour Micorsoft.jet.OLEDB.4.0’OLEDB.4.0
I also try link server option linkink excel
is there an easier way to import excel?
Thanks
In Management Studio you can right click the database name and choose Tasks
> Import Data... to open the "SQL Server Import and Export Wizard. But that
only works well for on demand import.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>
|||Kim,
Could it be that you are missing the final single quote after [example]?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>
|||Try the IMPORT wizard , as a one off
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>

Importing data from excel spread sheet to SQL 2005

I'm attempting to import data from an Excel spread sheet into an SQL 2005
this is what I try
Select *
Into SQLServerTable
From OPENROWSET(‘Micorsoft.jet.OLEDB.4.0’,’Excel 5.0, Database =
C:\example.xls;HDR = YES’,’Select * From [example])
I got error message abour Micorsoft.jet.OLEDB.4.0’OLEDB.4.0
I also try link server option linkink excel
is there an easier way to import excel?
ThanksIn Management Studio you can right click the database name and choose Tasks
> Import Data... to open the "SQL Server Import and Export Wizard. But that
only works well for on demand import.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Kim,
Could it be that you are missing the final single quote after [example]?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Try the IMPORT wizard , as a one off
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database =
> C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>

Importing data from excel spread sheet to SQL 2005

I'm attempting to import data from an Excel spread sheet into an SQL 2005
this is what I try
Select *
Into SQLServerTable
From OPENROWSET(â'Micorsoft.jet.OLEDB.4.0â',â'Excel 5.0, Database = C:\example.xls;HDR = YESâ',â'Select * From [example])
I got error message abour Micorsoft.jet.OLEDB.4.0â'OLEDB.4.0
I also try link server option linkink excel
is there an easier way to import excel?
ThanksIn Management Studio you can right click the database name and choose Tasks
> Import Data... to open the "SQL Server Import and Export Wizard. But that
only works well for on demand import.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database => C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Kim,
Could it be that you are missing the final single quote after [example]?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database => C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Try the IMPORT wizard , as a one off
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database => C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>