Import Excel data with .NET

[Niklas]

Hi

Before I start coding I need to be sure that nobody else has not done it yet
and that I can use it.

I need an import utility which import data from Excel to a database or some
object in .NET. The data in Excel is not in structured columns but can exist
everywhere in the workbook. For example if I am supposed to import a person
and all his/her cars which exist in a workbook I want to be able to create an
import protocol and specify that the first name exist in cell Sheet1!C4, the
last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column
Sheet2!B4. The resulting table to a database could look something like this

Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford

Are their any Open Source projects, Frameworks and/or components which I can
use or have a look at?

Regards
/Niklas

[Nicholas Paldino [.NET/C# MVP]]

Niklas,

There is really no need for such a thing really. You can use the Excel
automation model to access the sheet directly and then get the values in
those sheets/cells.

Basically, you should set a reference to the Microsoft Office Primary
Interop Assemblies in your project.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...

Hi

Before I start coding I need to be sure that nobody else has not done it
yet
and that I can use it.

I need an import utility which import data from Excel to a database or
some
object in .NET. The data in Excel is not in structured columns but can
exist
everywhere in the workbook. For example if I am supposed to import a
person
and all his/her cars which exist in a workbook I want to be able to create
an
import protocol and specify that the first name exist in cell Sheet1!C4,
the
last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column
Sheet2!B4. The resulting table to a database could look something like
this

Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford

Are their any Open Source projects, Frameworks and/or components which I
can
use or have a look at?

Regards
/Niklas

[Mark Rae]

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...

Are their any Open Source projects, Frameworks and/or components which I
can
use or have a look at?

Yes there are - loads of them - but, as Nicholas has already said, not
required at all for what you need...

[sloan]

I have some examples of using Excel here:

http://www.sqlservercentral.com/columnists/sholliday/

But there aren't really any "frameworks" .. because excel data can be some
random.

You can try putting the excel data into a dataset... and work with it from
there... in a business logic layer type object.


"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...

Hi

Before I start coding I need to be sure that nobody else has not done it

yet

and that I can use it.

I need an import utility which import data from Excel to a database or

some

object in .NET. The data in Excel is not in structured columns but can

exist

everywhere in the workbook. For example if I am supposed to import a

person

and all his/her cars which exist in a workbook I want to be able to create

an

import protocol and specify that the first name exist in cell Sheet1!C4,

the

last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column
Sheet2!B4. The resulting table to a database could look something like

this

>
Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford

Are their any Open Source projects, Frameworks and/or components which I

can

use or have a look at?

Regards
/Niklas

[Niklas]

Thank you. The programming stuff I know by heart, but I don't have the
resources to do a whole Export utility.

We have several thousands Workbooks which are created by different users
with different layout and now we want to export all this data to our other
systems. This program/utility should be configurable so each user can export
his/her own data. We are not the first company who wants to export Excel
data, so I had a look at the Internet. What I found was a lot of utility
which exports Excel tables to different databases.

Our data is not in nice tables, but is scattered throughout the whole
Workbook. The program should be able to create export protocol/templates
which can be reused when doing an export on an already specified Workbook
layout. I would prefer an utility/framework with source code which I can
integrate with out own system.

Regards
/Niklas

"Nicholas Paldino [.NET/C# MVP]" wrote:

Niklas,

There is really no need for such a thing really. You can use the Excel
automation model to access the sheet directly and then get the values in
those sheets/cells.

Basically, you should set a reference to the Microsoft Office Primary
Interop Assemblies in your project.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...

Hi

Before I start coding I need to be sure that nobody else has not done it
yet
and that I can use it.

I need an import utility which import data from Excel to a database or
some
object in .NET. The data in Excel is not in structured columns but can
exist
everywhere in the workbook. For example if I am supposed to import a
person
and all his/her cars which exist in a workbook I want to be able to create
an
import protocol and specify that the first name exist in cell Sheet1!C4,
the
last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column
Sheet2!B4. The resulting table to a database could look something like
this

Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford

Are their any Open Source projects, Frameworks and/or components which I
can
use or have a look at?

Regards
/Niklas

[NickHK]

But if everyone uses their own layout/format, how do you expect to make it
easier than if everyone combine all their own data into a single table that
you provide to them.
If these data use Named ranges or some set you can locate, then code could
combine them.
Otherwise what rules can you apply to locate the required data in the
correct order ?

NickHK

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:D3**********************************@microsof t.com...

Thank you. The programming stuff I know by heart, but I don't have the
resources to do a whole Export utility.

We have several thousands Workbooks which are created by different users
with different layout and now we want to export all this data to our other
systems. This program/utility should be configurable so each user can

export

his/her own data. We are not the first company who wants to export Excel
data, so I had a look at the Internet. What I found was a lot of utility
which exports Excel tables to different databases.

Our data is not in nice tables, but is scattered throughout the whole
Workbook. The program should be able to create export protocol/templates
which can be reused when doing an export on an already specified Workbook
layout. I would prefer an utility/framework with source code which I can
integrate with out own system.

Regards
/Niklas

"Nicholas Paldino [.NET/C# MVP]" wrote:

Niklas,

There is really no need for such a thing really. You can use the

Excel

automation model to access the sheet directly and then get the values in
those sheets/cells.

Basically, you should set a reference to the Microsoft Office

Primary

Interop Assemblies in your project.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...

Hi
>
Before I start coding I need to be sure that nobody else has not done

it

yet
and that I can use it.
>
I need an import utility which import data from Excel to a database or
some
object in .NET. The data in Excel is not in structured columns but can
exist
everywhere in the workbook. For example if I am supposed to import a
person
and all his/her cars which exist in a workbook I want to be able to

create

an
import protocol and specify that the first name exist in cell

Sheet1!C4,

the
last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in

column

Sheet2!B4. The resulting table to a database could look something like
this
>
Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford
>
Are their any Open Source projects, Frameworks and/or components which

I

can
use or have a look at?
>
Regards
/Niklas

[Niklas]

That means a lot of manual copy&past which no one likes and it is resource
intensive. We are going to export a lot of old data and future data will also
be exported in the same way. I would prefer that our super users do the data
mapping in the export utility and then all users uses these mappings to
export their data to our systems.

Regards
/Niklas

"NickHK" wrote:

But if everyone uses their own layout/format, how do you expect to make it
easier than if everyone combine all their own data into a single table that
you provide to them.
If these data use Named ranges or some set you can locate, then code could
combine them.
Otherwise what rules can you apply to locate the required data in the
correct order ?

NickHK

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:D3**********************************@microsof t.com...

Thank you. The programming stuff I know by heart, but I don't have the
resources to do a whole Export utility.

We have several thousands Workbooks which are created by different users
with different layout and now we want to export all this data to our other
systems. This program/utility should be configurable so each user can

export

his/her own data. We are not the first company who wants to export Excel
data, so I had a look at the Internet. What I found was a lot of utility
which exports Excel tables to different databases.

Our data is not in nice tables, but is scattered throughout the whole
Workbook. The program should be able to create export protocol/templates
which can be reused when doing an export on an already specified Workbook
layout. I would prefer an utility/framework with source code which I can
integrate with out own system.

Regards
/Niklas

"Nicholas Paldino [.NET/C# MVP]" wrote:

Niklas,
>
There is really no need for such a thing really. You can use the

Excel

automation model to access the sheet directly and then get the values in
those sheets/cells.
>
Basically, you should set a reference to the Microsoft Office

Primary

Interop Assemblies in your project.
>
Hope this helps.
>
>
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
>
"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...
Hi

Before I start coding I need to be sure that nobody else has not done

it

yet
and that I can use it.

I need an import utility which import data from Excel to a database or
some
object in .NET. The data in Excel is not in structured columns but can
exist
everywhere in the workbook. For example if I am supposed to import a
person
and all his/her cars which exist in a workbook I want to be able to

create

an
import protocol and specify that the first name exist in cell

Sheet1!C4,

the
last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in

column

Sheet2!B4. The resulting table to a database could look something like
this

Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford

Are their any Open Source projects, Frameworks and/or components which

I

can
use or have a look at?

Regards
/Niklas
>
>
>

[NickHK]

Niklas,
You say you have "several thousands Workbooks which are created by different
users with different layout".
How many different layouts ?
Can the user not record a macro of the actions for one sheet, the apply to
all their own sheets ?

NickHK

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:EE**********************************@microsof t.com...

That means a lot of manual copy&past which no one likes and it is resource
intensive. We are going to export a lot of old data and future data will

also

be exported in the same way. I would prefer that our super users do the

data

mapping in the export utility and then all users uses these mappings to
export their data to our systems.

Regards
/Niklas

"NickHK" wrote:

But if everyone uses their own layout/format, how do you expect to make

it

easier than if everyone combine all their own data into a single table

that

you provide to them.
If these data use Named ranges or some set you can locate, then code

could

combine them.
Otherwise what rules can you apply to locate the required data in the
correct order ?

NickHK

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:D3**********************************@microsof t.com...

Thank you. The programming stuff I know by heart, but I don't have the
resources to do a whole Export utility.
>
We have several thousands Workbooks which are created by different

users

with different layout and now we want to export all this data to our

other

systems. This program/utility should be configurable so each user can

export

his/her own data. We are not the first company who wants to export

Excel

data, so I had a look at the Internet. What I found was a lot of

utility

which exports Excel tables to different databases.
>
Our data is not in nice tables, but is scattered throughout the whole
Workbook. The program should be able to create export

protocol/templates

which can be reused when doing an export on an already specified

Workbook

layout. I would prefer an utility/framework with source code which I

can

integrate with out own system.
>
Regards
/Niklas
>
"Nicholas Paldino [.NET/C# MVP]" wrote:
>
Niklas,

There is really no need for such a thing really. You can use

the

Excel

automation model to access the sheet directly and then get the

values in

those sheets/cells.

Basically, you should set a reference to the Microsoft Office

Primary

Interop Assemblies in your project.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...
Hi
>
Before I start coding I need to be sure that nobody else has not

done

it

yet
and that I can use it.
>
I need an import utility which import data from Excel to a

database or

some
object in .NET. The data in Excel is not in structured columns but

can

exist
everywhere in the workbook. For example if I am supposed to import

a

person
and all his/her cars which exist in a workbook I want to be able

to

create

an
import protocol and specify that the first name exist in cell

Sheet1!C4,

the
last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in

column

Sheet2!B4. The resulting table to a database could look something

like

this
>
Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford
>
Are their any Open Source projects, Frameworks and/or components

which

I

can
use or have a look at?
>
Regards
/Niklas

[Niklas]

I don't know how many layouts whcich exists, but a way to go is to let the
users record a macro, thank you.
Regards
/Niklas

"NickHK" wrote:

Niklas,
You say you have "several thousands Workbooks which are created by different
users with different layout".
How many different layouts ?
Can the user not record a macro of the actions for one sheet, the apply to
all their own sheets ?

NickHK

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:EE**********************************@microsof t.com...

That means a lot of manual copy&past which no one likes and it is resource
intensive. We are going to export a lot of old data and future data will

also

be exported in the same way. I would prefer that our super users do the

data

mapping in the export utility and then all users uses these mappings to
export their data to our systems.

Regards
/Niklas

"NickHK" wrote:

But if everyone uses their own layout/format, how do you expect to make

it

easier than if everyone combine all their own data into a single table

that

you provide to them.
If these data use Named ranges or some set you can locate, then code

could

combine them.
Otherwise what rules can you apply to locate the required data in the
correct order ?
>
NickHK
>
"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:D3**********************************@microsof t.com...
Thank you. The programming stuff I know by heart, but I don't have the
resources to do a whole Export utility.

We have several thousands Workbooks which are created by different

users

with different layout and now we want to export all this data to our

other

systems. This program/utility should be configurable so each user can
export
his/her own data. We are not the first company who wants to export

Excel

data, so I had a look at the Internet. What I found was a lot of

utility

which exports Excel tables to different databases.

Our data is not in nice tables, but is scattered throughout the whole
Workbook. The program should be able to create export

protocol/templates

which can be reused when doing an export on an already specified

Workbook

layout. I would prefer an utility/framework with source code which I

can

integrate with out own system.

Regards
/Niklas

"Nicholas Paldino [.NET/C# MVP]" wrote:

Niklas,
>
There is really no need for such a thing really. You can use

the

Excel
automation model to access the sheet directly and then get the

values in

those sheets/cells.
>
Basically, you should set a reference to the Microsoft Office
Primary
Interop Assemblies in your project.
>
Hope this helps.
>
>
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
>
"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...
Hi

Before I start coding I need to be sure that nobody else has not

done

it
yet
and that I can use it.

I need an import utility which import data from Excel to a

database or

some
object in .NET. The data in Excel is not in structured columns but

can

exist
everywhere in the workbook. For example if I am supposed to import

a

person
and all his/her cars which exist in a workbook I want to be able

to

create
an
import protocol and specify that the first name exist in cell
Sheet1!C4,
the
last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in
column
Sheet2!B4. The resulting table to a database could look something

like

this

Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford

Are their any Open Source projects, Frameworks and/or components

which

I
can
use or have a look at?

Regards
/Niklas
>
>
>
>
>
>

[Phil]

Hi,
I am new in visual studio, and currently using ASP .net to do some of my
design. I have a similar question, I need to be able to have my pages ask for
an excel file, and automatically populate the data into a dataset. The excel
file used as an input should have a pretty set layout, so all i need is a way
to :

1. Refer to the excel file. (aspx page requests for the excel file to be
inputted by the user)
2. Read each cell.
3. Populate my dataset in the aspx page.

However, since i have no experience whatsoever in excel either, i am not
exactly sure where to begin.

"Basically, you should set a reference to the Microsoft Office Primary
Interop Assemblies in your project"

Currently this is all greek to me. It would be very helpful if you can
perhaps point me to any documentations that i should be reading that is
related to what i am trying to do. Also, a lot of my searches with keywords
such as "importing excel to asp visual studio" comes more often than not with
pages on exporting. Perhaps i should be looking with a more specific keyword?

Thanks in advance,
Phil

"Nicholas Paldino [.NET/C# MVP]" wrote:

Niklas,

There is really no need for such a thing really. You can use the Excel
automation model to access the sheet directly and then get the values in
those sheets/cells.

Basically, you should set a reference to the Microsoft Office Primary
Interop Assemblies in your project.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...

Hi

Before I start coding I need to be sure that nobody else has not done it
yet
and that I can use it.

I need an import utility which import data from Excel to a database or
some
object in .NET. The data in Excel is not in structured columns but can
exist
everywhere in the workbook. For example if I am supposed to import a
person
and all his/her cars which exist in a workbook I want to be able to create
an
import protocol and specify that the first name exist in cell Sheet1!C4,
the
last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in column
Sheet2!B4. The resulting table to a database could look something like
this

Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford

Are their any Open Source projects, Frameworks and/or components which I
can
use or have a look at?

Regards
/Niklas

[sloan]

http://www.sqlservercentral.com/colu...ataimports.asp

I think that will help. Its a KB I did a while back.


"Phil" <Ph**@discussions.microsoft.comwrote in message
news:DD**********************************@microsof t.com...

Hi,
I am new in visual studio, and currently using ASP .net to do some of my
design. I have a similar question, I need to be able to have my pages ask

for

an excel file, and automatically populate the data into a dataset. The

excel

file used as an input should have a pretty set layout, so all i need is a

way

to :

1. Refer to the excel file. (aspx page requests for the excel file to be
inputted by the user)
2. Read each cell.
3. Populate my dataset in the aspx page.

However, since i have no experience whatsoever in excel either, i am not
exactly sure where to begin.

"Basically, you should set a reference to the Microsoft Office Primary
Interop Assemblies in your project"

Currently this is all greek to me. It would be very helpful if you can
perhaps point me to any documentations that i should be reading that is
related to what i am trying to do. Also, a lot of my searches with

keywords

such as "importing excel to asp visual studio" comes more often than not

with

pages on exporting. Perhaps i should be looking with a more specific

keyword?

>
Thanks in advance,
Phil

"Nicholas Paldino [.NET/C# MVP]" wrote:

Niklas,

There is really no need for such a thing really. You can use the

Excel

automation model to access the sheet directly and then get the values in
those sheets/cells.

Basically, you should set a reference to the Microsoft Office

Primary

Interop Assemblies in your project.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Niklas" <Ni****@discussions.microsoft.comwrote in message
news:7B**********************************@microsof t.com...

Hi
>
Before I start coding I need to be sure that nobody else has not done

it

yet
and that I can use it.
>
I need an import utility which import data from Excel to a database or
some
object in .NET. The data in Excel is not in structured columns but can
exist
everywhere in the workbook. For example if I am supposed to import a
person
and all his/her cars which exist in a workbook I want to be able to

create

an
import protocol and specify that the first name exist in cell

Sheet1!C4,

the
last name in Sheet1!F4, telephone in Sheet1!E10 and all cars in in

column

Sheet2!B4. The resulting table to a database could look something like
this
>
Agnes|Carlsson|123456|Subaru
Agnes|Carlsson|123456|Volvo
Agnes|Carlsson|123456|Ford
>
Are their any Open Source projects, Frameworks and/or components which

I

can
use or have a look at?
>
Regards
/Niklas