Powershell - filter stringa nd output to new file for database import

Guys,
I have a flat file which having around 1-3gb data , and before do import in the database, i need some clean up so that when I do import only a new clean data get imported. Below is how we doing it in the database via  T-sql but since the database cannot accept any downtime  and the select query below always takes very very long to complete and very often block insert to the database, we decided to move this task to text file string manipulation.

do anyone know how to begin with Powershell command to output new file without having any values below within a rows ?

Powershell command select-string will do this job but I'm clueless how to write the code for the clean up.




AGT_domainname = '' OR
      AGT_username LIKE 'SELkry' OR
      AGT_username LIKE 'testuser%'     OR
      AGT_username LIKE 'siteminder'    OR
      AGT_username LIKE 'CN=MYR%'       OR
      AGT_username LIKE 'CN=MYRTEST%'   OR
      AGT_username LIKE 'selypz' OR
      AGT_username LIKE 'selyyz' OR
      AGT_domainname LIKE 'Monitoring External' OR
      AGT_domainname LIKE 'onitoring Internal' OR
      AGT_domainname LIKE 'Test Domain' OR
      AGT_domainname LIKE 'TXTAPP SE'   OR
      AGT_domainname LIKE 'TXTAPP COM'  OR
      AGT_domainname LIKE 'TXTAPP NET'  OR
      AGT_domainname LIKE 'TXTAPP FED'  OR
      AGT_eventid = '7' OR
      AGT_eventid = '8' OR
      AGT_eventid = '9'
motioneyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
Can you provide sample of the input file?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No, Select-String will not do the job (well). It does not honour fields, though you can emulate that e.g. based on counting delimiters. The search string would resemble to a complex regular expression.
Your T-SQL contains mostly LIKE without a wildcard - is that just missing here, or do you search for exact matches?

Probably you import the file into PowerShell as CSV, and that is the best way, as you need each field for performing the import into DB anyway. Assuming the CSV contains a header line naming the columns like the DB fields without the AGT_ prefix, the filter than looks like this:
Import-CSV FlatFile.csv | ? {
       $_.domainname -eq ''
-or  $_.username -like 'testuser*'
-or  <# and so on #>
} | % { <# Do the DB import of each result line here #> }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
motioneyeAuthor Commented:
Hi Qlemo
I will start to test this, hopefully it work well and faster than a database query.
0
motioneyeAuthor Commented:
Thanks..
It works with my  quick try, I will do more try and posting a new topic if have any issues.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.