A quick way to paste MySQL output to Excel
I don’t normally swim in Excel waters, so when I actually figure out something new, I thought it best to share and document it.
When I test my code, I copy output quite often into an open VIM window. Since my scripts typically end up writing to a DB (a mysql DB in this case), I typically have a sql prompt open ready to query for the results of my test. The results are something like this:
+---------+-------+-------+------------+------------+---------+ | page_id | m_imp | t_imp | m_elig_vis | t_elig_vis | n_users | +---------+-------+-------+------------+------------+---------+ | 15 | 88172 | 27139 | 71689 | 21044 | 10715 | | 7498 | 3132 | 2704 | 2630 | 2322 | 1006 | | 7727 | 0 | 1 | 0 | 1 | 0 | | 7732 | 0 | 0 | 0 | 0 | 0 | | 9870 | 181 | 170 | 165 | 143 | 54 | | 9871 | 431 | 518 | 323 | 370 | 186 | | 12385 | 0 | 0 | 0 | 0 | 0 | | 12924 | 0 | 0 | 0 | 0 | 0 | | 25857 | 0 | 17423 | 0 | 15718 | 6837 | +---------+-------+-------+------------+------------+---------+
That’s a typical MySQL result set. When I want to send it to excel, the conventional way is to rerun the query converting it to a csv and outputting the results in a /tmp directory (yes, I’m Mac/Linux based. I don’t know where MySQL on windows writes an OUTFILE to). I would then have to copy the file to a local directory on the MySQL server (usually in a colo or off-site), then scp the file to my local machine importing into Excel using Data->Get External Data. This method is cumbersome and not very quick.
It would be much smoother for me to copy the text output as above into my clipboard and ‘paste’ it into Excel. Then I can format it and run a few quick calculations faster than writing a php script or a mysql query. If you do copy the above table, as text, and paste it into excel, the output is a single column where every linefeed is a separate row. Having to hand-edit each cell is un-acceptably tedious, time consuming and error-prone.
What if, we could simply paste the plain text onto a column, then write a quick formula to convert that single column into multiple columns.
One relevant row in the column as the following format:
| 15 | 88172 | 27139 | 71689 | 21044 | 10715 |
What if I could write a formula which pulled out 15 to one cell. and 88172 into another cell. I need to create a formula to get pull out the data between the ‘|’ characters. If the formula knew to pull out the nth’s field, then I could copy such a formula across a worksheet which, in turn, can then be used as a source to populate other worksheets. So that’s simple enough.
I could use the =MID(text,start,count) function. The problem is, where do I start and what should I use as the count. The key is, being able to find the ‘nth’ occurrence of the ‘|’ character. Thanks to tips.com, that info is right here.
The formula I chose is of the form: =FIND(CHAR(1),SUBSTITUTE(A1,”B”,CHAR(1),3)) – which finds the 3rd occurrence of the character ‘B’. The ‘3’ in the formula can refer to a cell instead. And “B” can be changed to our delimiter: ‘|’.
The rest is pretty straightforward following from above.
The start of the Nth field is the position of the Nth delimiter + 1.
The length of the Nth field is the position of the (N+1)th delimiter minus the position of the Nth delimiter – 2.
1. Opened up a new Excel spreadsheet. copied my text (including tabular data) and pasted it on cell A2.
2. Then on I numbered B1, C1, D1, … as 1,2,3, etc. – this should correspond to the number of columns I have.
3. This then allowed me to put the following formula on cell B2
=MID($A2,FIND(CHAR(1),SUBSTITUTE($A44,"|",CHAR(1),B$1))+2,FIND(CHAR(1),SUBSTITUTE($A44,"|",CHAR(1),(B$1+1))) - FIND(CHAR(1),SUBSTITUTE($A44,"|",CHAR(1),B$1)) - 2)
I hope this helps. Saved me a ton today.