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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
To start, In cell A1 you have "Antwerp Nṟ 1". with underscore
In cell A19 you may have "Antwerp Nr 1" with out underscore.

At the end, this column you want to go to a field in mysql.

1. what do you do in between. How do you take it to mysql? a) through export as csv b) open connection in vba and insert to mysql? explain what you do.
2. after the funny characters get inserted to mysql, what are you going to do with that?
3. What does this column represent. customer name, location name, part name ? Other than cell A1 and A19 in the above example may create duplicate, Why the duplicate is a problem. Are there two different customer or locations or parts or same but the table design creates a duplicate problem?
4. what is the character set, collation of this data field in your mysql table.
5.if "Antwerp Nr 1" remains "Antwerp Nr 1" and "Antwerp Nṟ 1" becomes "Antwerp N-100-1" what problem that will pose?
6. what is the locale of your windows machine? for eg Us-en. in otherwords, which country you are in or what is the default language of the country this app is used.
 

TLB_Excel

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
As per output, once it is in mySQL, I'll use php to display it. I need the underlines, because there's no way to distinguish between these 2 situations per Antwerp above. This is the Belgian canceller issue for their employees in the railroad business. The cancels are applied to stamps that are applied to waybills. Certain cancellations - chamfers - have this problem. Not all cancellers were created equal, so I need to distinguish between them. Could be more than 1 canceller at work at any time. (Think of U.S. mail being cancelled.)
To start, In cell A1 you have "Antwerp Nṟ 1". with underscore
In cell A19 you may have "Antwerp Nr 1" with out underscore.

At the end, this column you want to go to a field in mysql.
Yes. I may use MS Access, because LongText allows me to use Rich Text and I can underline single characters. At least it's a start.

1. what do you do in between. How do you take it to mysql? a) through export as csv b) open connection in vba and insert to mysql? explain what you do.

CSV stinks, although some people state it can be done with Libre Office. Excel does not export styles, like "_", bold, italics, etc. The sample snippet above is the start of a vba conversion into HTML, because it keeps the styles.
2. after the funny characters get inserted to mysql, what are you going to do with that?
Add to the DB! This is to be shared with stamp collectors who favor these stamps and cancels.
3. What does this column represent. customer name, location name, part name ?
Railroad station Depot, person working the desk, different department, etc. They had cancellers for everything except flushing toilets.

Other than cell A1 and A19 in the above example may create duplicate, Why the duplicate is a problem. Are there two different customer or locations or parts or same but the table design creates a duplicate problem?
Duplicate? I can import them into mySQL but the styles are gone. All 6,500 records or more. No errors.
4. what is the character set, collation of this data field in your mysql table.

Was utf8, but there's too **** many places you have to place character set and collation reminders it's boggling. I even have code to change the entire DB over to utf8mb4 but even though the pundits say it works, it doesn't look like it.

5.if "Antwerp Nr 1" remains "Antwerp Nr 1" and "Antwerp Nṟ 1" becomes "Antwerp N-100-1" what problem that will pose?
Novices who will read this will freak out. There are other words like Caal (canal) and so forth I have to parse. A small quagmire. I though of find and replace using codes that php would read but that's a big step in a bad direction. Nobody would be able to send me their cancels. They'd give up. An alternative is create a book in Word or Publisher and use tables but that would be tedious to expand and contract pages, considering there'll be text besides the data. Such as:

Other Text

Data
Data
Data

Other Text

When displaying it. Tables in Word are a pain in the butt for large amounts of data.
6. what is the locale of your windows machine? for eg Us-en. in otherwords, which country you are in or what is the default language of the country this app is used.

U.S. English. Some of the data have accents as well and they come across fine. It could be French or Flemish on the cancel.

Thanks for the reply. Any hints would be appeciated.

Tracy
 

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
Thanks for explaining. I can not promise success, but will give it a try.
One last question, how the data arrives to you before you start your work. does the sender give you the data via txt/csv/xls file/email/website
 
Last edited:

TLB_Excel

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

ADVERTISEMENT

I have been entering them myself. In Excel. This was a 6 credit college project that I realize had all the makings of a masters degree. I did it to finish my 4 year degree. 17 tables, all normalized. Took about 6 months to get where I was when ended.

There are collectors out there that would love a comprehensive overview of these cancels for their collections. I would ask them for their cancels to fill in where I'm lacking. I'd ask them to send them as .txt / .csv / .xls and I'd have to convert them to my setup.

This information would be offered for free, but it is the size of a book. It is a niche market, to be sure, but it has been a serious challenge because I had to learn the basics of postgreSQL, php, IDEs, etc. from scratch. I have programmed in Basic, VBA, RM and IBM COBOL, RPG, dBase II, III, IV, rBase, FoxPro, Access, c, C++, amongst others. I'm just very rusty. This has given me a situation where I need a solid response from the output that will not blow up on me as it already has.

Tracy
 

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
You do not need anything. Excel does it all for you with out even asking, atleast for me.
I created an excel file. it has "Antwerp Nṟ 1" in A1.
I created a tbl in mysql with 2 columns. 1. dummykey autoid and 2. a varchar field with utf8_general_ci collation for that field.
VBA Code:
sqlstr1="INSERT INTO test_utf8( my_cellvalue) VALUES (" +chr(34)+ cells(1,"A").text + chr(34)+");"
conn1.Execute sqlstr1
and this unicode string "Antwerp Nṟ 1" is in the mysql table.
I did not require anything fancy. I am not sure why it is not working for you. My id is open to all for conversation.
click on the email icon next to your id in the green menubar at the top and start a conversation and put my id or
click on my avatar on the left of this and start a conversation, if you need further help to make it work for you.
 

TLB_Excel

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

ADVERTISEMENT

OK... I think my collation "discombobulation" has caused the problem. I can handle the parsing of fields, according to your statement. The database should be utf8_general_ci when I create it. Also, each text field with this issue should be the same. There are only a few fields in the whole database that are like this.

So, lemme see... in VBA

1) Open Excel worksheet.
2) Set range.
2A) Open MySQL DB.
3) for Excel rows
4) for Excel columns (5 of them)
5) Build / parse string(s) as you showed
6) Insert text in SQLstr into field(s)
Execute, of course... repeat for all 5
7) next column
8) Commit or auto-commit
8) next row
9) At EOF close all, set = nothing

Display / browse SQL table. At this point, I can play with all MySQL functions and hopefully, the underline won't disappear! :) That would save me from php torture of putting <U> around the character(s) in the field.

I'll try to make it generic so that someone else could use it if they needed it.

Thanks! I'll post a response when finished.

Tracy
 

TLB_Excel

New Member
Joined
Mar 31, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
thx for replying.
You're welcome.

It didn't work in as many ways as I've tried. **** Microsoft. Sheesh... allow us to export format styles. Creates a wonderful .html output. I just can't get vba to show me in the immediate window what the REAL text is, underlined. I may be missing some Developer's Tools Reference or some such obfuscation that's always in the path of success.

I have thought of a long drawn out way of adding another field with the underlined parts marked so I can <U> when I display them in a web page. It would be so darn much easier without the kludge, to be sure. It seems that I can't find a database or language that allows me to move the data out of Excel. I made a BIG mistake doing it that way.

Another way to do it is to create a book with Publisher but that would be almost as bad as the kludge mentioned. There just has to be a way to do this by someone. Too many php / vba tool kits out there and some of them simply don't work right.

If you can add little more to the code you sent already, it would be appreciated. I got the vba to read the spreadsheet and can read a mysql table but your example with simply putting "" around the .text is not working. It's either the receiving field that's wrong type, collation or something -OR- the Excel spreadsheet is goofy somehow.

I wonder if XML with a schema will keep the text as it is?

Thanks again.

Tracy
 

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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