[Beginner] How to fetch into an excel sheet ?

Question

0.00/5 (No votes)

See more:

hello every one. I want to import data from my excel sheet to my sqlServer 2008 database.
I have an excel sheet that contains different columns :
Example (excel sheet)

Service tag | marque| Type | Serial
FGT3456 | DELL | UC | Optiplex 760

and my table has the same structure, but instead of varchar values, I have foreign keys ( the IDs )

Example (table material)

Service tag | marque| Type | Serial
FGT3456 | 1 | 18 | 27

What I want to do is to fetch into the column marque in the excel sheet, get all the marque values, compare the values to those in my table, get all them IDs and in the end insert the Ids into my table material.
(I wish I was clear)

I tried this code but it's showing this error 'Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near ']'. Invalid object name 'SQL'.'

This is my code

SQL

ALTER PROCEDURE [dbo].[spx_Import]
	@SheetName varchar(20),
    @FilePath varchar(100),
	@HDR varchar(3),
	@TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)
    DECLARE @SQL1 nvarchar(1000)
    DECLARE @SQL2 nvarchar(1000)
		SET @SQL = 'SELECT idMarque FROM MarqueMateriel WHERE marque = SELECT (marque) FROM OPENDATASOURCE'
		SET @SQL1 = 'INSERT INTO Material (Service tag) SELECT (service tag) FROM OPENDATASOURCE'
		SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM '+@SQL''
			
    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 
    SET @SQL = @SQL + @HDR + ''''''')...[' 
    SET @SQL = @SQL + @SheetName + ']'
    SET @SQL1 = @SQL1 + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL1 = @SQL1 + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 
    SET @SQL1 = @SQL1 + @HDR + ''''''')...[' 
    SET @SQL1 = @SQL1 + @SheetName + ']'
    SET @SQL1 = @SQL2 + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL1 = @SQL2 + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 
    SET @SQL1 = @SQL2 + @HDR + ''''''')...[' 
    SET @SQL1 = @SQL2 + @SheetName + ']'
    EXEC sp_executesql @SQL
    EXEC sp_executesql @SQL1
	EXEC sp_executesql @SQL2
	
END

Please I need your help :( my tutor will turn my next 3 days into hell if I don't get it done

Posted 21-Jul-14 13:55pm

Member 10889990

Updated 22-Jul-14 2:07am

v2

Add a Solution

Comments

[no name] 21-Jul-14 21:06pm

The only thing that I added to your code was a check to see if the SP already exists and changed the ALTER PROCEDURE to a CREATE and I got no syntax error using SS2008R2
IF OBJECT_ID ( 'spx_Import', 'P' ) IS NOT NULL
DROP PROCEDURE spx_Import;
GO
CREATE PROCEDURE [dbo].[spx_Import]

Member 10889990 22-Jul-14 5:28am

This is the line that has the error "SELECT idMarque FROM MarqueMateriel WHERE marque = SELECT (marque) FROM OPENDATASOURCE" this is where I want to run through all the marque column in my excel sheet and check if there is a record similar to it and then insert in the table Material (SQL2)

3 solutions

Solution 2

The error is in this line:

SQL

SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM '+@SQL''

Replace it with:

SQL

SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM ' + @SQL + ''

Tip: When the error message is diplayed in Microsoft Sql Server Management Studio, double click on it to move cursor to the line with error.

Posted 22-Jul-14 2:32am

Maciej Los

Comments

Member 10889990 22-Jul-14 8:46am

I corrected it, Still have the same Error "Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near 'OPENDATASOURCE'."

Solution 1

change

SQL

SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM SQL'

to

SQL

SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM OPENDATASOURCE'

Posted 21-Jul-14 16:21pm

DamithSL

Comments

Member 10889990 22-Jul-14 5:25am

In the first query SQL I retieve the Marque ID from the table MarqueMateriel
In the query SQL2 I insert into the material table all rows in the SQL. This is how I should do, because I have only IDs in my table material not varchar values.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 10889990 · Accepted Answer · 2014-07-23T00:41:00

I found the solution, it's quite easy and should've think of it from the first time . I imported all the data from my excel sheet into a temp table, and fetch into my mark table get all IDs and insert them directly into my material table.
This is how I achieved this

SQL

USE [AxaStock]
GO


ALTER PROCEDURE [dbo].[spx_Import]
	@SheetName varchar(20),
    @FilePath varchar(100),
	@HDR varchar(3),
	@TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)
    DECLARE @QUERY nvarchar(1000)
		SET @SQL = 'INSERT INTO ESSAIEIMPORT (serviceTag, periodeGarantie, periodeLeasing, marque, designation, serie, entite, dateDG, nCommande) SELECT serviceTag, periodeGarantie, periodeLeasing, marque, designation, serie, entite, dateDG, nCommande FROM OPENDATASOURCE'
		SET @QUERY = 'insert into Materiel (serviceTag, idMarque, idTypeMateriel, idSerieMateriel) select distinct serviceTag, idMarque, idTypeMateriel, '+
		'idSerieMateriel from ESSAIEIMPORT ess, MarqueMateriel mm, Serie s, TypeMateriel tm where mm.marque=ess.marque and tm.nomType=ess.designation and '+
		's.serieMateriel=ess.serie delete from ESSAIEIMPORT' 	
    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 
    SET @SQL = @SQL + @HDR + ''''''')...[' 
    SET @SQL = @SQL + @SheetName + ']'
    EXEC sp_executesql @SQL
    EXEC sp_executesql @QUERY

	
END

[Beginner] How to fetch into an excel sheet ?

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us