Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • September 30, 2016, 01:40:14 PM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Advice on manipulating a flat file  (Read 588 times)

dluby

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 220
    • View Profile
    • Donate to Member
Advice on manipulating a flat file
« on: September 15, 2015, 06:51:58 AM »
Hi,

I have been given the 'pleasure' of masking data in a text file that is 160MB in size with 1.25M records.  The format is (yes the delimiter is ¦¦):

SRCE_CUST_NO¦¦FIRST_NAME¦¦LAST_NAME¦¦ADDR_1¦¦ADDR_2¦¦ADDR_3¦¦ADDR_4¦¦ADDR_5¦¦ADDR_6¦¦ADDR_7¦¦POST_CDE¦¦DOB¦¦MARITAL_STA¦¦STAFF¦¦EMPLR_STA

Can anybody recommend the easiest way to mask\amend certain columns for all the records.  So for example I'd like to replace the FIRST_NAME, LAST_NAME and address columns with dummy data (preferably with sequential numbering but not essential)?

So it would end up like this:

SRCE_CUST_NO¦¦FIRST_NAME¦¦LAST_NAME¦¦ADDR_1¦¦ADDR_2¦¦ADDR_3¦¦ADDR_4¦¦ADDR_5¦¦ADDR_6¦¦ADDR_7¦¦POST_CDE¦¦DOB¦¦MARITAL_STA¦¦STAFF¦¦EMPLR_STA
12343¦¦F_NAME1¦¦LAST_NAME1¦¦Address 1¦¦Address 1¦¦Address 1¦¦Address 1¦¦Address 1¦¦Address 1¦¦Address 1¦¦P_CODE1,25/05/1967¦¦MARRIED¦¦NULL
12343¦¦F_NAME2¦¦LAST_NAME2¦¦Address 2¦¦Address 2¦¦Address 2¦¦Address 2¦¦Address 2¦¦Address 2¦¦Address 2¦¦P_CODE2,02/08/1998¦¦SINGLE¦¦NULL

I tried using Excel 2010 but it can only load 1048576 records.

Thanks

tomos

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 10,251
    • View Profile
    • Donate to Member
Re: Advice on manipulating a flat file
« Reply #1 on: September 15, 2015, 09:52:55 AM »
CS-Calc claims to be able to work with 12 million rows, might be worth a try (if that's a possibility)
Tom

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 36,273
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: Advice on manipulating a flat file
« Reply #2 on: September 15, 2015, 10:01:01 AM »
A simple regex script (python, perl, etc.) would make quick work of it.

dluby

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 220
    • View Profile
    • Donate to Member
Re: Advice on manipulating a flat file
« Reply #3 on: September 16, 2015, 04:11:21 AM »
A colleague put together a SQL SSIS package to this this for me so problem resolved. Thanks