none
How do I preserve leading zeros in SQL when I export to Excel sheet? RRS feed

  • Question

  • Viorel_MVP gave me a fix for the problem of leading zeros being removed when data is exported to Excel as a string. One user is concerned and said. I am not sure if exporting the data as string formulas is going to work (“=[content]”).  I explored doing this when I was trying to correct the query from the SQL side and my concern was that the information is actually stored within the output.  I am not sure what processes people use the output in downstream, but it could interfere with those (e.g. comparing on a value or sending the file to another process).  Text files actually contain the formulaic values too.

    How can I addressed this when a cell or cell contain a formula and data is exported as string?

    The SQL data displayed on the web browser when Run Query button is clicked is in a GridView. When Export to Excel button is clicked that GridView data is exported to Excel and that is when the preceding zero/s are lost and not exported. Is there a way to add a screen shot of the GridView?

    Please note I had to update this question. I am thinking maybe I need to make a change to the method that is writing to excel file? Also I cannot change the SQL that is getting the data from the database. That SQL is coded in VB as Sqlquery = ConfigSqlStatementsRow.sqlquery

    I have a query that is run through an application. When the result is returned, a user clicks on Export to Excel button. The query result is then exported to Excel sheet.

    The problem is that when Excel sheet opens and data is loaded/exported, any column that have data with a leading zero no longer have that zero loaded. 

    How can I fix this? I do not want to ask the user to do anything extra!

    Table column are defined as follows CashLocation (varchar(6), null), NonCashLocation (varchar(6), null)

    The query run in VB code returns results that is then being exported into Excel. That query is not the problem.


    Here is the method that is writing to excel file. Is there a way to make a change to it so that when columns are added preceding zeros are preserved?

        Protected Sub WriteToExcelFile(dt As DataTable)
            'This method exports the resulting query datatable to an instance of Excel using StringWriter
            If Not dt Is Nothing Then
                Dim sw As New StringWriter()
    
                'Loop through the column names and output those first
                For Each datacol As DataColumn In dt.Columns
                    sw.Write(datacol.ColumnName + vbTab)
                Next
    
                Dim row As DataRow
                'Loop through the datatable's rows
                For Each row In dt.Rows
                    'Newline between the previous row and the next row
                    sw.Write(vbNewLine)
    
                    Dim column As New DataColumn()
                    'Loop through each column and write the cell the the stringwriter
                    For Each column In dt.Columns
                        'If the cell isn't empty write it, else write an empty cell
                        If Not row(column.ColumnName) Is Nothing Then
                            sw.Write(row(column).ToString().Trim() + vbTab)
                        Else
                            sw.Write(String.Empty + vbTab)
                        End If
                    Next column
                Next row
    
                'create an instance of Excel and write the data
                Response.Clear()
                Response.ContentType = "application/vnd.ms-excel"
                Response.AddHeader("Content-Disposition", "attachment;filename=GridViewExport.xls")
                Response.Output.Write(sw.ToString())
                Response.Flush()
                System.Web.HttpContext.Current.Response.Flush()
                System.Web.HttpContext.Current.Response.SuppressContent = True
                System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest()
            End If
        End Sub

    Here is the result of the query

    CashLocation NonCashLocation
    011                 015
    021                 025

    Here is what is exported into Excel sheet


    CashLocation     NonCashLocation

    11                     15

    21                     25







    • Edited by winkimjr2 Wednesday, October 9, 2019 6:29 PM Added details of new problem when cell has a formula
    Thursday, September 26, 2019 4:36 PM

All replies

  • Hi winkimjr2,

    Just prepend a single apostrophe ' to the columns in the SELECT statement.

    That's the standard way to handle leading zeros in Excel.

    SQL:

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, CashLocation VARCHAR(10), NonCashLocation VARCHAR(10));
    INSERT INTO @tbl (CashLocation, NonCashLocation)
    VALUES
    ('011', '015')
    , ('021', '025');
    -- DDL and sample data population, end
    
    SELECT ID
    	, '''' + CashLocation AS CashLocation
    	, '''' + NonCashLocation AS NonCashLocation
    FROM @tbl;


    Thursday, September 26, 2019 4:43 PM
  • Hi winkimjr2,

    What is the data type of CashLocation and NonCashLocation columns?


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    Thursday, September 26, 2019 6:17 PM
  • Hi winkimjr2,

    Try this:

    CREATE TABLE #Samples(ID INT IDENTITY(1,1) PRIMARY KEY, CashLocation VARCHAR(10), NonCashLocation VARCHAR(10));
    INSERT INTO #Samples (CashLocation, NonCashLocation)
    VALUES ('011', '015'), ('021', '025');
    
    SELECT ID,CAST(CAST(CashLocation AS INT) AS VARCHAR(10)) CashLocation,
    CAST(CAST(NonCashLocation AS INT) AS VARCHAR(10)) NonCashLocation
    FROM #Samples;
    
    DROP TABLE #Samples;
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    Thursday, September 26, 2019 6:54 PM
  • I don't understand what this question is doing in the T-SQL forum. This is something you should address in the Excel export in the application. And, yes, adding apostrophes may be required, but doing it in the SQL code woudl be the wrong place.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, September 26, 2019 10:00 PM
  • Hi winkimjr2,

     

    Please try following script.

     
    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
    Database=C:\yourpath\SQL Data.xlsx;','SELECT * FROM [Sheet1$]') 
    SELECT
    ISNULL (cast(sLM.CashLocation as int),'') AS CashLocation, 
    ISNULL(cast(sLM.NonCashLocation  as int),'') AS NonCashLocation
    FROM FincMgmt.dbo.sLocationMapping AS sLM with (nolock)
    INNER JOIN MinnNodes.dbo.NodeRelationships AS NR with (nolock)
    ON sLM.NodeID = NR.NodeID

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 27, 2019 6:27 AM
  • Rachel thanks for the input. The thing is that I cannot make changes to the SQL because this is inside a variable. Here is the code for how the query gets information from database table. So I do not know what I need to change so when export to excel button is click, what is exported to excel retains preceding zeros.

    I hope this will help you and others understand what the code in VB is. In vb.net code there is a method (WriteToExcelFile) used to export the query data table to an instance of Excel using Stringwriter. I did add it to the question. 

    Here is the vb.net code for the SQL  

    'get sql query & Precommands from config_Sql table   
     Sqlquery = ConfigSqlStatementsRow.sqlquery



    • Edited by winkimjr2 Thursday, October 3, 2019 1:55 PM
    Thursday, October 3, 2019 1:41 PM
  • Hi winkimjr2,

    >> "...Just prepend a single apostrophe ' to the columns in the SELECT statement..."

    Did you have a chance to try what I suggested?

    Thursday, October 3, 2019 1:51 PM
  • Hi Khabinsky,

    I do not think I can do that in the VB.Net code.  The reason is that the select statement or SQL looks like this Sqlquery = ConfigSqlStatementsRow.sqlquery.   Google search suggests In the RowDataBound, apply the style: e.Row.Cells(0).Attributes.Add("class", "text") 

     'create an instance of Excel and write the data
                Response.Clear()
                Response.ContentType = "application/vnd.ms-excel"
                Response.AddHeader("Content-Disposition", "attachment;filename=GridViewExport.xls")
                Response.Output.Write(sw.ToString())
                Response.Flush()
                System.Web.HttpContext.Current.Response.Flush()
                System.Web.HttpContext.Current.Response.SuppressContent = True
                System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest()




    • Edited by winkimjr2 Thursday, October 3, 2019 2:56 PM
    Thursday, October 3, 2019 2:12 PM
  • As I said previously, this is not an SQL question. SQL returns data, and it returns the data as-is. It has no knowing of where the data is presented, nor should it have. You should add the apostrophes when you write to Excel. Or you should tell Excel "hey, baby, this is cell is a string, so just take what you get honey".

    If you want help with these details, there are better forums for this. Just don't change the SQL code, because it's doing what it should.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 3, 2019 2:49 PM
  • Make sure that the SQL query returns these numbers as unchanged strings, not as numbers.

    Then adjust your program. Instead of:

       sw.Write(row(column).ToString().Trim() + vbTab)

    try this:

       sw.Write( "=""" & row(column).ToString().Trim() & """" & vbTab)

     


    • Edited by Viorel_MVP Thursday, October 3, 2019 3:16 PM
    Thursday, October 3, 2019 3:15 PM
  • Hi Viorel, I do not know which words to use to express my happiness right now. You should see my face with a huge smile. You have ended my frustration that I went through the last two weeks! Thank you!
    Thursday, October 3, 2019 4:32 PM
  • Hi winkimjr2,

    Glad to hear that everything is working for you now.

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread. 


    Thursday, October 3, 2019 5:45 PM
  • I did
    Thursday, October 3, 2019 6:18 PM
  • Hi winkimjr2,

    It is still not done.

    Currently it is as "Proposed as answer by Rachel_Wang"

    This thread is still not closed.



    Thursday, October 3, 2019 9:33 PM
  • Hi ,

     

    Thank you for your reply.

     

    We are glad to hear that  you have solved your issue ,please kindly mark the helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 7, 2019 9:04 AM
  • Hi Viorel_MVP, even though now when data is exported to Excel the leading zeros are not removed because data is sent as text, there is a potential problem when a cell contains a formula. Here is a message I received from one user with concerns regarding the cells that contain a formula. I’m not sure if exporting the data as string formulas is going to work (“=[content]”).  I explored doing this when I was trying to correct the query from the SQL side and my concern was that the information is actually stored within the output.  I am not sure what processes people use the output in downstream, but it could interfere with those (e.g. comparing on a value or sending the file to another process).  Text files actually contain the formulaic values too. How can this be addressed?

    Wednesday, October 9, 2019 6:23 PM

  • I also export date into excel. Could you please share me more information?

    Thursday, October 10, 2019 7:50 AM
  • I like to advise you again that this is a forum for T-SQL questions, and not a forum for Excel questions. Your SQL query should return the data plain and simple, and your VB code should deal with the Excel tribulations.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 10, 2019 7:57 AM
  • Sounds great.
    Friday, October 11, 2019 2:36 PM
  • Just to be perfectly clear. 

    The problem you are having is with Excel, not SQL Server or the file you are creating.  

    What you are fighting is a feature of Excel trying to "help" you. When you load a text file into Excel, it reads the first few rows of the file and "auto types" the columns for you.  There is no way to prevent this functionality in Excel.  When it sees values that look like numbers, it formats the column as "numeric", which removes the leading zeros.

    Please see:

    https://support.office.com/en-us/article/keeping-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7

    Friday, October 11, 2019 2:44 PM
    Moderator