LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 11-08-2009, 02:52 PM   #1
jindalarpan
Member
 
Registered: Mar 2006
Posts: 94

Rep: Reputation: 15
tool for data masking


hello all
i am having one requirnment,
for quality reasons we have to give a data from the data base to QA team for tesitng and quality mentainance in form of excel sheets.
is there any way i can change the data in the excel feils and then give to them and when they perform some test i can reconver them back.

for example
original data is
NAME1 ADDRESS1 TELEPHONE
TECH IDS D 122 999 999999


changed/mask data would be
NAME1 ADDRESS1 TELEPHONE
SDBGzHCR C,011 888,888888


c
 
Old 11-08-2009, 03:45 PM   #2
vs0001
LQ Newbie
 
Registered: Nov 2009
Posts: 2

Rep: Reputation: 0
Quote:
Originally Posted by jindalarpan View Post
hello all
i am having one requirnment,
for quality reasons we have to give a data from the data base to QA team for tesitng and quality mentainance in form of excel sheets.
is there any way i can change the data in the excel feils and then give to them and when they perform some test i can reconver them back.

for example
original data is
NAME1 ADDRESS1 TELEPHONE
TECH IDS D 122 999 999999


changed/mask data would be
NAME1 ADDRESS1 TELEPHONE
SDBGzHCR C,011 888,888888


c

There are many ways to do it . Since you mentioned Excel - You could select a PK , Create test data on a new sheet and maintain referential context using a VLOOKUP function. Hope this helps .
 
Old 11-08-2009, 03:51 PM   #3
jindalarpan
Member
 
Registered: Mar 2006
Posts: 94

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by vs0001 View Post
There are many ways to do it . Since you mentioned Excel - You could select a PK , Create test data on a new sheet and maintain referential context using a VLOOKUP function. Hope this helps .
no thats not working for me
because its sheet with may such entries

where i am reaplacing all the
b -> a
2 - > 1

like that each character of some rows
 
Old 11-09-2009, 12:06 AM   #4
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.10, Centos 7.5
Posts: 17,697

Rep: Reputation: 2494Reputation: 2494Reputation: 2494Reputation: 2494Reputation: 2494Reputation: 2494Reputation: 2494Reputation: 2494Reputation: 2494Reputation: 2494Reputation: 2494
I'm not clear what you are asking for. Are you starting with the data in a DB? If so, which one?
You could extract the data into a csv (comma separated value) format, which Excel will automatically be able to read.
For MySQL as the src, the fn you'd want is concat_ws() http://dev.mysql.com/doc/refman/5.0/...tion_concat-ws
 
Old 11-09-2009, 06:59 AM   #5
jindalarpan
Member
 
Registered: Mar 2006
Posts: 94

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by chrism01 View Post
I'm not clear what you are asking for. Are you starting with the data in a DB? If so, which one?
You could extract the data into a csv (comma separated value) format, which Excel will automatically be able to read.
For MySQL as the src, the fn you'd want is concat_ws() http://dev.mysql.com/doc/refman/5.0/...tion_concat-ws
Hello

check the the below mentioned comments it will help in understanding it.


original data in excel sheet
NAME1 ADDRESS1 TELEPHONE
TECH-IDS D 122 999 999999


changed/mask data would be
NAME1 ADDRESS1 TELEPHONE
SDBGzHCR C,011 888,888888


if you check the above example

T is replace with S
E with D
C with B
H with G
(blank space) with ,
I with H
D with C
S with R
9 with 8
8 with 7

like that was
hope i am able to explain it fully now :-)
 
Old 11-09-2009, 01:52 PM   #6
chythanya
LQ Newbie
 
Registered: Oct 2009
Posts: 1

Rep: Reputation: 0
hi
how to do the greb aditing in linux to windows xp & vista & windows 7
 
Old 11-10-2009, 05:57 AM   #7
vs0001
LQ Newbie
 
Registered: Nov 2009
Posts: 2

Rep: Reputation: 0
Here are some options.

1. excel will limit your data - Since your requirement is small , you could do a Find Replace for each LETTER . Here are some limitations .
Your data will have limitations with the case .
You will not be able to change numbers.

You could change all characters to UPPER .
<>F AND <>R Characters to the de-identified letter , making it case sensitive.

Once testing is complete - You can reverse the values.

2. You can also write a function to replace values from a reference table by hashing the first say 20 bytes - and reverse the values based on the hash value.



3. Concat Name 1 and Address 1 to make it the PK ( hoping it is unique ), or Identify a PK.

Create a test address able -
Like 1 Test Street , Apt 100 | Test City | Test State | Test Zip.
Replace using VLOOKUP function based on PK.

Once testing is complete , you can reverse the values using the PK originally created .
 
Old 11-18-2009, 04:14 AM   #8
jindalarpan
Member
 
Registered: Mar 2006
Posts: 94

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by vs0001 View Post
Here are some options.

1. excel will limit your data - Since your requirement is small , you could do a Find Replace for each LETTER . Here are some limitations .
Your data will have limitations with the case .
You will not be able to change numbers.

You could change all characters to UPPER .
<>F AND <>R Characters to the de-identified letter , making it case sensitive.

Once testing is complete - You can reverse the values.

2. You can also write a function to replace values from a reference table by hashing the first say 20 bytes - and reverse the values based on the hash value.



3. Concat Name 1 and Address 1 to make it the PK ( hoping it is unique ), or Identify a PK.

Create a test address able -
Like 1 Test Street , Apt 100 | Test City | Test State | Test Zip.
Replace using VLOOKUP function based on PK.

Once testing is complete , you can reverse the values using the PK originally created .
if its difficult for excel then can i do this in mysql or access ?
i will upload the data from excle to mysql or access
 
Old 03-10-2010, 03:48 AM   #9
jindalarpan
Member
 
Registered: Mar 2006
Posts: 94

Original Poster
Rep: Reputation: 15
is there any data masking tools for mysql ?
 
Old 03-11-2010, 11:12 AM   #10
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 22,222

Rep: Reputation: 5968Reputation: 5968Reputation: 5968Reputation: 5968Reputation: 5968Reputation: 5968Reputation: 5968Reputation: 5968Reputation: 5968Reputation: 5968Reputation: 5968
Quote:
Originally Posted by jindalarpan View Post
is there any data masking tools for mysql ?
Open a new thread for a new question, don't tack them onto old threads.
 
Old 03-25-2010, 03:41 AM   #11
jindalarpan
Member
 
Registered: Mar 2006
Posts: 94

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by TB0ne View Post
Open a new thread for a new question, don't tack them onto old threads.
ok sir no issues :-)
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Any Data Recovery Tool??? ajeetraina Linux - Newbie 5 05-15-2008 09:36 AM
data collection tool natarajk Red Hat 5 05-29-2006 12:15 AM
Data Recovery Tool PhuckFonix Linux - Security 5 06-07-2004 07:24 PM
data recovery tool Skunk_Face Linux - Security 2 03-18-2004 03:29 AM
Data Recovery Tool sniff Linux - Software 2 11-29-2003 02:10 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 04:59 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration