Import Dynamic exel to sqlserver
-
Hi, I try to import Excel files to sqlserver problem is that the number and the titles of columns are dynamic.How can i do that?by code. Thank you
-
Do you want a new table each time? You could use a select into SQL statement. What are you going to do with the data? A bunch of random SQL tables with random columns doesn't sound very useful.
thank you T M Gray, I have this problem for almost a month. I work on an intranet site management reviews of students. I want to help the manager module to enter student markes . the problem is that the subjects of a module are dynamic ,I tried a gridview in edit mode but I failed to create the columns dynamically and insert all data at once in the DB. Now I have an excel file generated by a dynamic sql query containing students of a module but I do not know how import it to the database because the column names and number change . the table: reveiw : id_student________id_subjects______marks table subject: id_subject_______name_subject______id_module the excel file columns: id_student_____name_student_____name_subject1____name_subject2.... in the name_subjet1 i have de marks of subject1. I do not know what to do ,is there another solution? Thanks.
-
thank you T M Gray, I have this problem for almost a month. I work on an intranet site management reviews of students. I want to help the manager module to enter student markes . the problem is that the subjects of a module are dynamic ,I tried a gridview in edit mode but I failed to create the columns dynamically and insert all data at once in the DB. Now I have an excel file generated by a dynamic sql query containing students of a module but I do not know how import it to the database because the column names and number change . the table: reveiw : id_student________id_subjects______marks table subject: id_subject_______name_subject______id_module the excel file columns: id_student_____name_student_____name_subject1____name_subject2.... in the name_subjet1 i have de marks of subject1. I do not know what to do ,is there another solution? Thanks.
-
It looks to me like you need to redo the query that generates the excel file. The relationships between student, subject, and module aren't completely clear to me.
Hi, this is the query that generates excel:
DECLARE @MATIERE nvarchar(max) SELECT @MATIERE = STUFF( ( select distinct ',[' + LIBELLE_MATIERE + ']' from MATIERE where CODE_MODULE =11 for xml path('') ), 1,1,'') DECLARE @SQL nvarchar(max) SELECT @SQL = N' select * from ( SELECT ETUDIANT.NUM_INSCRIPTION, ETUDIANT.NOM_PRENOM_ETUDIANT_ARABE,EXAMEN.CODE, EXAMEN.NOTE, MATIERE.LIBELLE_MATIERE FROM ETUDIANT INNER JOIN EXAMEN ON ETUDIANT.NUM_INSCRIPTION = EXAMEN.NUM_INSCRIPTION INNER JOIN CAPITALISE_MODULE ON ETUDIANT.NUM_INSCRIPTION = CAPITALISE_MODULE.NUM_INSCRIPTION INNER JOIN MATIERE ON EXAMEN.CODE_MATIERE = MATIERE.CODE_MATIERE INNER JOIN MODULE ON CAPITALISE_MODULE.CODE_MODULE = MODULE.CODE_MODULE AND MATIERE.CODE_MODULE = MODULE.CODE_MODULE WHERE (CAPITALISE_MODULE.CODE_MODULE = 11) AND (CAPITALISE_MODULE.CODE_TYPE_RELATION_MODULE = 1) ) Data PIVOT ( SUM(NOTE) FOR LIBELLE_MATIERE IN ( ' + @MATIERE + ' ) ) PivotTable ' exec sp_executesql @SQLthere is a relationship"review" between the student and subject and a relationship between subject and module(module consists of several materials) Thanks.