When exporting Excel, I have underlined characters in columns that will not export to .CSV and will to HTML

TLB_Excel

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
I have a spreadsheet in Excel 2013 that has cells with underlines in them. Style stuff. I need to get those out of Excel and into MySQL. Although 30 years in the field, I still get truly stumped.

For example, cell A1 has "Antwerp Nṟ 1" in it and the "r" is underlined. There is no way around leaving the underline out because there could be an "Antwerp Nr 1" without the underline also. So, both cases are present. This is a huge spreadsheet with about 6,500 entries and will get much bigger. Thought for sure Excel would come through with a Unicode / utf8 / utf8mb4 export, but no... Save As will not preserve the style. I even tried Open Office and Libre Office.

It exports to HTML fine and maybe I can parse that after I get it out of Excel. My MSQL DB is utf8 right now and although I can put "special characters" in place of the underlined character that php will process it, but if anyone else helps with the project, it would be painful for them to follow my lead.

I am going to try VBA to extract it because of my time with MS Access (underlines the whole field) and get the data correct and I have example code of how convert to HTML.

I am also quite good parsing data, but it's the final storage from Excel to text that buggers me.

I was thinking of trying a combined HTML <U>"letter"</U> combo with "," to make a text file that will resemble a .CSV file.

I have asked several people / companies and nobody has helped. I would be willing to turn over the code free when finished if there's an answer to this. It's not the code I'm afraid of, it's the quagmire of exporting.

Here's my "start", borrowing the code from another place...

VBA Code:
Public Sub ExportToHTML()
    Dim Filename As Variant
    Dim TDOpenTag As String, TDCloseTag As String
    Dim CellContents As String
    Dim Rng As Range
    Dim r As Long, c As Integer
   
'   Use the selected range of cells
    Set Rng = Application.Intersect(ActiveSheet.UsedRange, Selection)
    If Rng Is Nothing Then
        MsgBox "Nothing to export.", vbCritical
        Exit Sub
    End If
   
'   Get a file name
    Filename = Application.GetSaveAsFilename( _
        InitialFileName:="myrange.htm", _
        fileFilter:="HTML Files(*.htm), *.htm")
    If Filename = False Then Exit Sub
   
'   Open the text file
    Open Filename For Output As #1
   
'   Write the tags
    Print #1, "<HTML>"
    Print #1, "<TABLE BORDER=1 CELLPADDING=3>"
   
'   Loop through the cells
    For r = 1 To Rng.Rows.Count
        Print #1, "<TR>"
        For c = 1 To Rng.Columns.Count
            Select Case Rng.Cells(r, c).HorizontalAlignment
                Case xlHAlignLeft
                    TDOpenTag = "<TD ALIGN=LEFT>"
                Case xlHAlignCenter
                    TDOpenTag = "<TD ALIGN=CENTER>"
                Case xlHAlignGeneral
                    If IsNumeric(Rng.Cells(r, c)) Then
                      TDOpenTag = "<TD ALIGN=RIGHT>"
                    Else
                      TDOpenTag = "<TD ALIGN=LEFT>"
                    End If
                Case xlHAlignRight
                    TDOpenTag = "<TD ALIGN=RIGHT>"
            End Select
           
            TDCloseTag = "</TD>"
            If Rng.Cells(r, c).Font.Bold Then
                TDOpenTag = TDOpenTag & "<B>"
                TDCloseTag = "</B>" & TDCloseTag
            End If
            If Rng.Cells(r, c).Font.Italic Then
                TDOpenTag = TDOpenTag & "<I>"
                TDCloseTag = "</I>" & TDCloseTag
            End If
            CellContents = Rng.Cells(r, c).Text
            Print #1, TDOpenTag & CellContents & TDCloseTag
        Next c
        Print #1, "</TR>"
    Next r
'   Close the table
    Print #1, "</TABLE>"
    Print #1, "</HTML>"

'   Close the file
    Close #1
   
'   Tell the user
    MsgBox Rng.Count & " cells were exported to " & Filename
End Sub

This ought to get your thoughts moving. Thanks very much for reading. Be safe out there.

Tracy Barber
 

TLB_Excel

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
the vba immediate window or watch window will NOT display underline but the contents in all variables are 100% guaranteed unicode. every thing u need is in post 6. post mysql table create stt and vba code, xxxx the password and server name in connect string. i will debug. no need for table data. i did not save my test. will recreate when time permits. will be busy this week
Thanks. Normally, this would be a simple task, but with speed bumps like utf8, unicode, utf8mb4, my.ini, php.ini, vba references (like the ADO libraries, etc.) it gets a wee tad whacky.

I don't want to do a dine and dash where I get the answer and vamoose before the internet connection is cold. I don't play like that.

Talk to you when you have time. I'll keep plugging at it from a different angle or 2.

Tracy
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

TLB_Excel

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
jsb...

Finally, some time ago, I finally got a recipe that works. It works in MS Access 2010 and 2013. Simply importing the Excel spreadsheet will not keep the style formatting. But, I figured out how the MS people are rolling... You have to create the table to place the "fixed" data. Then you have to parse row by row, cell by cell, character by character. Memo field or Large Text, depending on the version. 2013 opens it fine and converts the Memo fields into Large Text with a Rich Text / HTML format if you want it. I did! Don't know if I'll port it to mySQL or not. Have to decide - I have the runtimes for Access 2010 2013. Might be easier and send out a database to those who want it instead of maintaining a web presence...

What I'm saying is that during the import direct from Excel fails - don't know why and the replacement of field by field worked like a cut and paste.

I was too darn rusty to come up with something simple the first time around. I chopped out 2 "sections" of the process and merged my checking in 1 process. This too can be consolidated with a function instead of a case structure and 5 choices. I learned quite a bit getting back into the mix. If you're interested what my VBA is, I'd be happy to post it. It could be modified greatly and I may do so, but after a break from mind-bending jumps through VBA land. Been a long time.

Thanks for the helping with juggling of my gray matter... LOL!

Tracy
 

Watch MrExcel Video

Forum statistics

Threads
1,130,108
Messages
5,640,139
Members
417,127
Latest member
shakilk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top