protected string ProcessDictionary(string filename) { string insertresults = ""; SQL_utils sql = new SQL_utils("data"); //Check for tblpk int tblpk = sql.IntScalar_from_SQLstring("select coalesce(tblpk,-1) from def.tbl where measureID=" + Request.QueryString["mID"]); if (tblpk > 0) { DataSet ds = SpreadsheetGearUtils.GetDataSet(filename, false); DataTable dt = ds.Tables[0]; // Add tblpk DataColumn col = new DataColumn("tblpk", typeof(int)); col.DefaultValue = tblpk; // Add fldextractionmode - and thus allow this measure to be imported DataColumn col2 = new DataColumn("fldextractionmode", typeof(int)); int mode = (int)FieldExtractionMode.matchFldname; col2.DefaultValue = mode; dt.Columns.AddRange(new DataColumn[2] { col, col2 }); insertresults = sql.BulkInsert(dt, "fld", "def"); } sql.Close(); return(insertresults); }
private void ReadExcelFile(string file_path, string filename) { Regex regex = new Regex(@"\d{4}"); //ensure that the filename has at least 4 digits if (regex.IsMatch(filename)) { string str_docversid = regex.Match(filename).Value.ToString(); int filename_docversid = Convert.ToInt32(str_docversid); string xlfile_path = String.Format("{0}{1}", file_path, filename); if (docversid == filename_docversid) { if (noColumnHeaders) { dset = SpreadsheetGearUtils.GetDataSet(xlfile_path, false, false, SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders); } else { dset = SpreadsheetGearUtils.GetDataSet(xlfile_path, false); } processing_results.Log(String.Format("Loaded file {0} (docversID {1})", filename, docversid)); if (dset != null) { if (_fileext.ToLower() == ".xlsx" | _fileext.ToLower() == ".xls") { processing_results.Log(String.Format("Excel file contains {0} sheets.", dset.Tables.Count)); } else if (_fileext.ToLower() == ".csv") { if (dset.Tables.Count > 0) { processing_results.Log(String.Format("CSV file contains {0} rows.", dset.Tables[0].Rows.Count)); } else { processing_results.Log(String.Format("CSV file not found.")); } } } } else { processing_results.Log("No docversID provided."); } } }
protected string ProcessOtherIDfile(string filename) { string result = ""; string fullname = MapPath(UploadDirectory) + filename; DataSet ds = SpreadsheetGearUtils.GetDataSet(fullname, false); DataTable dt = ds.Tables[0]; bool hasID = false; bool hasotherID = false; bool hassiteID = false; bool hasstudyID = false; foreach (DataColumn col in dt.Columns) { string colname = col.ColumnName.ToLower(); col.ColumnName = colname; if (colname == "id") { hasID = true; } if (colname == "otherid") { hasotherID = true; } if (colname == "siteid") { hassiteID = true; } if (colname == "studyid") { hasstudyID = true; } } if (hasID && hasotherID && hassiteID && hasstudyID) { SQL_utils sql = new SQL_utils("data"); int success_counter = 0; int insertfail_counter = 0; int cantfindID_counter = 0; for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = dt.Rows[i]; string otherID = row["otherid"].ToString(); string siteID = row["siteid"].ToString(); string studyID = row["studyid"].ToString(); string ID = row["id"].ToString(); int personID = 0; try { personID = sql.IntScalar_from_SQLstring(String.Format("select personID from uwautism_research_backend..vwMasterStatus_S where ID='{0}' and studyID={1}", ID, studyID)); } catch (Exception ex) { string sqlcode2 = String.Format("insert into uwautism_research_backend..tblOtherID_failed(studyID, ID, otherID, otherIDsiteID, failreason, created, createdBy) values({0},'{1}','{2}', {3}, 'No personID',getdate(), sec.systemuser())" , studyID, ID, otherID, siteID); sql.NonQuery_from_SQLstring(sqlcode2); cantfindID_counter++; } if (personID > 0) { string sqlcode = String.Format("insert into uwautism_research_backend..tblOtherID(personID, otherID, otherIDsiteID, created, createdBy) values({0},'{1}',{2}, getdate(), sec.systemuser())" , personID, otherID, siteID); try { sql.NonQuery_from_SQLstring(sqlcode); success_counter++; } catch (Exception ex) { string sqlcode2 = String.Format("insert into uwautism_research_backend..tblOtherID_failed(studyID, ID, otherID, otherIDsiteID, failreason, created, createdBy) values({0},'{1}','{2}', {3}, 'failed insert',getdate(), sec.systemuser())" , studyID, ID, otherID, siteID); sql.NonQuery_from_SQLstring(sqlcode2); insertfail_counter++; } } } result = String.Format(" OtherID inserted for {0} records.", success_counter); if (cantfindID_counter > 0) { result += String.Format(" Cannot find ID for {0} records", cantfindID_counter); } if (insertfail_counter > 0) { result += String.Format(" Insert failed for {0} records", insertfail_counter); } } else { result = "File does not contain all the required fields: studyID, ID, otherID, siteID. Check the file for the correct column names."; } return(result); }
protected void LoadDescstats(string filename) { DataSet dset_descstats = new DataSet(); if (File.Exists(HttpContext.Current.Server.MapPath("~/App_Data/DataDownloads/" + filename))) { //convert to .xls here try { dset_descstats = SpreadsheetGearUtils.GetDataSet(filename); int g = 0; } catch (Exception) { string result = "Sorry, the file [" + filename + "] was unable to load. Create a new Excel file to generate the latest descriptive stats."; ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true); } } else { string data_filename = filename.Replace("DescStats", "Data"); DataSet dset = SpreadsheetGearUtils.GetDataSet(data_filename); int dataproj_pk = Convert.ToInt32(Request.QueryString["pk"]); if (dataproj_pk > 0 & dset != null & dset.Tables.Count > 0) { dset_descstats = utilDataProject.DataProject_Descstats(dataproj_pk, dset); SpreadsheetGearUtils.SaveDataSetToExcel(dset_descstats, filename, false, "xlsx"); int g = 0; } else { string result = "Sorry, the file [" + filename + "] was not found."; ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + result + "');", true); } } if (dset_descstats != null & dset_descstats.Tables.Count > 0) { foreach (DataTable dt in dset_descstats.Tables) { if (dt.TableName == "fileinfo") { GridView gv = new GridView(); gv.DataSource = dt; gv.DataBind(); panel0.Controls.Add(gv); } else { var dtSorted = dt.AsEnumerable() .OrderBy(r => r.Field <string>("Measure")) .ThenBy(r => r.Field <string>("Varname")) .ThenBy(r => r.Field <string>("TimePoint")) .CopyToDataTable(); GridView gv = new GridView(); gv.RowDataBound += gv_RowDataBound; gv.AllowSorting = true; gv.DataSource = dtSorted; gv.DataBind(); gv.CssClass = "GridViewClass"; Label lbl = new Label(); lbl.Text = " By " + dt.TableName; lbl.Font.Bold = true; lbl.Font.Size = 12; Literal lit = new Literal(); lit.Text = "<br/><br/>"; panel1.Controls.Add(lbl); panel1.Controls.Add(gv); panel1.Controls.Add(lit); } } } }