Parse a HTML Table into a ListViewhttps://www.joellipman.com/articles/web-development/html/parse-a-html-table-into-a-listview.html

30 March 2013 StringSplit,ReturnedHTMLTableCols,ReturnedHTMLTableColString,| } LV_ModifyCol() Method #2 This method only uses two columns with the label being a concatenation of the value of the first column and the column heading....

• Type: Article
• Author: Joel Lipman
• Category: Hypertext Markup Language
• Language: en-GB

SSIS Skip Rows in Excel Source filehttps://www.joellipman.com/articles/microsoft/ssis/ssis-skip-rows-in-excel-source-file.html

04 April 2013 NOT the first column of the data sheet (eg. F1 = B11) !!! Additional We add a conditional split task to the data flow which only passes through rows from the Excel sheet which are not blank/null. Browsing the net, I found this is sometimes also used for...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

Joes Syntax Styler (JSS)https://www.joellipman.com/component/content/article/joes-syntax-styler-jss.html?catid=40

06 April 2013 on getting it to recognize more programming languages so that I can use it on my own site. Limitations Recognizes SQL only Proposed Enhancements: Recognize PHP Recognize HTML Recognize AutoHotKey (AHK) Recognize CSS For this reason, please do not...

• Type: Article
• Author: Joel Lipman
• Category: Product Documentation
• Language: *

MS Excel - Sort pivottable column headings by datehttps://www.joellipman.com/articles/microsoft/excel/sort-pivottable-column-headings-by-date.html

12 April 2013 a string: Another alternative to round brackets, square brackets! Additional: I have made this work with the parentheses but only after refreshing ALL DATA in the spreadsheet and reconfiguring which fields go in "Report Filter", "Column Labels", "Row...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB

Improve Default Joomla Search https://www.joellipman.com/articles/cms/joomla/improve-default-joomla-search-heuristics.html

07 May 2013 and Wikipedia (or near enough). How? I'd like to use various techniques, and the first part of this article (Stage 1) is the only part you need to modify as this will improve your Joomla search results by giving priority to articles where the search...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

T-SQL concatenate an incremental row numberhttps://www.joellipman.com/articles/database/t-sql/t-sql-concatenate-an-incremental-row-number.html

24 May 2013 functionality with an ORDER BY which seems to work somewhat. But using the code above still produces an error which can only be seen when using more records and more employees. PARTITION BY So a sort of "group" action will allow this if we add...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

htaccess Rewrites SEF URL and submits to PHPhttps://www.joellipman.com/articles/web-development/php/htaccess-rewrites-sef-url-and-submits-to-php.html

05 September 2013 for possible Code Injection Do NOT allow the use of apostrophe or double-quotes, convert these to a numerical representation only if you need to convert them back later (eg. 034, 039). Do NOT allow any punctuation you don't use in your site structure....

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

Creating a custom form field type for Joomla XML fieldsethttps://www.joellipman.com/articles/cms/joomla/creating-a-custom-form-field-type-for-joomla-xml-fieldset.html

25 October 2013 variables. Why? In Joomla, the XML type of "sql" is extremely limited. The component I'm making has to ensure user's can only see their own projects and not everyone's: SELECT * FROM #__projects ORDER BY name -- yields all projects irrespective of which...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Generate a Timesheet in MySQL https://www.joellipman.com/articles/database/mysql/generate-a-timesheet-in-mysql.html

05 December 2013 we have to deal with still: We have lost the rows with days that have no events. We need to group by days so that each day only outputs one row (see Thursday). We want the earliest time and the latest time per morning and afternoon slot. SELECT DATE(...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

T-SQL: Parse an XML valuehttps://www.joellipman.com/articles/database/t-sql/t-sql-parse-an-xml-value.html

27 March 2014 'nvarchar(50)') FROM Event_XML.nodes('/*/*[contains(local-name(.),"SURNAME")][1]') AS r(c) -- added [1] to only get 1 result returned ) AS Node_Surname FROM [dbo].[XML_EVENTS] Search if Node or value contains string: -- exist() returns 1 or 0 if a node...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

SSRS Stop Scrolling Behindhttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-stop-scrolling-behind.html

08 April 2014 display went behind my report and instead the whole report acted as a header on top of the scrolling content and would only ever display, the top rows of the dataset. Why? A lot of articles out there on the net are offering solutions that do the exact...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

SSIS: How to loop through multiple flat files as data sourceshttps://www.joellipman.com/articles/microsoft/ssis/ssis-how-to-loop-through-multiple-flat-files-as-data-sources.html

13 June 2014 SSIS package intended to take a text file as its source and to populate a database table with this data. Note that this only works if all the text files to be used as source data have the same number of columns and where the column widths match. How?...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connecthttps://www.joellipman.com/articles/database/mysql/mysqldump-got-error-2049-connection-using-old-pre-4-1-1-authentication-protocol-refused-client-option-secure-auth-enabled-when-trying-to-connect.html

28 July 2014 issue as I can connect to my database using MySQL Workbench (I have enabled the old authentication protocol). The error ONLY appears when I try to "data export" the database. Why? The quick solution for everyone else is to change/reset the password of...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

SITS: Export field code and namehttps://www.joellipman.com/articles/web-development/xml/sits-export-field-code-and-name.html

12 August 2014 to come through the XSLT but watch your datatypes Notes: We did not find a way to return the code when the lookup failed, only an alternative message "*LookupFailed*".

• Type: Article
• Author: Joel Lipman
• Category: Extensible Markup Language
• Language: *

Add attribute xsl:nil=true on empty elements using XSLThttps://www.joellipman.com/articles/web-development/xml/xslt/add-attribute-xsl-nil-true-on-empty-elements-using-xslt.html

18 August 2014 value: -- What I have: // -- What I want: Why? Outputting from SITS:Vision to our staging environment, the application would only output blank values using single tags so we had to find a place to introduce it. On strings this has little worth, but on...

• Type: Article
• Author: Joel Lipman
• Category: XML Stylesheet Language Transformations
• Language: *

A quick run through of setting up an export process in SITShttps://www.joellipman.com/articles/web-development/xml/a-quick-run-through-of-setting-up-an-export-process-in-sits.html

21 August 2014 File > Export... Type XET as the Data format Double-click in the Template field to see relevant XETs (in our demo, this will only display XETs relevant to CAP) Specify a destination table OK to create/overwrite Testing: XET is not available as an export...

• Type: Article
• Author: Joel Lipman
• Category: Extensible Markup Language
• Language: *

Basic Android App using Google Maps and Current Locationhttps://www.joellipman.com/articles/google/androidos/basic-android-app-using-google-maps-and-current-location.html

10 October 2014 your app and not from your computer (as other people don't use your computer to access the service). If your fellow testers only see a grey map, just double-check the key is the right one given to Google Maps. Follow On Article/Sequel Next: Article on...

• Type: Article
• Author: Joel Lipman
• Category: AndroidOS
• Language: *

MessageBox or AlertDialog in Android Javahttps://www.joellipman.com/articles/google/androidos/messagebox-or-alertdialog-in-android-java.html

20 October 2014 popup which I use as a message to the user for debug purposes. Why? It's similar to javascript's message box or confirm box, only Android let's you redesign the dialog. For my purposes I have used an XML as the layout. How? This displays a standard...

• Type: Article
• Author: Joel Lipman
• Category: AndroidOS
• Language: *

Android: Keytool and Google Maps displaying greyhttps://www.joellipman.com/articles/google/androidos/android-keytool-and-google-maps-displaying-grey.html

31 October 2014 beta Java Development Kit v1.8.0_20 What? The article below serves as a checklist to ensure I don't release an app that only displays maps to myself (again). Why? This took me a while to figure out so hopefully this will help others. How? The gist is...

• Type: Article
• Author: Joel Lipman
• Category: AndroidOS
• Language: *

Excel: convert degrees minutes seconds to decimalhttps://www.joellipman.com/articles/microsoft/excel/excel-convert-degrees-minutes-seconds-to-decimal.html

06 November 2014 39.81111111 116.7083333 Note that negative numbers should be supported. I tend to use Excel to reduce the decimal point to only 7 decimal places (for importing to a database where DECIMAL(10,7) ). Merging the lot If B1 contains the coordinates and there...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB