Others | SQL with Manoj

Query Excel file source through Linked Server

November 12, 2010 11 comments

In previous post we saw how to setup a Linked Server for MySQL Database. Now lets go with other data sources. Excel files are the most important source of data and report management in a particular department.

When you need to do some query on Excel data, one way is to use Import/Export wizard, push the excel contents to SQL Server and then query on SQL Server DB. Another and easy way is to create a Linked Server to Excel file and query directly the Excel file itself.

You just need to create the Excel file and execute the following SQL Statements below:

–> For Excel 2003 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLS_NewSheet',
	@srvproduct = 'Jet 4.0',
	@provider = 'Microsoft.Jet.OLEDB.4.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xls',
	@provstr = 'Excel 5.0; HDR=Yes'

– Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLS_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLS_NewSheet...[Sheet1$]

–> For Excel 2007 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLSX_NewSheet',
	@srvproduct = 'ACE 12.0',
	@provider = 'Microsoft.ACE.OLEDB.12.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xlsx',
	@provstr = 'Excel 12.0; HDR=Yes'

– Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLSX_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLSX_NewSheet...[Sheet1$]

Note: If your excel file don’t have headers, then set HDR=No

You may need to execute the following SQL Statements to configure the Linked Server initially:

USE MSDB
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

>> Check & Subscribe my [YouTube videos] on SQL Server.

Categories: Excel Tags: ACE 12.0, Excel, Jet 4.0, Linked Server, Microsoft.ACE.OLEDB.12.0, Microsoft.Jet.OLEDB.4.0, OPENQUERY, sp_addlinkedserver

Basic UNIX/Linux commands for Interview Questions – Part 2

April 15, 2010 1 comment

Some basic UNIX/Linux commands (Part-2): [Part-1]
… from college notes !

Q36: Display record length of the 1st line of file emp.1st.

Ans: [student@localhost student]$ head -1 emp1.1st |wc –c
41

Q37: Display record length of the 1st line of file emp.1st & store result in a variable.

Ans: [student@localhost student]$ p=`head -1 emp1.1st|wc -c`
[student@localhost student]$ echo “$p”
41

Q38: Display line no 5 & 7 from emp2.1st

Q39: Get the year of joining from file emp2.1st of all the emp.

Ans: [student@localhost student]$ cut –d “|” -f 5.7, 5.8 emp2.1st
50
47
58
62
50
55

Q40: Create a file shortlist by starting 1st 5 lines of emp1.1st.

Q41: Extract field numbers 2 & 3 from shortlist save as cut1.

Q42: Extract fields 1,4,5 from shortlist save as cut2.

Ans: [student@localhost student]$ cut -d”|” -f 1,4,5 shortlist >cut2
[student@localhost student]$ cat cut2
9876|production|12/03/50
2365|personnel|11/05/47
5423|admin|30/08/56
6213|accounts|05/06/62/6300\
4290|production|07/09/50

Q43: Put all fields of cut1 & cut2 together.

Q44: Sort on 2nd field of shortlist in two ways.

Q45: Create a sorted output file named sortlist.

Q46: Sort on 3rd field & then by 2nd field of shortlist.

Q47: Sort according to year of birth in shortlist.

Q48: Create a file dept.1st.

Ans: [student@localhost student]$ cat >dept.1st

Q49: Convert ‘|’ to ‘~’ in emp1.1st.

Ans: [student@localhost student]$ tr ‘|’ ‘~’ <emp1.1st
2233~a.k. shukla~g.m~sales~12/52/52~6000
5678~sumit chakrabarty~d.g.m ~marketing~19/04/43~6000
1006~chanchal singhvi ~ director~sales~03/09/38~6700
1265~s.n dasgupta~manager~sales~12/09/63~5600
2476~anil aggarwal~manager~sales~01/05/59~5000
6521~lalit chowdary~director~marketing~26/09/45~8200
2345~j.b.saxena~g.m~marketing~12/03/45~8000
0110~v.k.agarwal~g.m~marketing~31/12/40~9000

Q50: Display frequency of occurance of all the lines.

Ans: [student@localhost student]$ uniq –c

Q51: Display lines beginning with 2.

Ans: [student@localhost student]$ grep "^2" emp1.1st
2233|a.k. shukla|g.m|sales|12/52/52|6000
2476|anil aggarwal|manager|sales|01/05/59|5000
2345|j.b.saxena|g.m|marketing|12/03/45|8000

Q52: Display lines where salary lie between 7000 & 7999.

Ans: [student@localhost student]$ grep “7…$” emp1.1st
6521|lalit chowdury |director |marketing|26/09/45|8200
2345|j.b.saxena |g.m. |marketing|12/03/45|8000
0110|v.k.agarwal |g.m. |marketing|31/12/40|9000

Q53: Remove consecutive occurrences of spaces in emp1.1st.

Ans: [student@localhost student]$ tr –s ‘ ‘ <emp.1st
2233|a.k.shukla|g.m.|sales|12/12/52|6000
5678|sumit chakrobarty|d.g.m|marketing|19/04/43|6000
1006|chanchal singhvi|director|sales|03/09/38|6700
1265|s.n.dasgupta|manager|sales|12/09/68|5600
2476|anil aggarwal|manager|sales|01/05/59|5000
6521|lalit chowdury|director|marketing|26/09/45|8200
2345|j.b.saxena|g.m.|marketing|12/03/45|8000
0110|v.k.agarwal|g.m.|marketing|31/12/40|9000

Q54: Convert the contents of emp1.1st to uppercase.

Ans: [student@localhost student]$ sort –f emp1.1st
2233|A.K.SHUKLA |G.M. |SALES |12/12/52|6000
5678|SUMIT CHAKROBARTY |D.G.M |MARKETING|19/04/43|6000
1006|CHANCHAL SINGHVI |DIRECTOR |SALES |03/09/38|6700
1265|S.N.DASGUPTA |MANAGER |SALES |12/09/68|5600
2476|ANIL AGGARWAL |MANAGER |SALES |01/05/59|5000
6521|LALIT CHOWDURY |DIRECTO |MARKETING|26/09/45|8200
2345|J.B.SAXENA |G.M. |MARKETING|12/03/45|8000
0110|V.K.AGARWAL |G.M. |MARKETING|31/12/40|9000

Q55: Display lines containing jai sharma with a variable.

Ans: [student@localhost student]$ a=grep ‘jai sharma’ emp.1st
0

Q56: Display lines containing jai sharma without a variable.

Ans: [student@localhost student]$ grep ‘jai sharma’ emp.1st

Q57: Display lines containing ‘Agarwal’ , ‘agarwal’,& ‘aggarwal’ from emp.1st.

Ans: [student@localhost student]$ grep –e “Agarwal” -e “agarwal” –e
“aggarwal”
2476|anil aggarwal |manager |sales |01/05/59|5000
0110|v.k.agarwal |g.m. |marketing|31/12/40|9000

Q58: Display one copy of redundant records of file dept.1st.

Ans: [student@localhost student]$ uniq –d dept.1st
01|accounts |6213
02|admin |5423
03|marketing |6521
04|personnel |2365
05|production|9876
06|sales |1006

Categories: Interview Q, Linux Tags: Interview Questions, Linux Commands, UNIX Commands, Unix Interview Questions

Basic UNIX/Linux commands for Interview Questions – Part 1

April 11, 2010 1 comment

Some basic UNIX/Linux commands:
… from College notes !

Q1. Start UNIX/Linux while logging in remotely to telnet

Ans: [student@localhost student]$ telnet 192.168.0.4

Q2. Enter the user name as student and password as student.

Ans: [student@localhost student]$

Q3. Display all files starting with a dot and filename more than three characters.

Ans: [student@localhost student]$ ls .???*

Q4. Create files chap01,chap02,chap05,chap07,chap*,chap[0-3]

Ans: [student@localhost student]$ cat > chap01
[student@localhost student]$ cat > chap02
[student@localhost student]$ cat > chap05
[student@localhost student]$ cat > chap07
[student@localhost student]$ cat > chap0*

Q5. Display all files starting with an alphabet irrespective of the case.

Ans: [student@localhost student]$ ls [a-Za-z]*

Q6. Try the command pwd to see the present working directory

Ans: [student@localhost student]$ /home/student

Q7. Create files namely abc.txt ,aby.txt ,xdf ,x02,x04,ab4.

Ans: [student@localhost student]$ cat > abc.txt
[student@localhost student]$ cat > aby.txt
[student@localhost student]$ cat > xdf
[student@localhost student]$ cat > x02
[student@localhost student]$ cat > x04
[student@localhost student]$ cat > ab4

Q8. Display the files starting with a or t and second character b or x and length of the files should be only 3 characters.

Ans: [student@localhost student]$ ls [a,t][b,x]?

Q9. Create directories namely dir1,dir2,dir3

Ans: [student@localhost student]$ mkdir dir1,dir2,dir3.

Q10. Create three files in each of the directory.

Ans: [student@localhost student]$ cat > /home/student/dir1/file01
[student@localhost student]$ cat > /home/student/dir2/file02
[student@localhost student]$ cat > /home/student/dir3/file03

Q11. Copy all the files from these directories to the current directory.

Ans: [student@localhost student]$

Q12. Copy recursively the three directories include , bin, lib from / to directory.

Ans: [student@localhost student]$ cp –R { nitin1,nitin2,nitin3 }
/home/student/kap

Q13. Remove the file chap*

Ans: [student@localhost student]$ rm chap*

Q14. Display the file contents of file chap[0-3].

Ans: [student@localhost student]$ cat chap[0-3]

Q15. Create a file hello.

Ans: [student@localhost student]$ cat > hello

Q16. Create a hard link of the file hello name it as hai.

Ans: [student@localhost student]$ ln hello hai

Q17. Display the listing of both hello and hai files along either their i-node number.

Ans. [student@localhost student]$ ls –i hello hai

Q18. Create the symbolic link of file hello as hellohai

Ans. [student@localhost student]$ ln –s hello hai

Q19. Display the listing of both hello and hellohai files along with their i-node number.

Ans. [student@localhost student]$ ls –i hello hellohai

Q20. Display the process status of all the processes running on linux.

Ans. [student@localhost student]$ ps

Q21. Display the hidden files and also marked them as executable and directory.

Ans. [student@localhost student]$ ls –Fa

Q22. Display all the directories, sub- directories and files.

Ans. [student@localhost student]$ls -R

Q23. Create a file name in file at 3 to 4 line of text to it.

Ans. [student@localhost student]$cat >infile

Q24. Count number of lines, words and characters in the filename infile.

Ans. [student@localhost student]$wc –lwm infile

Q25. Count number of words in infile with and without redirection.

Ans. [student@localhost student]$wc –w<infile

Q26. In a single command except data as well as display contents of the file infile.

Ans. [student@localhost student]$cat infile –

Q27. Display all the files starting with an alphabetic irrespective of the case.

Ans. [student@localhost student]$ls [a-zA-Z]*

Q28. Display the number of users logged on.

Ans. [student@localhost student]$who | wc-l

Q29. Display the number of files present in the current directory.

Ans. [student@localhost student]$ls –l | wc-l

Q30. Count number of bytes of all the .c files individually present in the current directory.

Ans. [student@localhost student]$wc –c *.c

Q31. Count total number of bytes of all the .c files present in the current directory.

Ans. [student@localhost student]$wc –c *.c | tail -1 –

Q32. Display the statement as there are _ files in the current directory using command substitution.

Ans. [student@localhost student]$echo “There are `ls –l –R | wc –l` files in current dir.”

Q33. Try the two statement echo the average pay is $1000
echo ‘the average pay is $1000’
echo “the average pay is $1000”

Ans. [student@localhost student]$echo ‘the average pay is $1000’
the average pay is $1000
echo “the average pay is $1000”
the average pay is 000

Q34. Create a shell variable as work and store the present working directory in it and display the same.

Ans. [student@localhost student]$ work=`pwd`

Q35. Try the command cat alone without using an argument.

Ans. [student@localhost student]$cat
(With only cat command with no file name it only read standard input.)

Check [Part-2] for next set of Questions.

Categories: Interview Q, Linux, Others Tags: Interview Questions, Linux Commands, UNIX Commands, Unix Interview Questions

C++ Program to implement File Handling With Class Objects – Q32

February 1, 2010 Leave a comment

Q32. Program to implement File Handling With Class Objects:

Define a class to represent a bank account. Include the following members:
i) Depositor Name
ii) Account Number
iii) Balance Amount

Member Function
i) To Assign Initial values(opening balance Rs. 1000 by default)
ii) To deposit an amount
iii) To withdraw an amount(if possible)
iv) To display the current balance

Write a file based program to store the records of at least ten accounts in “ACCOUNT_DETAIL” data file and perform the required manipulations- DEPOSIT, WITHDRAW & BAL_ENQUIRY using the file on a given account. The changes after each deposit and withdrawal should be updated in the given file.

… from College notes (BCA/MCA assignments):

#include <fstream.h>
#include <stdlib.h>
#include <iomanip.h>
#include <conio.h>

const int MAX = 100;

enum bool{_false, _true};

class Cbank{
	private:
		int accNo;
		char name[20];
		float bal;
	public:
		Cbank() : accNo(100){}
		void getAcc(int );
		bool accPresent(int );
		void saveBal(int, float );
		void dispAcc() const;
		void dispAll() const;
	};

void Cbank :: getAcc(int i){
	accNo += i;
	cout<<"\n Enter Name: ";
	cin>>name;
	cout<<"\n Enter Amount for opening Acc.: ";
	cin>>bal;
	}



bool Cbank :: accPresent(int _acnt){
	if(accNo == _acnt)
		return _true;
	else
		return _false;
	}

void Cbank :: saveBal(int type, float _amt){
	if(type == 2){		// Deposit.
		bal += _amt;
		cout<<"\n Balance Updated.";
		}
	else if(type == 3){	// Withdrawl.
		if(bal >= _amt){
			bal -= _amt;
			cout<<"\n Balance Updated.";
			}
		else
			cout<<"\n Not Enough Funds on Account, SORRY !!";
		}
	}

void Cbank :: dispAcc() const{
	cout<<"\n Account Information:-";
	cout<<"\n ~~~~~~~~~~~~~~~~~~~";
	cout<<"\n Account No.: "<<accNo;
	cout<<"\n Holders Name: "<<name;
	cout<<"\n Balance: "<<bal;
	}

void Cbank :: dispAll() const{
	cout<<endl<<setw(8)<<accNo<<setw(20)<<name<<setw(20)<<bal;
	}

void main(){
	int ch, i, _tot = 0;
	int _acc;
	float _amt;
	char nx;
	bool found;
	Cbank *Obank = new(Cbank[MAX]);
	fstream fstr;
	fstr.open("Bank.txt", ios::in | ios::nocreate);
	if(fstr){
		while(fstr){
			fstr.read((char *) &Obank[_tot], 
sizeof(Obank[_tot]));
			_tot++;
			}
		_tot -= 2;
		}
	fstr.close();
	// Data Retrieved form Disk.
	while(1){
		clrscr();
		cout<<"\n BANK AUTOMATION";
		cout<<"\n ~~~~~~~~~~~~~~~";
		cout<<"\n 1 -> Create Account.";
		cout<<"\n 2 -> Deposit Money.";
		cout<<"\n 3 -> Withdrawl Money.";
		cout<<"\n 4 -> Balance Inqury.";
		cout<<"\n 5 -> Display Accounts.";
		cout<<"\n 6 -> Exit.";
		cout<<"\n\n Enter your choice: ";
		cin>>ch;
		clrscr();
		switch(ch){
			case 1:	// Create Acc.
				while(_tot < MAX){
					cout<<"\n Account No.: "<<_tot+1;
					Obank[_tot].getAcc(_tot);
					cout<<"\n Want to add more (y/n): ";
					cin>>nx;
					_tot++;
					if(nx == 'n')
						break;
					}
				break;
			case 2:	// Deposit Money.
				found = _false;
				cout<<"\n Enter Account No: ";
				cin>>_acc;
				for(i=0; i<=_tot; i++){
					if(Obank[i].accPresent(_acc)){
						found = _true;
						break;
						}
					}
				if(!found){
					cout<<"\n Account Not Found.";
					break;
					}
				else{
					cout<<"\n Enter Amount to Deposit: ";
					cin>>_amt;
					Obank[i].saveBal(ch, _amt);
					}
				break;
			case 3:	// Withdrawl Money.
				found = _false;
				cout<<"\n Enter Account No: ";
				cin>>_acc;
				for(i=0; i<=_tot; i++){
					if(Obank[i].accPresent(_acc)){
						found = _true;
						break;
						}
					}
				if(!found){
					cout<<"\n Account Not Found.";
					break;
					}
				else{
					cout<<"\n Enter Amount to Withdrawl:";
					cin>>_amt;
					Obank[i].saveBal(ch, _amt);
					}
				break;
			case 4:	// Balance Inquiry.
				found = _false;
				cout<<"\n Enter Account No: ";
				cin>>_acc;
				for(i=0; i<=_tot; i++){
					if(Obank[i].accPresent(_acc)){
						found = _true;
						break;
						}
					}
				if(!found){
					cout<<"\n Account Not Found.";
					break;
					}
				else{
					Obank[i].dispAcc();
					}
				break;
			case 5:	// Display Acc.
				cout<<"\n Account Information:-";
				cout<<"\n ~~~~~~~~~~~~~~~~~~~";
				cout<<"\n ACC No."<<setw(20)
    <<"NAME"<<setw(20)<<"BALANCE";
				cout<<"\n``````````````````````````````````
  ``````````````````````````````````";
				for(i=0; i<_tot; i++)
					Obank[i].dispAll();
				break;
			case 6:	// Exit;
				fstr.open("Bank.txt", ios::out);
				for(i=0; i<=_tot; i++)
					fstr.write((char *) &Obank[i], 
sizeof(Obank[i]));
				fstr.close();
				// Data Saved to Disk.
				delete []Obank;
				exit(1);
			default:
				cout<<"\n Enter Appropriate Choice.";
			} // end of switch.
		getch();
		} // end of while.
	} // end of main.

Output:

BANK AUTOMATION

1 -> Create Account.
2 -> Deposit Money.
3 -> Withdrawl Money.
4 -> Balance Inqury.
5 -> Display Accounts.
6 -> Exit.

Enter your choice:1

Account No.: 1
Enter Name: Manoj

Enter Amount for opening Acc.: 1000

Want to add more (y/n): y

Account No.: 2
Enter Name: Nitin

Enter Amount for opening Acc.: 2000

Want to add more (y/n): n

Account Information:-

ACC No.                NAME             BALANCE
```````````````````````````````````````````````````````````````````
     100                 Manoj               1000
     101                 Nitin                 2000

Categories: Cpp Tags: Class Object, Cpp, Cpp File Handling, File Handling

C++ Program to implement File Handling with Command Line Arguments – Q31

January 31, 2010 Leave a comment

Q31. Program to implement File Handling with Command Line Arguments:

Copy the contents of an existing file to a new file the names of which are taken as command line arguments. The program should handle errors if the given first file name does not already exist.

… from College notes (BCA/MCA assignments):

#include <iostream.h>
#include <stdlib.h>
#include <fstream.h>
#include <string.h>
#include <conio.h>

void main(int argc, char *argv[]){
	char src[12], dest[12];
	char buff[1000];
	int len;
	clrscr();

	if(argc > 3){
		cout<<"\n Illegal Number of Arguments."<<endl;
		exit(1);
		}
	else if(argc == 2){
		cout<<"\n Enter the Destination File."<<endl;
		cin>>dest;
		}
	else if(argc == 1){
		cout<<"\n Enter Source and Destination File."<<endl;
		cin>>src;
		cin>>dest;
		}
	else{
		strcpy(src, argv[1]);
		strcpy(dest, argv[2]);
		}

	ifstream fin;
	fin.open(src, ios::in);

	if(!fin){
		cerr<<"\n File Does not Exist.";
		getch();
		exit(1);
		}
	fin.read((char*)&buff, sizeof(buff));
	fin.close();

	len = strlen(buff) - 1;

	ofstream fout;
	fout.open(dest, ios::out);
	fout.write((char*)&buff, len);
	fout.close();

	cout<<"\n File copied Successfully.";

	getch();
	}

Output:

C:\TC\MANOJ>31_FH.exe man.txt abc.txt

File Copied Successfully.