-
insert data form xls
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
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 :
Code:
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...
-
Should be:
and PERIOD not in (select PERIOD from MY_TABLE where ...)
-
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
Code:
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
Code:
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 ? ...
-
View doesn't accept variable.
-
-
-
I was hungry
it's better after eat
Code:
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 !
-
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.
-
HEY
I've created a view "MyExcel"
Code:
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
Code:
<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>
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|