question

Cenk-1028 avatar image
0 Votes"
Cenk-1028 asked LanHuang-MSFT commented

Primitive Reconciliation

Hello,

There is this primitive reconciliation being made with Excel. In the beginning, it was not a problem because the data was scarce, and comparing GUIDs by eye in excel was not difficult. Now the data is too much for visual inspection. So is there a way that you can suggest to compare these GUIDs in excel with the database?

I really appreciate any help you can provide.

Thank you.

dotnet-csharpdotnet-aspnet-mvcdotnet-aspnet-webapi
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Cenk-1028,
Do you want to use asp.net to achieve this function?

0 Votes 0 ·

Yes, I have Asp.Net MVC and a web API. I am looking for a solution without importing excel data into the SQL database.

0 Votes 0 ·

Importing data into a database is industry standard and best practice.

If you want to make a very simple processes as difficult as possible then import the Excel data into a collection like a List<T> on the web server. Next fetch the data from the database into a collection on the same web server. Finally, use LINQ to compare the data.

0 Votes 0 ·

The common solution is importing the excel data into a database table. Once imported you have the full force of SQL to do whatever comparisons are needed.

Database servers like SQL Server have excellent import tools. Read the docs...

0 Votes 0 ·

needs to be solved in a more automated way. Importing into the database, writing queries to compare, deleting imported data from the database, etc. no such resource exists.

0 Votes 0 ·

Let me remind you what you asked...

So is there a way that you can suggest to compare these GUIDs in excel with the database?

You specifically asked for a suggestion for comparing data. A database is the best tool for comparing set data. That's what databases are for. If database design is not in your wheelhouse then you should have mentioned the design constraint.

needs to be solved in a more automated way. Importing into the database, writing queries to compare, deleting imported data from the database, etc. no such resource exists.

This is an uninformed response at best.

The processing can be done on a web server if that's what you prefer or know best. Rather than importing the data into a database, the data is imported into the web server's memory and manipulated. You'll need a tool to extract the Excel data which can be an Excel API or ODBC. You'll also need to fetch data from the database which can be done with LINQ or ADO.NET. Then you'll write code to do the actual comparison/reconciliation which runs on the web server. I assume LINQ but looping constructs will work too.

Where do the results of reconciliation end up? The database? Excel?

Can you at least show us the code you've attempted?










0 Votes 0 ·
Show more comments
SEOExpert-5078 avatar image
0 Votes"
SEOExpert-5078 Suspended answered SEOExpert-5078 Suspended published

Hi,

The normal arrangement is bringing the succeed information into a data set table. When imported you have the overwhelming power of SQL to do anything correlations are required.

Thanks


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered LanHuang-MSFT commented

Hi @Cenk-1028,

I am looking for a solution without importing excel data into the SQL database.

I think you can try to upload Excel file, then read and import its data into DataSet or DataTable. This way you don't have to import the data into the database.
You can first import the data into an Excel file and then display it in an ASP.Net GridView control.
Then view the data through the button, and export the gridview as an excel file after the gridview is updated.
You can check the example below, the data checking part can be changed according to your own needs.

  protected void Page_Load(object sender, EventArgs e)
         {
    
         }
         protected void btnUpload_Click(object sender, EventArgs e)
         {
             if (FileUpload1.HasFile)
             {
                 string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                 string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                 string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
                 string FilePath = Server.MapPath(FolderPath + FileName);
                 FileUpload1.SaveAs(FilePath);
                 Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
             }
         }
         private void Import_To_Grid(string FilePath, string Extension, string isHDR)
         {
             string conStr = "";
             switch (Extension)
             {
                 case ".xls": //Excel 97-03
                     conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                     break;
                 case ".xlsx": //Excel 07
                     conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                     break;
             }
             conStr = String.Format(conStr, FilePath, isHDR);
             OleDbConnection connExcel = new OleDbConnection(conStr);
             OleDbCommand cmdExcel = new OleDbCommand();
             OleDbDataAdapter oda = new OleDbDataAdapter();
             DataTable dt = new DataTable();
             cmdExcel.Connection = connExcel;
             //Get the name of First Sheet
             connExcel.Open();
             DataTable dtExcelSchema;
             dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
             string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
             connExcel.Close();
             //Read Data from First Sheet
             connExcel.Open();
             cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
             oda.SelectCommand = cmdExcel;
             oda.Fill(dt);
             connExcel.Close();
             //Bind Data to GridView
             GridView1.Caption = Path.GetFileName(FilePath);
             GridView1.DataSource = dt;
             GridView1.DataBind();
         }
         protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
         {
             string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
             string FileName = GridView1.Caption;
             string Extension = Path.GetExtension(FileName);
             string FilePath = Server.MapPath(FolderPath + FileName);
             Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
             GridView1.PageIndex = e.NewPageIndex;
             GridView1.DataBind();
         }
         protected void btnExportGridintoExcel_Click(object sender, EventArgs e)
         {
             ExportGridToExcel();
         }
    
         private void ExportGridToExcel()
         {
             Response.Clear();
             Response.AddHeader("content-disposition", "attachment;filename=ExportGridData.xls");
             Response.ContentType = "File/Data.xls";
             StringWriter StringWriter = new System.IO.StringWriter();
             HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
             GridView1.RenderControl(HtmlTextWriter);
             Response.Write(StringWriter.ToString());
             Response.End();
         }
    
         public override void VerifyRenderingInServerForm(Control control)
         {
             // controller   
         }
    
         protected void CHECK_Click(object sender, EventArgs e)
         {
    
             foreach (GridViewRow row in GridView1.Rows)
             {
                 int value =Convert.ToInt32(row.Cells[2].Text);
                 if(value != 1)
                 {
                     row.Cells[2].Text = 1.ToString();
                 }
    
             }
         }

 <div>
             <asp:FileUpload ID="FileUpload1" runat="server" /><br />
             <asp:Button ID="btnUpload" runat="server" Text="Upload" OnC lick="btnUpload_Click" />
             <br />
             <asp:Label ID="Label1" runat="server" Text="Has Header ?" />
             <asp:RadioButtonList ID="rbHDR" runat="server">
                 <asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" >
                 </asp:ListItem>
                 <asp:ListItem Text = "No" Value = "No"></asp:ListItem>
             </asp:RadioButtonList>
             <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="PageIndexChanging" AllowPaging = "true">
             </asp:GridView>
             <asp:Button ID="CHECK" runat="server" Text="CHECK" OnC lick="CHECK_Click" /><br/><br />
             <asp:Button ID="btnExportGridintoExcel" runat="server" Text="btnExportGridintoExcel" OnC lick="btnExportGridintoExcel_Click" />         
         </div>

Web.config

 <connectionStrings>        
         <add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
         <add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
     </connectionStrings>

261221-1.gif
Best regards,
Lan Huang


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



1.gif (345.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @LanHuang-MSFT,

Thank you for your support. I wonder how does your solution work with let's say 10K data? Any performance issues?

0 Votes 0 ·

Hi @Cenk-1028,
To improve performance, a WHERE clause can be used to greatly reduce the number of rows returned to the client.
You can also reduce the amount of data returned to the client by explicitly listing only the required columns in the SELECT statement.
Another good workaround is to retrieve rows in batches (e.g. hundreds of rows at a time),
and only retrieve the next batch when the client finishes the current batch.
This method also works when you are reading information from a database.
For more information you can check the documentation:
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/populating-a-dataset-from-a-dataadapter
Best regards,
Lan Huang

0 Votes 0 ·