Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: Parsing rows from a csv file
Topic Summary: csv file
Created On: 11/02/2017 06:41 AM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
 11/02/2017 06:41 AM
User is offline View Users Profile Print this message

Author Icon
WilhelmEvans
Journeyman (50-99)

Posts: 85
Joined: 10/30/2014

I've been asked to set up a new AM task.  Not sure if it can be done so I'm asking first.  We need to select row data only if a certain cell (always column W) has a number that starts with (7), ex:720000319623, the rest of the numbers are always diff.   I need to extract the rows data into another .csv file with the headers included and email this to a client.   Not sure if it would be easier to extract the rows or delete the rows without a (7).  And yes, we  do not need the other rows if there is no record beginning with (7) in column (W).      Any suggestions please?



 Category Survey
 11/02/2017 07:30 AM
User is offline View Users Profile Print this message

Author Icon
WilhelmEvans
Journeyman (50-99)

Posts: 85
Joined: 10/30/2014

I know it would be helpful to include the actual file without PHI. So here it is.



 Category Survey
 11/02/2017 07:33 AM
User is offline View Users Profile Print this message

Author Icon
WilhelmEvans
Journeyman (50-99)

Posts: 85
Joined: 10/30/2014

sorry, got it now



 Category Survey



 11/02/2017 11:22 AM
User is offline View Users Profile Print this message

Author Icon
WilhelmEvans
Journeyman (50-99)

Posts: 85
Joined: 10/30/2014

Just got more details on the project.  I will need to split the rows out based on the ClientId, column (X) and send seperate files to diff locations. 



 Category Survey
 11/03/2017 12:52 PM
User is offline View Users Profile Print this message

Author Icon
Ricardo Castaneda
HelpSystems

Posts: 3424
Joined: 01/11/2008

Hello,

Would it be possible to post the task that you have built so far?

 Category Survey


-------------------------

Ricardo Castaneda | Principal Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700
 11/06/2017 08:36 AM
User is offline View Users Profile Print this message

Author Icon
WilhelmEvans
Journeyman (50-99)

Posts: 85
Joined: 10/30/2014

sorry for the mess this task is, but I'm way out of my league on this one.  I've tried different ways.  embarrassing.

 

     <AMVARIABLE NAME="Date1">%Format(now()-1,'YYYYMMDD')%</AMVARIABLE>
<!--     <AMFILESYSTEM SOURCE="\\kdcfs01\Artiva\Will\Dignity*" DEST="\\kdcfs01\Artiva\Will\Test\Dignity Trans_*.txt" />-->
<AMVARIABLE NAME="mail">Wilhelm.evans@getixhealth.com</AMVARIABLE>
<!--<AMVARIABLE NAME="theFileNameb"></AMVARIABLE>-->
<!--<AMVARIABLE NAME="theDirectoryb">\\kdcfs01\Artiva\Will\Test\*.*</AMVARIABLE>-->
<!--<AMLOOP TYPE="FOLDER" FOLDER="%theDirectoryb%" RESULTVARIABLE="theFileNameb">-->
<!--     <AMFILESYSTEM ACTIVITY="write_file" FILE="%theFileNameb%">\\kdcfs01\Artiva\Will\Test\file\Merch%Date1%.txt">%theFileNameb% %FileDateTime(theFileNameb)% %FileLen(theFileNameb)% bytes</AMFILESYSTEM>-->
<!--</AMLOOP>-->
<!--<AMVARIABLE NAME="FileCapArt1" TYPE="TEXT"></AMVARIABLE>-->
<!--<AMVARIABLE NAME="ArtContents1"></AMVARIABLE>-->
<!--<AMVARIABLE NAME="VarLineArt1"></AMVARIABLE>-->
<!--<AMVARIABLE NAME="Merchant">-1</AMVARIABLE>-->
<!--<AMFILESYSTEM ACTIVITY="read_file" AM_ONERROR="CONTINUE" FILE="\\kdcfs01\Artiva\Will\test\Dignity Trans_20171031-000000.csv" RESULTVARIABLE="ArtContents1" />-->
<!--<AMLOOP TYPE="LIST" LIST="%ArtContents1%" RESULTVARIABLE="VarLineArt1" DELIMITER="new_line">-->
<!--      <AMLOOP TYPE="FILECONTENTS" FILE="\\kdcfs01\Artiva\Will\test\Dignity Trans_20171031-000000.txt" RESULTVARIABLE="VarLineArt1" DELIMITER="720000319615">-->
<!--     <AMINCREMENTVARIABLE RESULTVARIABLE="Merchant" />-->
<!--     <AMFILESYSTEM ACTIVITY="write_file" FILE="%VarLineArt1%</AMFILESYSTEM">\\kdcfs01\Artiva\Will\Test\file\Merch%Date1%.txt">%VarLineArt1%</AMFILESYSTEM>-->
<!--</AMLOOP>-->
<!--     <AMVARIABLE NAME="Cad">%Cadence - 6%</AMVARIABLE>-->
<!--<AMVARIABLE NAME="NBay">-1</AMVARIABLE>-->
<!--<AMLOOP TYPE="LIST" LIST="%ArtContents%" RESULTVARIABLE="VarLineArt" DELIMITER="NorthBay\ToGW">-->
<!--     <AMINCREMENTVARIABLE RESULTVARIABLE="NBay" />-->
<!--</AMLOOP>-->
<AMVARIABLE NAME="i">0</AMVARIABLE>
<AMLOOP TYPE="ARRAY" ARRAY="arTxt" RESULTVARIABLE="curLine" DATASET="">
     <AMINCREMENTVARIABLE RESULTVARIABLE="i" />
     <AMFILESYSTEM ACTIVITY="write_file" FILE="file%i%.txt" APPEND="no">%curLine%</AMFILESYSTEM>
</AMLOOP>
<!--<AMFILESYSTEM ACTIVITY="get" SOURCE="\\kdcfs01\Artiva\Will\Test\*.*" RESULTVARIABLE="FileCapArt1" FILEPROPERTY="filename" />-->
<!--<AMSENDEMAIL FROM="noreply@getixhealth.com" TO="%mail%" SUBJECT="Merchant Files">File count source  \\kdcfs01\Artiva\Will\Test

Client:                  File(s) 
Merchant Data:    %ArtContents1%
</AMSENDEMAIL>-->



 Category Survey
 11/07/2017 11:53 AM
User is offline View Users Profile Print this message

Author Icon
Devin Cannon
HelpSystems

Posts: 398
Joined: 03/17/2016

Hi Wilhelm,

Here's a task that loops through each row of the file and checks if Row W starts with a 7.  If it does, the task writes that row to a new CSV file.

The task only writes to one file and doesn't consider Row X.  This would be possible to do but would take quite a bit of time to create.

The task would have to loop each row in the initial file.  If Row W starts with 7, then write to a file with the client ID in its name.  But first the task would have to check if the file already exists and check how many rows already exist so it writes the new row to the correct row in the file.  You wouldn't want to write to Row 5 if there is already a Row 5 in the file.

I hope this gets you started in the right direction.



 Category Survey


Wilhelm.aml
Wilhelm.aml  (2 KB)



-------------------------

Devin Cannon | Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665
 11/08/2017 06:08 AM
User is offline View Users Profile Print this message

Author Icon
WilhelmEvans
Journeyman (50-99)

Posts: 85
Joined: 10/30/2014

You are absolutely brilliant!  I love the comments that explain the steps.  Most helpful in learning.  I'll tweak where I can to complete the rest of what they need.  Hopefully I'll be able to take it from here.  Honestly, you guys are brilliant. 



 Category Survey
 11/08/2017 09:12 AM
User is offline View Users Profile Print this message

Author Icon
WilhelmEvans
Journeyman (50-99)

Posts: 85
Joined: 10/30/2014

me again.  to solve the next step in this process, I have to format two columns.  i've created and used a basic script that works outside of automate.  i then placed it in a BASIC Script IDE and i get this error:  Script error: "Not an object reference." at script line 2.  Still using same test file and parsed file.  any thoughts?  Am I approaching this in the wrong direction?  Script below:

Sub Main
     Workbooks.Open Filename:= _
        "\\KDCFS01\Artiva\Will\test\PARSEDTestFile_20171031-000000.csv"

Range("W:W").Select
Sheets(1).Select
With Selection
    Selection.NumberFormat = "0"
    .Value = .Value

Range("Z:Z").Select
Sheets(1).Select
With Selection
    Selection.NumberFormat = "0"
    .Value = .Value

ActiveWorkbook.Save
ActiveWindow.Close

End With
End With
End Sub



 Category Survey
 11/08/2017 11:09 AM
User is offline View Users Profile Print this message

Author Icon
WilhelmEvans
Journeyman (50-99)

Posts: 85
Joined: 10/30/2014

Something new.  I removed script task and just tried the original task.  it works great, but only when I run it manually.  It does not work in the background.  i have set everthing to background user and even tried using visible and non visible workbooks. 



 Category Survey
 11/09/2017 11:54 AM
User is offline View Users Profile Print this message

Author Icon
Ricardo Castaneda
HelpSystems

Posts: 3424
Joined: 01/11/2008

Hello,

I do apologize however tasks that run excel actions cannot run in the background. Our ODS (Open Document Spreadsheet) actions can but you will need to move away from Excel to ODS.

 Category Survey


-------------------------

Ricardo Castaneda | Principal Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1585 guests browsing this forum, which makes a total of 1585 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog