And it was over 50 columns, so it was a tedious hour, column-widening, format-changing, blank-column-hiding. (And whether they even were blanks needed checking; it was over 4000 rows, so a lot of scrolling around needed to conclude it had some data, but was 98% blank. This was over and over.)
I wrote up a tool to make the job easier. A lot of it just reduces a remote, awkward menu click to a convenient button.
Worse, the final result needed to be uploaded to a database table in it's own right, so text had to be quoted, dates needed to be right, the INSERT statements had to have about 40 columns assembled into one long string with a huge formula.
This is the tool I developed for a very specific little problem, but it may come up again and again.
Download the SQLtools Spreadsheet
Clicking on COPY SHEET makes a new worksheet, same name as the old, with
"_SQL" added to the sheet name.
It also puts in the contents of the top row cells as comments on those cells, so that if the column name is wider than the column, you can just touch your mouse to it to see the column name.
The first column is selected, and shows in blue. The arrow keys move the focus of the tool to columns right and left. Whichever column has focus shows the column-letter in huge text, and the column name (from Row#1) above that.
The blank space may show messages, like "May need widening!" if it sees "####",
or "Column 99% blank. Click here to hide."
Excel is super-fast at finding blank cells, if you use the Excel function instead of your eyeballs! It takes milliseconds to check what percentage of that column is just blank. If it's over 90%, what little data there is, may be stray marks. Excel does this so fast, I made it automatic with every column-move with the arrow keys.
So you just click right on the "Mostly blank" message itself to both hide the column, and jump the "column cursor" one column right to continue on with cleaming up the table.
This is especially handy when there's a whole group of blank columns. It's barely seven seconds to do the seven clicks to hide them all.
The "HEAD" and "WRAP" buttons just save you from going down into menus to freeze the top row, for scrolling, or making huge text columns readable by wrapping the text around so the row becomes several text-lines deep. Here's both:
Column F contains text up to 1855 characters wide, but when dumped to spreadsheet, just 30 or so characters are showing, there's no way to read it.
After 5 quick clicks on the "10" button for "Widen by 10", at least the
column is as wide as the original comments form, but there's still several lines of text per entry.
After clicking the WRAP button, Column F now looks like the original comments form, and the analyst can read it. Clicking WRAP again, will reverse the process, taking it back to one-line deep. (As a happy accident, column G is now readable without much fiddling with width.)
The effects of "By 1", "5", "Narrow by 5" are assumed to be obvious. Try them!
This has a very specific effect. It would have done nothing to Column F, above. It is a "Smart Widen" for number, and DATE-type, columns that have been turned into "#####" or "1.2E+7" because the column is too narrow for the numbers.
If you have either of the two cases (exponents on the left, #### on the right), and click the WIDEN button, you'll see the column widen one character at a time until the number or date becomes visible, then there's a short wait while it checks all the way to the bottom to be sure they're all good.
This saves you from the typical experience, of widening it "enough", then scrolling down a page, to find an even larger number, and have to widen it again.
After WIDEN:
NOTE: Testing showed you can WIDEN a column to show a date, discover the DATE format included hours and minutes, then change the DATE format, making the column TOO wide. The WIDEN button does not narrow columns to fit, sorry. That's when the "NARROW by 5" button was invented!
For an alternative, the "#####" date-type column at left is fixed by just clicking on the "SET DATE FORMAT" button, because the YYYY/MM/DD format is narrow enough to fit without any more widening.
You can change the text above the SET DATE FORMAT button to any legitimate Excel format string, with "HH:MI" being what to add for minutes and seconds. "MMM" will give you the month abbreviation, MONTH the full word.
Obviously, this button merely saves you some mouse-clicks, navigating to the "Set Cell Format" dialogue box for the selected column.
This has only a little bit of "AI" in it, a few IF statements. If the column seems to be all numbers, it looks for the largest number of places before and after the decimal, and provides an Oracle NUMBER(width,decimal-places) value in the text below.
If text, it finds the largest, and offers a "VARCHAR2(widest)" entry. If it seems to be a date, that's just DATE.
Worth noting: if the guess is "VARCHAR2(4000)", then the text cells go all the way up to the maximum size of an Excel cell, and your spreadsheet may have chopped off part of a database retrieval or other data-conversion!
This program creates a new worksheet, that is the current sheet's name with "_create" added on, and writes a "CREATE TABLE sheetname " opening line at top. YOu might want to change the sheet name to the tablename you want to create with an upload, or you just change this later.
The program pops up a second window, on that sheet, so you can see the lines of table definition added to the CREATE TABLE statement as the selected column goes through every visible, that is, every not-hidden, column in the table.
When done, you can usually just copy it from the sheet - don't miss the commas in column B - and paste to the SQL program, or text file, of your choice.
Large text cells may have single-quotes in them already, shown by arrow.
All of the cells have single-quotes around them, and the quote in "Builder's Road", has been doubled. The cell can now just be a comma-separated entry in an INSERT statement.
The program reads the Excel date format you are using, and creates an Oracle TO_DATE statement using the same format, so you can use any date format you want, and your date cell will be able to INSERT properly.
If you've just used the buttons at top to look through the whole table, are satisfied with the columns, then this is the only button you need to prepare the table to be INSERT statements. Numbers will be left, but DATE and CHAR columns will be prepared.
Also, blank cells are turned into the word NULL, because some SQL database accept two commas with nothing between them, Oracle doesn't. You have to put in NULL for blank columns.
An INSERT statement may go over the 4000-character limit for one Excel cell. If so, the excess is put into as many cells to the right as needed. However, cutting and pasting from multiple cells to a text editor or SQL tool may not seamlessly join the pastes together - you might need to delete spaces.
Especially for such cases, the simpler solution might be to use the other product of this program, which is a file named "sheetname.SQL" in the current PC directory.
With Excel 2013, Microsoft made a call nobody asked for: they switched Excel from "Multiple Document Interface", MDI, where there is one Excel window, and all the open workbook windows appear inside that - to "Single Document Interface", SDI, like Word, where every workbook gets its own Windows window.
With SDI, a plug-in that opens up a dialogue box, like mine, "owns" that dialogue, and when the user clicks on the worksheet the dialogue is processing, the dialogue disappears "under" the separate Windows window. So you can't use the dialogue without separating it from the window it's working on.
The workaround, is to just open the SQLtools.xlsm workbook as well, and the macros make them both share the screen. It's not as good, but it's workable. So SQLtools is just a workbook, that you open at the same time, not an Excel Add-in.
There seems to be no solution for the problem on the web, and one must conclude that Microsoft, really, really doesn't care about Excel Add-in developers.