Bulk Insert from Excel [Programming SQL]

From: Steve Kass on 30 Jul 2006 12:34

Manuel,

I'm not sure I understand. If you only need to compare data (values in
the columns)
between Excel and your database table, there is nothing special about
using Excel.
Just use the OPENROWSET function like a table.

If you need to look at the column headers in Excel to see if they match the
columns of some particular table, then you could change HDR=YES to
HDR=NO and use SELECT TOP 1. While TOP 1 is not guaranteed
to give the first row (which with HDR=NO is the column names), I
think it is relatively safe to assume it will. (I'm not sure this is what
you want, but it is the only thing I can think of where you would
do something differently with Excel than with a table.

If I'm still misunderstanding you, can you be more specific about
what you have and what you need? For example:

I have a spreadsheet with the following structure:

ProjectID Length Width Height
4 5 6 8
2 3 9 8
....

and I have the following tables:

ProjectTable, which looks like this:
(show the column names and a few rows)

ProjectItemsTable, which looks like this:
(show the column names and a few rows)

I want to [find out| insert| ...] ..., which for the data
shown above, will have the result...

It's often possible to import the Excel data into
a table and then use the table to answer your questions,
which might be easier:

select * into #temporary_holding_table
from OPENROWSET(...

Steve

Manuel wrote:

>Thanks once again Steve.
>I'm afraid I haven´t made the scenario clear enough. I´ll try and to make
>things easier "projecto" in portuguese becomes "project".
>I have a main table called "project_tbl" with PK "projectID". I have a
>secondary table called "projectItems_tbl". "project_tbl.projectID" =
>"projectItems_tbl.projectID".
>Then I have the Excel Spreadsheet where the first column is called
>"projectID", followed by other columns where the description or parameters of
>Items are described like "Length", "Width", "Height".
>So, I can have in this Spreadsheet, many rows with the same "projectID", as
>all those items belong to the same "project".
>Actually what I thought, was just to compare all the columns on the first
>row of the spreadsheet and find out if that instance was already in
>"projectItems_tbl". That would mean that either the page was reloaded, or
>inadvertently someone try to insert what was already inserted. As a
>procedure, the insertion is only to be executed after a new spreadsheet is
>created for items of the next "projectID" overwriting the previous one, so it
>keeps the same name.
>I hope my portuguese-english makes sense to you.
>Thanks so much.
>
>Manuel
>
>
>"Steve Kass" wrote:
>
>
>
>>Manuel,
>>
>>You can join the Excel "table" like any other database table
>>
>>SELECT T.ProjectoID
>>FROM dbo.ProjectoItems_tbl AS T
>>JOIN (
>> SELECT ProjectoID
>> FROM OPENROWSET(... <same as before> )
>>) AS E
>>ON T.ProjectoID = E.ProjectoID
>>
>>or for example, to insert rows that are not already in the table:
>>
>>INSERT INTO dbo.ProjectoItems_tbl
>>FROM (
>> SELECT ProjectoID
>> FROM OPENROWSET(... <same as before> )
>>) AS E
>>WHERE NOT EXISTS (
>> SELECT * FROM dbo.ProjectItems_tbl AS T
>> WHERE T.ProjectID = E.ProjectoID
>>)
>>
>>Probably for what you need here, there is nothing you need
>>to do differently because you are working with Excel, other
>>than refer to the Excel data using OPENROWSET.
>>
>>SK
>>
>>Manuel wrote:
>>
>>
>>
>>>Hi Steve,
>>>Thanks for your reply.
>>>I´m still missing something. Please be patient as I´m a 58 year old guy that
>>>decided to learn some programming just a year ago.
>>>I really do have more than one row in the excel Spreadsheet.
>>>Suppose I have 3 columns. 'ProjectoID', 'Column 2' and 'Column3' named the
>>>same in the database table. The Spreadsheet will have many rows (number
>>>unknown).
>>>How do I refer to them in the second WHERE clause? Would it just be:
>>>WHERE Column2 = 'Column2' AND Column3 = 'Column3'
>>>
>>>What I need to accomplish is to prevent the insertion of the Spreadsheet if
>>>someone hits F5 or reloads the browser or the Spreadsheet has not been
>>>modified.
>>>Thanks again.
>>>
>>>
>>>
>>>

From: Manuel on 30 Jul 2006 13:58

Let me try to be more specific and at the same time it might help explain a
bit of what is behind all this.
This is a company (my daughter´s) that moves objects of art (the items)
within Europe. The project is the job of moving for example a collection of
art from Museum A in Madrid to Museum B in Lisbon.

project_tbl (some colums)

projectID(autoNumber) projectCityOfOrigin projectCityOfDestination

1 Madrid Lisbon
2 Paris Madrid

Then I have projectItems_tbl

projectItemID(autoNumber) projectID Length Height Width

1 1 2.5
3.5 6
2 1 3.2
4.5 7
3 1 3.5
4.2 8

The customers have a supplied Excel template with the exact same column
names as projectItems_tbl, without obviously "projectoItemID", and
"projectID" that they leave blank as they don´t know what will it be, that
they fill with the description of the items to be moved and send it by E-mail.
Once received, the new job is created. Knowing the new "projectID", whoever
receives the Spreadsheet fills all rows with that specific "projectID" and
saves it always with the same name, the one that the query refers to.
Ideally would be to detect any row in the Spreadsheet with the same values
as in "projectItems_tbl". For example 1, 3.5, 4.2, 8.
That item, wich is "projectItemID=3" already exists belonging to projectID=1
and with the same values.

I really hope you undestand what I´m trying to achieve.

Thank you very much

--
Manuel

"Steve Kass" wrote:

> Manuel,
>
> I'm not sure I understand. If you only need to compare data (values in
> the columns)
> between Excel and your database table, there is nothing special about
> using Excel.
> Just use the OPENROWSET function like a table.
>
> If you need to look at the column headers in Excel to see if they match the
> columns of some particular table, then you could change HDR=YES to
> HDR=NO and use SELECT TOP 1. While TOP 1 is not guaranteed
> to give the first row (which with HDR=NO is the column names), I
> think it is relatively safe to assume it will. (I'm not sure this is what
> you want, but it is the only thing I can think of where you would
> do something differently with Excel than with a table.
>
> If I'm still misunderstanding you, can you be more specific about
> what you have and what you need? For example:
>
> I have a spreadsheet with the following structure:
>
> ProjectID Length Width Height
> 4 5 6 8
> 2 3 9 8
> ....
>
> and I have the following tables:
>
> ProjectTable, which looks like this:
> (show the column names and a few rows)
>
> ProjectItemsTable, which looks like this:
> (show the column names and a few rows)
>
> I want to [find out| insert| ...] ..., which for the data
> shown above, will have the result...
>
> It's often possible to import the Excel data into
> a table and then use the table to answer your questions,
> which might be easier:
>
> select * into #temporary_holding_table
> from OPENROWSET(...
>
> Steve
>
> Manuel wrote:
>
> >Thanks once again Steve.
> >I'm afraid I haven´t made the scenario clear enough. I´ll try and to make
> >things easier "projecto" in portuguese becomes "project".
> >I have a main table called "project_tbl" with PK "projectID". I have a
> >secondary table called "projectItems_tbl". "project_tbl.projectID" =
> >"projectItems_tbl.projectID".
> >Then I have the Excel Spreadsheet where the first column is called
> >"projectID", followed by other columns where the description or parameters of
> >Items are described like "Length", "Width", "Height".
> >So, I can have in this Spreadsheet, many rows with the same "projectID", as
> >all those items belong to the same "project".
> >Actually what I thought, was just to compare all the columns on the first
> >row of the spreadsheet and find out if that instance was already in
> >"projectItems_tbl". That would mean that either the page was reloaded, or
> >inadvertently someone try to insert what was already inserted. As a
> >procedure, the insertion is only to be executed after a new spreadsheet is
> >created for items of the next "projectID" overwriting the previous one, so it
> >keeps the same name.
> >I hope my portuguese-english makes sense to you.
> >Thanks so much.
> >
> >Manuel
> >
> >
> >"Steve Kass" wrote:
> >
> >
> >
> >>Manuel,
> >>
> >>You can join the Excel "table" like any other database table
> >>
> >>SELECT T.ProjectoID
> >>FROM dbo.ProjectoItems_tbl AS T
> >>JOIN (
> >> SELECT ProjectoID
> >> FROM OPENROWSET(... <same as before> )
> >>) AS E
> >>ON T.ProjectoID = E.ProjectoID
> >>
> >>or for example, to insert rows that are not already in the table:
> >>
> >>INSERT INTO dbo.ProjectoItems_tbl
> >>FROM (
> >> SELECT ProjectoID
> >> FROM OPENROWSET(... <same as before> )
> >>) AS E
> >>WHERE NOT EXISTS (
> >> SELECT * FROM dbo.ProjectItems_tbl AS T
> >> WHERE T.ProjectID = E.ProjectoID
> >>)
> >>
> >>Probably for what you need here, there is nothing you need
> >>to do differently because you are working with Excel, other
> >>than refer to the Excel data using OPENROWSET.
> >>
> >>SK
> >>
> >>Manuel wrote:
> >>
> >>
> >>
> >>>Hi Steve,
> >>>Thanks for your reply.
> >>>I´m still missing something. Please be patient as I´m a 58 year old guy that
> >>>decided to learn some programming just a year ago.
> >>>I really do have more than one row in the excel Spreadsheet.
> >>>Suppose I have 3 columns. 'ProjectoID', 'Column 2' and 'Column3' named the
> >>>same in the database table. The Spreadsheet will have many rows (number
> >>>unknown).
> >>>How do I refer to them in the second WHERE clause? Would it just be:
> >>>WHERE Column2 = 'Column2' AND Column3 = 'Column3'
> >>>
> >>>What I need to accomplish is to prevent the insertion of the Spreadsheet if
> >>>someone hits F5 or reloads the browser or the Spreadsheet has not been
> >>>modified.
> >>>Thanks again.
> >>>
> >>>
> >>>
> >>>
>

From: Manuel on 30 Jul 2006 13:58

From: Steve Kass on 30 Jul 2006 23:49

Manuel,

It's not really an Excel issue at this point, but I think what you want is

SELECT *
FROM OPENROWSET(... <same as before> )
) AS E
WHERE EXISTS (
SELECT * FROM dbo.ProjectItems_tbl AS T
WHERE T.Length= E.Length
AND T.Height = E.Height
AND T.Width = E.Width
)

In other words, find the items in the spreadsheet that look like
they are already in the table. (As written, for any projectID
value, but if you want to check if they are in the table only
for a specific projectID value (3 for this example):

SELECT *
FROM OPENROWSET(... <same as before> )
) AS E
WHERE EXISTS (
SELECT * FROM dbo.ProjectItems_tbl AS T
WHERE T.Length= E.Length
AND T.Height = E.Height
AND T.Width = E.Width
)
AND ProjectID = 3

I left out checking for a match on ItemID also, since it is
an auto number, and could be different in the spreadsheet
from the number it is in the table, but if that discripancy can't
happen, then you would include the additional condition in
the where clause.

SK

Manuel wrote:

>Let me try to be more specific and at the same time it might help explain a
>bit of what is behind all this.
>This is a company (my daughter´s) that moves objects of art (the items)
>within Europe. The project is the job of moving for example a collection of
>art from Museum A in Madrid to Museum B in Lisbon.
>
>project_tbl (some colums)
>
>projectID(autoNumber) projectCityOfOrigin projectCityOfDestination
>
>1 Madrid Lisbon
>2 Paris Madrid
>
>Then I have projectItems_tbl
>
>projectItemID(autoNumber) projectID Length Height Width
>
>1 1 2.5
>3.5 6
>2 1 3.2
>4.5 7
>3 1 3.5
>4.2 8
>
>The customers have a supplied Excel template with the exact same column
>names as projectItems_tbl, without obviously "projectoItemID", and
>"projectID" that they leave blank as they don´t know what will it be, that
>they fill with the description of the items to be moved and send it by E-mail.
>Once received, the new job is created. Knowing the new "projectID", whoever
>receives the Spreadsheet fills all rows with that specific "projectID" and
>saves it always with the same name, the one that the query refers to.
>Ideally would be to detect any row in the Spreadsheet with the same values
>as in "projectItems_tbl". For example 1, 3.5, 4.2, 8.
>That item, wich is "projectItemID=3" already exists belonging to projectID=1
>and with the same values.
>
>I really hope you undestand what I´m trying to achieve.
>
>Thank you very much
>
>
>
>
>

From: Steve Kass on 30 Jul 2006 23:49

First | Prev | Next | Last
Pages: 1 2 3 4 5
Prev: Dateformat
Next: Reading .LDF