Read strings from a file and seach for those strings in Files – Return lines

We have a list of numbers in a file , lets say in msisdnList.txt, we have a number of files in a directory,say /data/CDRs/

We want read msisdns from msisdnList.txt and output lines in the files under /data/CDRs/ into a single file  CDR_File.txt:

-n adds the line where the string is found in the file
-h removes the filename from the output
-F


while read line;
do
grep -h -F $line  /data/CDRs/* > CDR_File.txt;</pre>
<pre><! -- For more than one string
<egrep -i 'string1|string2' /data/CDRs/* > CDR_File.txt>
done; < msisdnList.txt

Alternatively, we could cat the file, and recursively grep each line


cat msisdnList.txt | while read line;
do
grep -h -F $line  /data/CDRs/* > CDR_File.txt;
done;

Happy Scripting !!!

 

 

Advertisements

MS SQL: Writing to a text file from a Query (T-SQL)

MS SQL: Writing to a text file from a Query (T-SQL)

Previous Article is an alternative to spooling for Oracle, since this blog is about my day today activities at work.

Got this task where i needed to use data running on different platforms (one oracle and the other MS-SQL)

My reporting choice was on Oracle. So i needed to get data from the MS-SQL server onto the Oracle server and use a SQL Loader to load that data into an oracle table.

Am only delving over the actual extraction of query results into a text file which we shall load using the sql loader.

We shall create a directory and a blank text file where our query results shall be written. This will be at

c:/sample/sample.txt

We shall use an MS-SQL utility called bcp, this feature is enabled by default for versions earlier than MS-SQL Server 2005.

To enable BCP we shall run the command below


EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

we shall create a procedure


IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'sp_Sample')

AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE sp_Sample

GO

CREATE PROCEDURE sp_Sample

DECLARE @sql varchar(8000)

SELECT @sql = 'bcp " select empno,empname,salary from employees " queryout

c:\sample\sample.txt -c -t, -T –S<SQLServerName>'

EXEC master..xp_cmdshell @sql

Syntax

bcp {“query”}{ queryout }{data_file}{[-c -t, -T –S]}

queryout: The command exports data retrieved through a query into a data file.
c (character format): The bcp utility uses character data for the bulk copy operation.

-t : Field terminator. In our case we used comma (t,) as our field terminator

-T: Indicates that a trusted connection should be used to connect to SQL Server. You should use this option if the SQL Server instance uses integrated security

-S:Used to specify the server instance name

Oracle : Alternative to spooling

ALTERNATIVES TO SPOOLING – ORACLE

Spooling a large file can be quite resource intensive and time consuming.

I was always being forced to spool files in the night to avoid such issues. But a quick need for big datasets of data meant i needed a work around this.

You must be wondering whether there’s a way you can quickly query and save this info onto the server hosting the Oracle DBMS.

True, what i did was that, run a query and wrote the results onto the DBMS server and later ftp’d to that location and picked that file. Alternatively instead of ftp you could send that file directly to your email.(I will write about the email option in another article).

Lets look at how i went about this.

1. Lets create a virtual directory name where we shall store this file on our server

 CREATE DIRECTORY SPOOLDIR AS '/u01/spooldir'; 

TO View all Directories

  SELECT * FROM ALL_DIRECTORIES; 

2. Log in to the linux box an an oracle user and create the spooldir at the above location

$root~  su – oracle

$oracle~  mkdir /u01/spooldir

3. We shall create a procedure to write to this file


CREATE OR REPLACE PROCEDURE P_SPOOL

AS

f        utl_file.file_type;

x        utl_file.file_type := f;

BEGIN

x := utl_file.fopen('SPOOLDIR','[filename].csv','W');

utl_file.put_line(x,'EMPLOYEENO,EMPLOYEENAME,SALARY');

FOR s in (select empno,empname,salary from employees)

LOOP

utl_file.put_line(x,s.empno||','||s.empname||','||s.salary);

END

LOOP;

utl_file.fclose(x);

END P_SPOOL;

 x := utl_file.fopen('SPOOLDIR','[filename].csv','W'); 

Replace [filename] with ur preferred filename and remove the box brackets

Utl_file.putline adds a line to the [filename].csv file you have added. In the above case, we shall have EMPLOYEENO, EMPLOYEENAME and SALARY as our headers.

Write the query resultset to the s variable

 FOR s in (select empno,empname,salary from employees) 

We then loop through the dataset while using the put_line function from the utl_file package to write each


LOOP

utl_file.put_line(x,s.empno||','||s.empname||','||s.salary);

END

You can grant permissions to the directory as below

GRANT read, write ON DIRECTORY  SPOOLDIR TO ORACLEUSER;

Working With GWT

Been working on an Ebidding/E-tendering Solution where bid information is input through the workflow process, from when the bid drafts are made, then invitations for bids are issued then bid documents are issued, rebid meeting minutes until evaluation of the bids and contracts awarding.

I used GWT with the GXT library and JPA for persistence with DTOs for transfer of data between the entity classes and the GWT (JavaScript) Client.

I used GWT RPC for communication between the Server and Client.

The client sends and receives DTOs, but the server needs pure Java Objects to be used by the JPA Entity Manager, which arose the need for transforming from DTO to JPA entity classes and vice varsa.So i had to map the entity classes to the DTOs
I realized later that Hibernate should have been a better choice as i had to pass whole objects to the server in order to retrieve single values or return a single value like a supplierID eg.

// add some code here

There are however a coupla third party libraries like Gilead formally Hibernate4GWT and Dozer which can be used for mapping these entity classes to DTO, but i decided against going for them. Will delve into them later on.

I also had the option of Using RequestFactory but didn’t delve so much into it, but it is much more data oriented than GWT-RPC which is service oriented. It is designed to implement its own persistent layer on both the server and client side. It gat its own protocols for data exchange between the server and client.

 

To be updated……………Project is stil on going

Identifying and Non Indentifying Relationships

In Non identifying relationships one table doesnt need another table to identify a record in that table.
Eg
Leads(Leadid,LeadName,LeadTypeid)
LeadTypes(LeadTypeid,LeadTypeName)

Whereas in an identifying relationship,a record in one table cannot be uniquely identified if certain records in 1 or more tables does exit.
Accounts(AccountID,AccountName,AccountAddress)
AccountCases(AccountCasesID,AccountID,CasesID)
Cases(CasesID,CaseName,DateCreated)

You cant have an AccountCases entry without either the Accounts and Cases records.

Am trying to use DBDesign Fork for ERD development and later push this into an MS SQL DB,nice tool.

(Song 3) – Pain

Here’s number 3

This whole thing is wearing me out
Despite my relentless efforts
ur imprint is embedded
in the pysche of my life
2nite i c miles away
but cant get that far

(chorus)

done with cliches
Its pain
comes reeling thu my door
with haste i always live
cant live with it
cant stand it

Am a monk(cant fight)
that ws a story
this is something else

The wailing of a molested nun
The works of an athiest
Who has unfortunately caught some air.

(Chorus)
Done with cliches
Its pain
Comes reeling thu my door
With haste i always live
Cant live with it
Cant stand it

(Song 2) – Lost

Here is Number 2

In the past
it was flowers pictures toasts n sunsets…
There were binges..
Billboards said it was a passion

thought of the closet is elusive
Pegions flying in the closet
feathers floating in the air
They transcend to great heights

The breeze solaced us
What happened to the cassettes?
Am sorry times change
A ridiculous off the wall remark
A nasty retort to human indiscretion

Loniless is tsunami
A jew in hezbollar
flowers to the graveyard
Cigarrate burns in my lungs..

The thoughts of a dying pig
Another source of agigation gone
deluSions of inadequacy
Stuck in the lies of the weather
Caught in the middle..

Maybe blood flowing through our arteries
But we are dissimilar
The pigment of a silver lining
The intricasies of a heart
All bring us to a halt

N now i wonder
wat really engenders the confusion
we r lost in the revolving globe