Best apparel websites india

Here are some good shopping website for clothes in India.

http://www.Jabong.com
http://www.Myantra.com
http://www.Yebhi.com
http://www.infibeam.com/Apparel/

If you have come across a good and trustworthy website for clothes in India, feel free to post in comments.

awk command in Unix

awk is a very useful and powerful command in Unix. It's used to work with columns in a text file.

Here is a simple awk command to list the data of 1st column in a text file.



awk '{print $1}'   file_name

We can specify the field-separator using -F option. Refer below example.
Here tab is the filed separator. Below command will print the content of Column 2 in text file.
awk -F'\t' '{print $2}' file_name

We can print the length of data in column 2 using below awk command.
awk -F'\t' '{print length($2)}' file_name

We can check conditions in awk command and print accordingly.
awk -F'\t' '{if(length($4) > 50 ) print $4} ' file_name

The above command will print all the data in Column 4 if length of data is greater than 50 characters.

For more Tips & Tricks in Unix, Click Here.

Group by and Count is Microsoft Excel

Sometimes, we may need to group data in a Column in Excel and take the distinct values and the count of their occurrence.

Refer below data.

In the above data, if we need distinct values and the number of occurrences, follow the below steps:

1. First get the distinct values to another column. Refer detailed instructions here.
2. Then, use COUNTIF function to get the count of occurrence.

COUNTIF usage:

COUNTIF takes two parameters, 1st is a range where data is present multiple times, and 2nd is the value whose count needs to be found.

Syntax:

=COUNTIF(A1:A12, B1)

For more Tips & Tricks in Excel, Click Here.

How to get distinct records in Microsoft excel?

Follow the below steps to get distinct values from a column in excel sheet to a blank column.

1. Select Data-Filter-Advanced filter
2. Set your list range to the range containing data whose distinct to be retrieved
3. Check Copy to Another Location
4. Check Unique Records Only
5. Set Copy To range to an empty range on the same worksheet.

For more Tips & Tricks in Excel, Click Here.

How to alter a table in Oracle to modify multiple column definition

Oracle alter multiple columns
----------------

To alter a single column in Oracle, the syntax is as below:

alter table
   table_name
modify
   column_name  datatype;

If you want to modify multiple columns, use below syntax:

alter table
   table_name
modify
   (
   column1_name  column1_datatype,
   column2_name  column2_datatype,
   column3_name  column3_datatype,
   column4_name  column4_datatype
   );

The above command is really helpful and saves time while modifying multiple columns in Oracle.

For more tips and tricks on Oracle, click here.

Oracle sqlldr - how to load first few characters of a string to Database in sqlldr

Today I had a Unique challenge while loading Data from text files to Oracle Database using sqlldr.

Many records were getting discarded as bad records. After investigation, I found that, the data is getting rejected because of a specific column whose length is less compared to the length of data in text file. I was getting error as below:

Error on table tbl_test, column col2.
Field in data file exceeds maximum length

The size and datatype of column col2 is varchar2(100).

To load all the data to the Database without any bad records, there are two options.
1. Increase the column width to match the length of data
OR
2. Truncate the data to 100 chars before loading to DB

I preferred the second option for below reasons:
a) First 100 chars of the string is sufficient for my analytics
b) Less space consumption
c) I am not sure of the maximum length that can be for that column in text file.

Now I am decided to trim the data to first 100 chars before loading to DB.
Let's see how to load first few characters of a string to Database in sqlldr?

We have to modify the ctl file (control file) as below:

Old ctl file:

LOAD DATA
INFILE "INFILE_NAME"
BADFILE "BAD_FILE"
DISCARDFILE "DISCARD_FILE"
APPEND into table tbl_test
fields terminated by '\t'
trailing nullcols
(
col1,
col2,
col3,
col4 sysdate
)

Modified ctl file:

LOAD DATA
INFILE "INFILE_NAME"
BADFILE "BAD_FILE"
DISCARDFILE "DISCARD_FILE"
APPEND into table tbl_test
fields terminated by '\t'
trailing nullcols
(
col1,
col2 CHAR(1000) "substr(:col2,1,100)",
col3,
col4 sysdate
)

Change Details:

1. We are using substring function to load first 100 chars
2. If you don't specify length of the input character data, sqlldr uses default of 255 characters. Hence by explicitly mentioning CHAR(1000), we are instructing sqlldr to read all data where col2 length is less than or equal to 1000 chars. We have to increase this number if we see data having higher length.

For more Oracle Tips, Click here. 

Excel Lookup function - Search a name and show it's vaue

This article will explain what is Lookup function in Microsoft excel and how to use Lookup function with example.

LOOKUP function is a very useful function in Microsoft Excel which returns  a value from a range of values(a row or a column) or from an array.

There are two forms of the function.

1. Vector form
2. Array Form

1. Vector Form
-------------------

In this form, the function searches for the lookup_value in a range, i.e. a row or a column and when a match is found, it takes the corresponding result_value from another range specified (row or column).

Note: The data in lookup_range must be in sorted order. Otherwise Lookup function will give incorrect result.

Syntax:

=LOOKUP(lookup_value, lookup_range,[result_range])

result_range is optional.

Example:

Here are some more examples and the corresponding result:

=LOOKUP(104,A2:A5)              returns 104

=LOOKUP(103,A2:A5,B2:B5)     returns Binay

2. Array Form

The array form, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.

Syntax:

=LOOKUP(lookup_value,array)

Example:

=LOOKUP("B", {"A","B","C","D";10,11,12,13})    returns 11 =LOOKUP("B", {"a","b","c","d";10,11,12,13})   returns 11
=LOOKUP("b", {"A","B","C","D";10,11,12,13})   returns 11
=LOOKUP("E", {"A","B","C","D";10,11,12,13})   returns 13
=LOOKUP("Back", {"A","B","C","D";10,11,12,13})   returns 11
=LOOKUP("0", {"A","B","C","D";10,11,12,13})   returns #N/A

For more Tips & Tricks in Excel, Click Here.