none
How to convert the HTML Body to .CSV file. RRS feed

  • Question

  • Hi,

    I have to convert the HTML body to .csv file which will be used as a attachment in mail box...

    Please help. 

    This bold mark is the HTML body which i have to convert into .csv file.

     

    EXEC msdb.dbo.sp_send_dbmail @profile_name='ERPMAILS',                 
    @recipients='harish@vaarisolutions.com',    
    @subject= @Subj,                
    @body=@BODYText, @Body_format = 'HTML'  ,@file_attachments = @BODYText

    Thanks

    Harish Waghmare

    Wednesday, January 15, 2020 9:56 AM

All replies

  • https://www.convertcsv.com/html-table-to-csv.htm

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 15, 2020 10:05 AM
    Answerer
  • In T-SQL? Better use PowerShell: https://www.example-code.com/powershell/html_table_to_csv.asp

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 15, 2020 10:13 AM
  • If you have an HTML which is also a valid XML, then probably you can use the XML features of SQL Server. Show some details or example about your string.

     

    Or maybe you can generate the CSV in other parts of the code where you build the body.

    Wednesday, January 15, 2020 10:16 AM
  • Do you have any samples to do this...
    Wednesday, January 15, 2020 11:38 AM
  • Hi Harish,

    As Viorel pointed out, if your HTML is in reality XHTML you can easily handle it via SQL Server XML data type methods and XQuery.

    The nodes() method is most useful in your case, It will convert XML into a rectangular data set.

    Useful links:

    Wednesday, January 15, 2020 2:20 PM
  • Hi Harish Waghmare, 

    Thank you for your issue. 

    As I know that it might be difficult to achieve your requirement . In SQL , I try to use xml type data to reproduce your requirement . Please check .

    DECLARE @string varchar(max)
    DECLARE @xml xml 
    SET @string='
    <?xml version="1.0" encoding="utf-8"?>
    <TickerBrokerStandardDateLineitem>
      <TickerBrokerStandardDateLineitemValues>
        <TickerBrokerStandardDateLineitemValue>
          <TabName>Consensus Model</TabName>
          <StandardDate>2010 FY</StandardDate>
          <XFundCode>TRIN</XFundCode>
          <StandardLineItem>Total Sales</StandardLineItem>
        </TickerBrokerStandardDateLineitemValue>
        <TickerBrokerStandardDateLineitemValue>
          <TabName>Consensus Model</TabName>
          <StandardDate>2011 FY</StandardDate>
          <XFundCode>TRIN</XFundCode>
          <StandardLineItem>Total Sales</StandardLineItem>
        </TickerBrokerStandardDateLineitemValue>
      </TickerBrokerStandardDateLineitemValues>
    </TickerBrokerStandardDateLineitem> ' 
    
    set @xml= stuff(cast(@string as varchar(max)),1,charindex('>',cast(@string as varchar(max))),'')
    
    select  T.C.value('TabName[1]','varchar(max)') as TabName,
            T.C.value('StandardDate[1]','varchar(max)') as StandardDate,
            T.C.value('StandardLineItem[1]','varchar(max)') as StandardLineItem,
            T.C.value('XFundCode[1]','varchar(max)') as XFundCode
    		into temp_table
    from    @xml.nodes('/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue') as T ( C )
    
    EXEC xp_cmdshell 'sqlcmd -E -Q "select * from yourdatabase.dbo.temp_table" -o C:\yourpath\file.csv '
    /*
    TabName            StandardDate       StandardLineItem    XFundCode
    ------------------ ------------------ ------------------- -----------
    Consensus Model    2010 FY            Total Sales         TRIN
    Consensus Model    2011 FY            Total Sales         TRIN
    */
    

    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.

    Thursday, January 16, 2020 6:16 AM
  • Hi,

    Thanks for your solution... Possible to convert the HTML to XML in stored procedure so that i can use the same to convert into .csv file.

    Thanks 

    Friday, January 17, 2020 7:22 AM
  • Hi Harish Waghmare,

    Thank you for your reply. 

    Sorry that html is not a data type in SQL Server . And as Yitzhak said that 'if your HTML is in reality XHTML you can easily handle it via SQL Server XML data type methods and XQuery', Please check .

    Please kindly mark the helpful replies 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.

    Friday, January 17, 2020 7:36 AM
  • Hi,

    Now i had created the Temporary Table and want to transfer the data to .csv.  I had used bcp command but the header/column information is not coming...

    Please help me in this

    Monday, January 20, 2020 6:20 AM
  • Is there any data in your csv?

    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, January 20, 2020 7:04 AM