Microsoft - Joel Lipman .Com

Import Excel CSV file as JavaScript array

: Joel Lipman; Excel; 05 October 2015; Hits: 2829

What I have:
A CSV file exported from Excel along with double-quotes

label1,label2
item1a,item2a
item1c,"item2c,c"
item1b,item2b

label1,label2
item1a,item2a
item1c,"item2c,c"
item1b,item2b

What I want:
To read the file (stored on the server) and convert to a JavaScript array of objects

copy raw

var my_object_array = [
     { my_col1_val: 'item1a', my_col2_val: 'item2a' },
     { my_col1_val: 'item1b', my_col2_val: 'item2b' },
     { my_col1_val: 'item1c', my_col2_val: 'item2c,c' }
];

var my_object_array = [
{ my_col1_val: 'item1a', my_col2_val: 'item2a' },
{ my_col1_val: 'item1b', my_col2_val: 'item2b' },
{ my_col1_val: 'item1c', my_col2_val: 'item2c,c' }
];

What I want again:

Read a CSV file already uploaded with JavaScript
Populate a JS array with each row
Account for strings containing double-quotes (and commas to ignore)
Sort the resulting object array

How?

Category: Excel :: Article: 635

: Joel Lipman; Windows OS; 23 September 2015; Hits: 2570

What?
A quick article with the code to retrieve your product key in Windows 7 with a small VB script file. There are other articles on the web about this but the ones I found returned errors such as WshShell not valid. This article has a working example applicable to Windows 7 Professional.

Why?
With the Windows 10 operating system offered as a free upgrade (for Windows 7 or later at time of print), I needed the serial number / product key from my Windows 7 Professional 32-bit operating system. I didn't want to dig through my CD/DVDs to find my original windows 7 disc and no longer have access to the email account when I purchased Windows 7 (my university one).

How?
I'm going to use my trusty notepad program, copy the following code to it, and run it:

: Joel Lipman; Excel; 06 November 2014; Hits: 4900

What?
A quick article on how to get the value of 40°4′20″N 116°35′51″E into 40.079857, 116.603112.

How?
Let's pretend all the names are in column A, in Column B I have the coordinates that I want to convert:

copy raw

A                                       B
-------------------------------------   ----------------------
Beijing Capital International Airport   40°4′20″N 116°35′51″E
Beijing Shahezhen Air Base              40°8′57″N 116°19′17″E
Beijing Tongxian Air Base               39°48′40″N 116°42′30″E

A B
------------------------------------- ----------------------
Beijing Capital International Airport 40°4′20″N 116°35′51″E
Beijing Shahezhen Air Base 40°8′57″N 116°19′17″E
Beijing Tongxian Air Base 39°48′40″N 116°42′30″E

Note that I tend to copy the coordinates off a website which has strange apostrophes and double-quotes. You can change this but remember to put two double-quotes if you are searching on it, eg:

copy raw

FIND(""",B1)   // will NOT work!
FIND("""",B1)  // will work
FIND("″",B1)   // will work

FIND(""",B1) // will NOT work!
FIND("""",B1) // will work
FIND("″",B1) // will work

Category: Excel :: Article: 596

: Joel Lipman; Excel; 06 November 2014; Hits: 2708

What?
A quick article on if you want to extract the link from some text you have copied off the web and into an MS Excel document.

How?

Open up a new workbook.
Get into VBA (Press Alt+F11)
Insert a new module (Insert > Module)
Copy and Paste the Excel user defined function below
copyrawstyler
```
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
```
1. Sub ExtractHL()
2. Dim HL As Hyperlink
3. For Each HL In ActiveSheet.Hyperlinks
4. HL.Range.Offset(0, 1).Value = HL.Address
5. Next
6. End Sub
Press F5 and click “Run”
Get out of VBA (Press Alt+Q)

Category: Excel :: Article: 595

SSIS Multiple Lookups in one

: Joel Lipman; SQL Server Integration Services; 05 August 2014; Hits: 12573

What?
A quick article on an alternative to multiple lookups in SSIS. For any SSIS developer trying to create packages that need to decode a number of values into their full name/description (eg. "M" to "Male", "Prof" to "Professor").

Why?
Our extract phase in the SSIS package takes data from a source database and puts it in staging in the exact form it came through. Our transform package will convert the data and output it to the target system. For our package to decode what "M" means, it needs to look this up in a table on the original server. We had around 12 lookups to do, so you could do this:

But we didn't want to do this.

How?

: Joel Lipman; SQL Server; 07 July 2014; Hits: 9423

Applies To:

MS SQL Server 2008 R2
MS Windows 7 Enterprise (Client)
MS Excel 2010

What?
A really quick note on how to insert a carriage return or new line into the column name/alias (the header). It might seem trivial but these little aesthetic changes done at the database level can save some time.

Why?
I have an Excel report which dynamically gets its content from a data source located on a database on the other side of the world. I want the header in the column "Academic Week" to break across two lines so that the column doesn't expand to the width of "Academic Week" and instead expands to the width of the word "Academic".

What I have:

copy raw

Academic Week    Monday      Tuesday     Wednesday   Thursday    Friday
---------------- ----------- ----------- ----------- ----------- -----------
1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2                21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...

Academic Week Monday Tuesday Wednesday Thursday Friday
---------------- ----------- ----------- ----------- ----------- -----------
1 14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2 21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...

What I want:

copy raw

Academic 
Week      Monday      Tuesday     Wednesday   Thursday    Friday
--------- ----------- ----------- ----------- ----------- -----------
1         14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2         21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...

Academic
Week Monday Tuesday Wednesday Thursday Friday
--------- ----------- ----------- ----------- ----------- -----------
1 14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2 21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...

How?
To do this in a select query resultset, you insert the special character references "CHAR(10)" [line feed] and "CHAR(13)" [carriage return] but to do this in the name of the column heading, the answer is a much simpler one,

Category: SQL Server :: Article: 568

: Joel Lipman; SQL Server Integration Services; 13 June 2014; Hits: 18374

What?
This is an article to demonstrate a quick step-by-step on having an SSIS package loop through a directory/folder of files in order to populate a database table. We could add each file as a separate connection manager but this is inefficient and not versatile enough to accommodate files that get added later.

Why?
I am creating an extract 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?
I've adapted my real working product with an example. As my work was for a Personnel/HR project, and data confidentiality is somewhat important, some of the images will be censored or data changed in the example below.

: Joel Lipman; SQL Server Integration Services; 09 June 2014; Hits: 5573

What?
I started getting this error:

copy raw

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.2" is not registered for use on this computer

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.2" is not registered for use on this computer

Why?
We use a combination of MS Visual Studio 2008, 2010 and 2012. VS2008 for our old SSIS packages and VS2010 for database solutions running against SQL Server 2008 R2. We can use VS2012 for both but this requires upgrading all the packages and then making them suitable for our new SQL Server 2012 instances.

How?
This applies to a workstation (hopefully you're not doing development directly on the server). I re-installed sql server setup, re-ran the repair to no avail. A clue came from the MS site for an older version of the pipeline in that you access the SQL Server Configuration Manager. This may not be the fix for you but it was for me:

Open Start > All Programs > SQL Server 2012 (or your latest)
Expand "Configuration Tools" and open "Sql Server Configuration Mnaager".
Right-click on "SQL Server Integration Services 10.0" and select "STOP".
Right-click on "SQL Server (SQLEXPRESS)" and select "STOP".

Category: SQL Server Integration Services :: Article: 566

SSIS: Add Reference Microsoft.SqlServer.TxScript

: Joel Lipman; SQL Server Integration Services; 03 June 2014; Hits: 10288

What?
This article is so that I don't spend so long in googling what the TxScript referenced assembly is when creating an SSIS custom component.

How?
This is simply the "Script Component" under "Extensions". No MSDN website said this... obvious to Microsoft staff so why should their documentation include it?

: Joel Lipman; SQL Server Reporting Services; 15 April 2014; Hits: 6269

Applies to:

Microsoft SQL Server Reporting Services 2012
Microsoft Visual Studio 2012 Premium
Microsoft Windows 7 Enterprise

What?
So I have a column in an SSRS report which displays a date. Being rather pernickety, I would like a question mark to display if there is no date to populate the field.

Why?
At the moment, the expression in there is something like this:

copy raw

=Format(Fields!MyCompletionTime.Value, "dd/MM/yyyy HH:mm:ss")
-- yields 11/02/2014 11:21:32

=Format(Fields!MyCompletionTime.Value, "dd/MM/yyyy HH:mm:ss")
-- yields 11/02/2014 11:21:32

Sounds good, where's the problem?

Category: SQL Server Reporting Services :: Article: 562

: Joel Lipman; SQL Server Reporting Services; 11 April 2014; Hits: 7193

What?
This article describes a solution our reporting server administrators found to cut the loading time of the first report of the day.

Why?
Take any SSRS report, if one of us was the first to run it on that day, it would take an additional 60 seconds to get with it and display the report. Any subsequent running of the report loaded it almost immediately.

How?

: Joel Lipman; SQL Server Reporting Services; 08 April 2014; Hits: 12925

What?
Sounds easy but actually if you use an image which has transparent areas, ie. has one color which will be transparent, the transparent pixels will be colored in with the page background color.

Why?
I want an image to display per row as a status marker for 3 different types of results: Success, Failure, Unknown. My images are circles with the background being transparent. I want the first column to display an image based on the status result.

If I simply insert an image, the report would use the background color of the report.
How?

Import Excel CSV file as JavaScript array

VBScript to retrieve Windows Product Key

Excel: convert degrees minutes seconds to decimal

Excel: Extract hyperlink from link

SSIS Multiple Lookups in one

Include a carriage return in a column heading

SSIS: How to loop through multiple flat files as data sources

SSIS: Cannot open Data Flow Task: DTS.Pipeline.2 issue

SSIS: Add Reference Microsoft.SqlServer.TxScript

SSRS Display question mark when date is blank

SSRS No Delay for First Report of the Day

SSRS Change Background Color of Image

Excel

Infopath

SharePoint

SQL Server

SQL Server Integration Services

SQL Server Reporting Services

Team Foundation Server

Windows OS

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support