Azhar's Blog Space

Export a text file from Excel with comma delimiters and single quotes.

2010-12-23T02:18:00.000-08:00

I got this need for exporting excel in a text file with comma delimters and single quotes to troubleshoot a issue in oracle database.

The issue was to find the records in the table with against their primary keys, now the primary key type was a string and I had them in a excel file and they were 100s in number, now think to write a where clause it will be a pain in ass..

So I found out the method to export the excel in text format with the records as comma delimtted and with single quotes as we need them in where clause.

Eg. Select * from Employee Where EmployeeID in ('XYZ', 'ABC', 'YUV'.......)

1. Copy your contents in a MACRO enabled Excel workbook.
2. Press Alt + F11 or go to Developer Tab and then click Visual Basic in the code group.
3. Go to MS main menu - > Excel Options - > Popular - > select the Show Developer and click OK.
4. Open the Visual Basic editor and click the Module on the insert menu.
5. Paste the following code in the module space

Sub ExportTextFormat()
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer

' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma Exporter")

' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum

' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If

' Turn error checking on.
On Error GoTo 0

' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count

' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file with quotation marks.
Print #FileNum, "'" & Selection.Cells(RowCount, _
ColumnCount).Text & "',";

' Check if cell is in last column.
If ColumnCount = Selection.Columns.Count Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount

' Close destination file.
Close #FileNum
End Sub

6. Save the file, select the data you want to export and run the Macro ExportTextFormat.
7. Give a filename with the full path (eg. D:/Test.txt)
8. You are good to go now.

Hope this helps!

-Azhar

PL/SQL Date function - to_date()

2010-10-27T04:43:00.000-07:00

In Oracle/PLSQL, the to_date function converts a string to a date.

The syntax for the to_date function is:

to_date( string1, [ format_mask ], [ nls_language ] )

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
RRRR	Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M.	Meridian indicator
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

to_date('2003/07/09', 'yyyy/mm/dd') would return a date value of July 9, 2003.
to_date('070903', 'MMDDYY') would return a date value of July 9, 2003.
to_date('20020315', 'yyyymmdd') would return a date value of Mar 15, 2002.

Disabling script debugging in VS 2008 and IE 8

2010-09-16T05:14:00.000-07:00

Visual studio 2008 creates a problem when it starts debugging the javascript. It actually generates a number of files by name anonymous code which leads to a slow debugging in development environment.

In IE 7 you can disable the script debugging directly from IE tools. But in IE 8 it doesn't work, there are some workarounds given by microsoft as I thought it is good to paste here.

Work around:

1. This work around requires the Silverlight 2 Tools for Visual Studio 2008 SP1 to be installed. So if you don't have these tools installed, you can download them from Microsoft (Click Here). If this is not an option for you, there is some alternative work arounds at the bottom of this blog post.
2. Right click on your project and open up project properties
3. Go to the 'Start Options' sheet, and click the 'Silverlight' button at the bottom of the screen. Because the debugger cannot debug both Silverlight code and Script code at the same time, this will disable Script debugging and therefore avoid the problem.

Alternative work around – disable all script debugging:

* Open a new command prompt (start->run, cmd.exe). If you are on a 64-bit computer this needs to be a 32-bit prompt (start->run, c:\windows\syswow64\cmd.exe)
* reg add HKLM\SOFTWARE\Microsoft\VisualStudio\9.0\AD7Metrics\Engine\{F200A7E7-DEA5-11D0-B854-00A0244A1DE2} /v ProgramProvider /d {4FF9DEF4-8922-4D02-9379-3FFA64D1D639} /f

If you are using Visual Web Developer Express, replace 'VisualStudio' with 'VWDExpress':

* reg add HKLM\SOFTWARE\Microsoft\VWDExpress\9.0\AD7Metrics\Engine\{F200A7E7-DEA5-11D0-B854-00A0244A1DE2} /v ProgramProvider /d {4FF9DEF4-8922-4D02-9379-3FFA64D1D639} /f

If you want to restore your computer so that you can debug scripts again:

* reg add HKLM\SOFTWARE\Microsoft\VisualStudio\9.0\AD7Metrics\Engine\{F200A7E7-DEA5-11D0-B854-00A0244A1DE2} /v ProgramProvider /d {170EC3FC-4E80-40AB-A85A-55900C7C70DE} /f

Work around for performance problems caused by 'anonymous code' nodes:

Some sites run into problems being debugged due to numerous "anonymous code" nodes in solution explorer. For these sites, instead of disabling script debugging completely, it's possible to disable just the 'anonymous code' nodes. This requires Visual Studio 2008 SP1. To do so:

* reg add HKCU\Software\Microsoft\VisualStudio\9.0\Debugger /v HideAnonymousScriptCodeNodes /t REG_DWORD /d 1 /f

Hope this helps!!

-Azhar

How to get current week start date from current date in .NET

2010-08-12T02:22:00.000-07:00

Following code will give you the week start date on passing any date.

private DateTime getWeekStartDate(DateTime weekDate)
{
DateTime currentWeekStartDate = weekDate;

string StartDay = "1" // You can set any day as start day of the week
// 1 for monday, 2 for tuesday and so on

while (!StartDay.ToLower().Equals(currentWeekStartDate.DayOfWeek.ToString().ToLower()))
{
currentWeekStartDate = currentWeekStartDate.Subtract(new TimeSpan(1, 0, 0, 0));
}
return currentWeekStartDate
}

Hope this helps, thanks

How to catch a drop down event from a datalist and have a cascading effect.

2010-08-12T02:06:00.000-07:00

Many a times in projects we have come across a need to get a drop down event from a datalist for a particular row, here is the code how you can achieve it.

1. In your data list set the selected index change event of the drop down and set the AutpPostBack Property to true.

2. Write the code as below

protected void ddlYourDropDown_SelectedIndexChanged(object sender, EventArgs e)
{

DropDownList ddlYourDropDown= (DropDownList)sender;

//So here you got the dropdown from the datalist row
// do whatever you want

}

Once you get the particular drop down from the datalist you can do whatever you want with its value, for example, if you need to have a cascading effect of drop downs within your data list, then you can acieve it very easily as below.

protected void ddlParentDropDown_SelectedIndexChanged(object sender, EventArgs e)
{

DropDownList ddlParentDropDown_= (DropDownList)sender;
DataListItem dataListItem = (DataListItem)ddl.Parent; // get the datalistitem you select to get the drop down to which you want to populate based on the parent drop down

DropDownList ddlChildDropDown= (DropDownList)dataListItem.FindControl("ddlChildDropDown");
ddlChildDropDown.Items.Clear();
if (ddlParentDropDown!= null && ddlChildDropDown!= null)
{
if (ddlParentDropDown.SelectedIndex != 0)
{
//populate Child drop down with parent drop down's (ddlParentDropDown) value

}
}

Import Excel into MySql database....!!!!

2009-10-19T03:01:00.000-07:00

Its easy and quick, thought to share it with you all....

Convert Excel into csv file with Save As.
Open the csv file in notepad/wordpad, see the delimeter (should be a comma).
Write the following command in the query browser
LOAD DATA LOCAL INFILE 'D:\\converted_csv.csv' INTO TABLE database.table FIELDS TERMINATED BY ',' ENCLOSED BY ‘”‘ LINES TERMINATED BY '\r\n' (table.field1, table.field2,table.field3...);

If you get any error, probably the single and double qoutes are not correct, sometimes the editor changes the quotes ASCII, better delete the quotes and write again...

TERMINATED BY is the delimeter you use "comma in our case"
LINES TERMINATED BY – new line character, if you do not use \r, the query will insert a new line character in the database.

Hope this helps....

Thanks
Sayed Azharuddin

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

2009-08-31T04:42:00.000-07:00

This is a common error faced by many of us and some times it takes hours to find what went wrong.

The reason it happens is

1) The size of the Varchar2 parameter, the maximum allowed size of the Varchar2 datatype is 4000, if the input string exceeds that number you will get this error.

2) Or you have defind your parameter of size 10 and trying to write 12 characters.

Workaround - For first reason, there is no way out you can use the Varchar2 datatype, better use CLOB objects.

For second, try to increase the size of the parameter.

Hope this helps.

Thanks
-Azhar

PL SQL query to create a search query.

2009-08-03T04:26:00.000-07:00

Hi,

Often we search for a particular value in the user tables of a database, for each table we have to change the query, if not the column name, at least the table name. The good new is, we can avoid doing that. We can write a query which will create a search query as its result records each for a user table in the database.

select 'select * from '||table_name||'
where upper (to_char(&in_column)) = upper (to_char('||'''&in_value'''||'));'
from user_tab_columns
where column_name = upper ('&in_column');

Run tha above query in PL SQL and see the magic.

Hope that helps you.

Thanks..

Website structure does matter.

2009-07-30T04:49:00.000-07:00

When you have decided to create a website for your business or say you are a developer and designing a website for your customer, you should always keep in mind that the Web site structure should not be complicated. A website should be well-organized, a well-organized website is one which is easy to understand, navigate and find easily what the visitor is looking for.

The intensionof the article is to make you understand the level of leaves/pages of the website should reside in. A good well organized website makes it easy for you to grow your site logically.

The page (which has the content) of your website must not be more than two level in the depth, that is the required page should not take more than two clicks from the home page. This is very important with regards to Search Engines.

A typical website structure could be as in the picture below.

Tier 1 – The home page

Tier 2 – The team sites/folders/sub sites

Tier 3 – The leaves/pages with contents

References

website structure

website structure

website structure

Hope this helps.

Experts have listed Top 25 dangerous errors in the programming world.

2009-07-29T23:50:00.000-07:00

click here for Top 25 Errors

SQL Reporting on Microsoft Sharepoint.

2009-07-29T03:56:00.000-07:00

Guys, the listed links are very helpful in reporting on sharepoint lists. Reporting on sharepoint list is not that simple as you do with any of the database. It requires specialized skills and creativity. If you can think then you may discover a new method as well.

Click here

Here

and here

You can do query the data from two different lists like you do with tables but then you need a third party tool like Enyses RS Extension.

Hope this helps!!

How to pull Active Directory Users and populate into a drop down.

2009-07-29T00:34:00.000-07:00

Sometimes it is very hard to get it done, use this code to pull all the users from your active directory.

private void populateUsers()
{
ddlUsers.Items.Clear();

using (HostingEnvironment.Impersonate()) // To break the security for some time
{
DirectoryEntry entry = new DirectoryEntry("your AD server name");
DirectorySearcher searcher = new DirectorySearcher(entry);
try
{

searcher.Filter = " (&(objectClass=user)(objectCategory=person)(DN=*) "; //DN is the distinguished name of the AD user

foreach (SearchResult sResultSet in searcher.FindAll())
{
ListItem item = new ListItem();

item.Text = GetProperty(sResultSet, "givenName") + SPACE + GetProperty(sResultSet, "sn"); // sn - Surname
item.Value = "your domain" + GetProperty(sResultSet, "sAMAccountName"); // sAMAccountName - login name

ddlUsers.Items.Add(item);
}

ddlUsers.Items.Insert(0, "Select User");
}
catch (Exception ex)
{
throw new AppException(ex);
}
}
}
public static string GetProperty(SearchResult searchResult, string PropertyName)
{
if (searchResult.Properties.Contains(PropertyName))
{
return searchResult.Properties[PropertyName][0].ToString();
}
else
{
return string.Empty;
}
}

Hope this helps!!

Custom webpart development using ASP .NET.

2009-07-28T21:49:00.000-07:00

There are many references found on the web for custom webpart development, some of which I am adding here, if you want any specific functinality then let me know.

http://www.codeguru.com/csharp/.net/net_asp/webforms/article.php/c12293/

http://www.datasprings.com/Resources/ArticlesInformation/Sharepoint2007CustomWebParts/tabid/775/language/en-US/Default.aspx

http://msdn.microsoft.com/en-us/library/ms452873.aspx

Hope this helps!!

Thanks.

How to sort a drop down list in C#.

2009-07-28T06:50:00.000-07:00

There is no built in method available to sort a dropdown or any list in C#, thought to share with you guys.

#region SortDropDownList

public void SortDropDownList(DropDownList ddl)
{
//create a ListItem array the size of the items
//in your DropDownList
ListItem[] sorted = new ListItem[ddl.Items.Count];
//loop through all the items in your ListItem array
for (int i = 0; i < sorted.Length; i++)
{
//resize the array on each iteration
Array.Resize(ref sorted, i);
//add the current index to the array
sorted[i] = ddl.Items[i];
}
//call Array.Sort to sort your ListItem array
Array.Sort(sorted);
//remove all items from the DropDownList
ddl.Items.Clear();
//add the sorted items to the DropDownList
ddl.Items.AddRange(sorted);
}
#endregion

text with rotation displayed with no effect in crystal reports.

2009-07-27T02:13:00.000-07:00

Problem

Text with rotation have no effect on DHTML viewer.

Resolution

1. locate default.css from C:\Program Files\Business Objects\common\3.5\crystalreportviewers115\css\

2.Add the vertical text entry to the CSS file and save

.verticalText
{
writing-mode: tb-rl;
}

3. Specify the vertical text tag in your report by right clicking on the field that you want vertical, selecting properties, and under the “common” tab, enter “verticalText” in the “CSS Class Name” field.

Hope this helps.
Thanks.

Images/Charts not displayed on view but fine when exported.

2009-07-27T02:02:00.000-07:00

Symptom

Charts/Images on crystal report are not dispalyed when viewed from viewer, but when you export it to any of the format for example "pdf", there you will find your charts/images. I have found a work around for this.

Resolution

1. Right-click My Computer and click Manage.
2. Navigate to Services and Applications > Internet Information Services (IIS) Manager > Web Sites > Default Web Site.
3. Right-click crystalreportviewers115 and then click Properties.
4. On the ASP.NET tab, choose a 2.0.x version from the ASP.NET Version list.
5. Click the Apply button and then click OK.
6. Right-click businessobjects and click Properties.
7. On the Virtual Directory tab, click the Configuration button.
8. Check the path of the mappings and note the version number (in the path ...\Framework\vX.X...)
9. Repeat steps 2 to 5 for crystalreportsviewers115.If the paths for businessobjects and crystalreportviewers115 are different, change one or the other so that they point to the same version of .NET Framework.
10. Stop and restart the Default Web Site.

Hope this helps

Thanks.

How to pass the logged in user name to the store procedure or query in Crystal Reports.

2009-07-27T01:01:00.000-07:00

Crystal Report has this lilmitation that you cannot use or pass the Crystal Special fields to the stored procedure or command object as a parameter, say if you want to retrieve the data of the current logged in user as

Select * from tUser where user_id = CurrentCEUserName

CurrentCEUserName is the crystal special field. To achieve the above thing, please follow the following steps.

1) Create a blank report, suppress all the sections except the report footer.
2) Add a formula field and place the special field CurrentCEUserName in the formula.
3) Create a subreport in the and place in the report footer.
4) Create the command with parameter UserId in the subreport.
5) From main report link the formula field created with the subreports command parameter.This way the Current CE User Name will be available in the subreport which is actually our main report.

Hope this helps,

Thanks.

Navigate to portal home from Mysite.

2009-07-27T00:57:00.000-07:00

We had this problem of having Portal home link (to return to the portal home page) on Mysite of every user during one of our project, what we did was we created a link in SharedServices1 > Personalization site links.

Easy enough to add a link in SharedServices1 > Personalization site links. Except that when you click and return to your Portal, the menu has retained the MySite navigation because the URL contains "?MySiteView=1".The solution (as per link) is to add a # on the end of the URL you enter, this makes the rest of the link an anchor so SP will ignore it and display your normal menu. Thought to share it with you guys.
http://portalhome/#

Thanks.