insert data form xls

[aubweb]

Hello,

I'm trying to make a query to insert data form xls file to my database and it's working

Here's my code

INSERT INTO MY_TABLE
	SELECT SAP_NBR, PERIOD, ANSWER, OSAT, SCORE
	FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
	  'Data Source="C:\rip\TMP_Aubry\gsi_osat.xls";
	    User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
	WHERE 	SAP_NBR is not null
	AND 	PERIOD is not null
	AND 	ANSWER is not null
	AND 	OSAT is not null
	AND 	SCORE is not null

And I would like to verify if there no sap_nrb and period already the same in the database
so I add :

AND SAP_NBR not in MY_TABLE.SAP_NBR
AND PERIOD not in MY_TABLE.PERIOD

but it doesn't work

what's the correct syntax to make it work ???

Thanks a lot & merry Xmas...

[rmiao]

Should be:

and PERIOD not in (select PERIOD from MY_TABLE where ...)

[aubweb]

Hello, it's me again...

This time I'm trying to create a view with a variable for the Excel file to open.

The user upload a file to the server and than I execute the query

my view

CREATE VIEW [MyExcel] 
AS 
	SELECT SAP_NBR, PERIOD, ANSWER, OSAT, SCORE
	FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
	'Data Source="C:\rip\TMP_Aubry\'@FICHIER'";
	User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
		WHERE 	SAP_NBR is not null
			AND 	PERIOD is not null
			AND 	ANSWER is not null
			AND 	OSAT is not null
			AND 	SCORE is not null

and the insert query

INSERT INTO EAME_GSI_OSAT_BIS
	SELECT *
	FROM [MyExcel]
	WHERE 	SAP_NBR is not null
	AND 	PERIOD is not null
	AND 	ANSWER is not null
	AND 	OSAT is not null
	AND 	SCORE is not null
	AND	SAP_NBR not in (select SAP_NBR from EAME_GSI_OSAT_BIS where SAP_NBR <> [MyExcel].SAP_NBR)
	AND	PERIOD not in (select PERIOD from EAME_GSI_OSAT_BIS where PERIOD <> [MyExcel].PERIOD)

I would like to get the filename of the uploaded file to put it in my view
and
I would like to check if there's not a row with the same sap_nbr and same period yet.

I think I,m near the end of the tunnel ?
am I ? ...

[rmiao]

View doesn't accept variable.

[aubweb]

so how can I do that ?

[rmiao]

Try with sp.

[aubweb]

I was hungry
it's better after eat

		INSERT INTO EAME_GSI_OSAT_BIS (SAP_NBR, PERIOD, ANSWER, OSAT, SCORE)
			SELECT [MyExcel].SAP_NBR, [MyExcel].PERIOD, [MyExcel].ANSWER, [MyExcel].OSAT, [MyExcel].SCORE
			FROM [MyExcel]
				WHERE 	[MyExcel].SAP_NBR is not null
				AND 	[MyExcel].PERIOD is not null
				AND 	[MyExcel].ANSWER is not null
				AND 	[MyExcel].OSAT is not null
				AND 	[MyExcel].SCORE is not null
				AND		[MyExcel].SAP_NBR not in (select SAP_NBR from EAME_GSI_OSAT_BIS)
				AND		[MyExcel].PERIOD not in (select PERIOD from EAME_GSI_OSAT_BIS)

Works fine now

thx rmiao !

[Waltervis]

Well im new to this but,

Do you have used a store procedure now?

Because i have an excel file and i want to import some of the fields of this file into an already exsisting table

Name on name
phonenumber on phonenumber etc...

How can i do this in sql2000?

Please help me out with this
thank you

WH.

[aubweb]

HEY

I've created a view "MyExcel"

CREATE VIEW [MyExcel] as 
	SELECT SAP_NBR, PERIOD, ANSWER, OSAT, SCORE
	FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
	'Data Source="C:\rip\TMP_Aubry\gsi_osat.xls";
	User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
		WHERE 	SAP_NBR is not null
			AND 	PERIOD is not null
			AND 	ANSWER is not null
			AND 	OSAT is not null
			AND 	SCORE is not null

and in my coldfusion page I use

<cfquery name="xinsert" datasource="dash_fin">
		INSERT INTO EAME_GSI_OSAT_BIS (SAP_NBR, PERIOD, ANSWER, OSAT, SCORE)
			SELECT [MyExcel].SAP_NBR, [MyExcel].PERIOD, [MyExcel].ANSWER, [MyExcel].OSAT, [MyExcel].SCORE
			FROM [MyExcel]
				WHERE 	[MyExcel].SAP_NBR is not null
				AND 	[MyExcel].PERIOD is not null
				AND 	[MyExcel].ANSWER is not null
				AND 	[MyExcel].OSAT is not null
				AND 	[MyExcel].SCORE is not null
				AND		[MyExcel].SAP_NBR not in (select EAME_GSI_OSAT_BIS.SAP_NBR from EAME_GSI_OSAT_BIS WHERE EAME_GSI_OSAT_BIS.PERIOD = [MyExcel].PERIOD)
				AND		[MyExcel].PERIOD not in (select EAME_GSI_OSAT_BIS.PERIOD from EAME_GSI_OSAT_BIS WHERE EAME_GSI_OSAT_BIS.SAP_NBR = [MyExcel].SAP_NBR)
		</cfquery>