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...
This ought to get your thoughts moving. Thanks very much for reading. Be safe out there.
Tracy Barber
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 SubThis ought to get your thoughts moving. Thanks very much for reading. Be safe out there.
Tracy Barber
