You must Sign In to post a response.
  • Category: .NET

    Display custom error messages, if data type mismatch while reading excel file into database(in C# Pr

    I am new to .NET, Hope to get some suggestions on my below scenario:
    Scenario: I have an external Excel file, which should be read and loaded into the database using C#.
    So, For that I have created two tables:
    1. Staging table (A temporary table which reads the data from excel file before loading into the Final Table).
    2. Final Table (Contains the verified data).
    At the staging table, I need to write a program to validate the datatypes and prompt the user by displaying the error message.(Eg: where an excel column contains only the numeric values and if any cell contains the text/string in that particular column , C# Program should result in displaying the message( " Text not allowed , please enter numeric value").
    In order to achieve the above, I want to write a stored procedure which stores the error messages in temporary table and C# program execute the stored procedure to read the error messages from temporary table.
    I tried the above procedure by giving the non-numeric/text ("ABC") value into the numeric column. But I did not receive an error message as it is taking as null rather than displaying error message

    OLEDB Connection String :
    string STR_OLEDBCONNECTION = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + PATH + "';Extended Properties='Excel 12.0 Xml;HDR=Yes';IMEX = 1";

    Below is the SQL Stored Procedure to read the error messages into temporary table.
    USE [EXCEL_DATABASE]
    GO

    CREATE PROCEDURE [dbo].[spexcel]
    AS
    BEGIN
    --SET NOCOUNT ON added to prevent extra result sets from
    --interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @count int

    IF OBJECT_ID('tempdb..#TempResult') IS NOT NULL DROP TABLE #TempResult

    CREATE TABLE #TempResult
    (
    [Messages] nvarchar(max) NULL

    )

    --Incremental Table 1

    INSERT INTO #TempResult
    SELECT '[Incremental File 1] Column [Units] at Row: ' + CONVERT(nvarchar(255),(SCOPE_IDENTITY() + 1)) + ' is not in correct date format' FROM [dbo].[Staging_OEM]
    WHERE [Units] IS NOT NULL AND TRY_CONVERT(float, [Units]) IS NULL




    SELECT @count = COUNT(*) FROM #TempResult
    IF @count = 0
    BEGIN


    INSERT INTO [dbo].[Final_OEM] ([Source], [Geography], [Product Type], [Period], [Period Format],[Year], [Device Type], [Segment Type], [Platform], [Units],[Other Software Units],[Revenue],[Budget Units],[Budget Revenue],[Hardware Units],[Brand],[Price Band],[Segment],[Prod Rollup])
    SELECT [Source], [Geography], [Product Type], [Period], [Period Format],[Year], [Device Type], [Segment Type], [Platform],CONVERT(float, [Units]),[Other Software Units],CONVERT(float,[Revenue]),CONVERT(float,[Budget Units]),CONVERT(float,[Budget Revenue]),[Hardware Units],[Brand],[Price Band],[Segment],[Prod Rollup] FROM [dbo].[Staging_OEM]
    END

    SELECT * FROM #TempResult
    END


    C# Program to read Temporary table messages:
    static bool checkNumeric(SqlConnection SQLConnection,string fileLabel)
    {
    bool result = true;
    var errMsg = "";
    using (SqlCommand cmd = new SqlCommand())
    {

    cmd.CommandText = "[EXCEL_DATABASE].[dbo].[spExcel]";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = SQLConnection;
    SqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())
    {
    errMsg += reader[0].ToString() + Environment.NewLine;
    }
    cmd.Dispose();
    reader.Close();
    }
    if (errMsg != "")
    {
    Common.writeToError("[" + DateTime.Now.ToString() + "] Error encountered while reading " + fileLabel + Environment.NewLine + errMsg);
    result = false;
    }
    else
    {
    Common.writeToLog("[" + DateTime.Now.ToString() + "] Reading " + fileLabel + " is successful.");
    }
    return result;


    }
    }
    }
  • #765451
    Hi,

    What is your question? Have you got any error? Please elaborate your question, so that we can help you better.

    Request you to share the error details.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments