Duplicating the rows in a text file with data value sign changed

95 pts.
Tags:
VBScript
Visual Basic
How can I duplicate all records of a file and change the data value sign i.e + to - and - to + using VBscript
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 16  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Sound like homework. Show us what you have and we can help. It should be fairly simple math.
    126,110 pointsBadges:
    report
  • jugal123
    input file is like:

     abc|xyz|a|ab_1|100
     abs|pqr|d|ab3|-200
    output should be like 
    abc|xyz|a|ab_1|100
    abc|xyz|a|ab_1|-100
    abs|pqr|d|ab3|-200 
    abs|pqr|d|ab3|+200
    and thn sort it based upon some column value
    95 pointsBadges:
    report
  • ToddN2000
    You should select a record, perform the calculation to reverse the sign ( *-1), then insert a new record with new calculated value. Repeat this till the input table has been processed.
    126,110 pointsBadges:
    report
  • jugal123
    thats helpful....thanks ...but what about sorting the file based upon a column value?
    95 pointsBadges:
    report
  • carlosdl
    Todd, it appears to me that jugal123 is not talking about database tables, but text files.
    84,580 pointsBadges:
    report
  • ToddN2000
    In your example are we to assume the following

    abc|xyz|a|ab_1|100
    abc is column 1 (name1)
    xyz is column 2 (name2)
    a is column 3 (name3)

    and so on?

    when done adding all the duplicates, what format do you want your output displayed?  You can check this out for sorting data tables link.
    126,110 pointsBadges:
    report
  • jugal123
    Hi.. I m not using tables.. I want to do all with in VB script only. and ur understanding is correct regarding columns.  Here is the full scenario :
    I am having a input text file. I have to process it via vb script and add duplicate records with data value sign changed, than the out put file should be sorted one in to .csv format... Do let me know if u need clarifications ( not using data table)
    95 pointsBadges:
    report
  • ToddN2000
    You would read your test file record.
    Break it up into separate columns by using a break on your delimiter "|". 
    It looks like column 5 would have to be converted to a numeric (int or dec). 
    Perform the math to convert the amount. 
    Then do an insert into a temporary table,datalist or array.
    At the end,  read and export to a csv.
    Something like this
    // initialize writer
    
       StreamWriter csv;
    
    
       // set target directory
       string targetDir = "e:\\dir\\subdir;
       if ( ! Directory.Exists ( targetDir ) ) Directory.CreateDirectory ( targetDir );
    
    
       // set target csv
       string filename = "whatever";
       csv = File.CreateText ( targetDir + "\\" + filename + ".csv" );
    
    
       // write csv header row
       DataTable tblSource = // function to fetch your data here //
       foreach ( DataColumn col in tblSource.Columns ) {
          csv.Write ( col.ColumnName );
       }
    
    
       // loop thru each row
       foreach ( DataRow srow in tblSource.Rows ) {
    
          // line break
    
          csv.WriteLine ( "" );
          // write column values
          foreach ( DataColumn col in tblSource.Columns ) {
             csv.Write ( row [ col ] );
          }
       }
    
       // close writer
     
       csv.Close ( ); 
    126,110 pointsBadges:
    report
  • jugal123
    Thanks Todd.. It seems really helpful.. let me give it a try.. Will be back here If I see any challenges or  else to say Thanks again for your help...Tc
    95 pointsBadges:
    report
  • jugal123
    Hi Todd,

    I  have developed the initial draft of the script.
    Challenge I am facing in 
    1. math conversion 
    2.there is a blank row after each row  in file.

    My sample Code:
    'Initialize the file path and new csv file creation

    filefolder="C:\files\"


    Set fso = CreateObject("Scripting.FileSystemObject")
    strFile= filefolder & "JDa.dat"
    'Open existing dat file for reading
    Set f1 = fso.OpenTextFile(strFile, 1)

    'Create new .dat file with a .csv 
    strNewFileName=Left(strFile,Len(strFile)-4) & "-New" & ".csv"

    Set f2=fso.CreateTextFile(strNewFileName,True)

    //file reading code

    'file writing code


    f2.writeline strTranid & "," & strEntity & "," & strLastday & "," & strBu & "," & strAcct & "," & strSacct & "," & strIcpv & "," & strSledger & "," & strLedger & "," & strDoc & "," & CIntAmt
    f2.writeline strTranid & "," & strEntity & "," & strLastday & "," & strBu & "," & strAcct & "," & strSacct & "," & strIcpv & "," & strSledger & "," & strLedger & "," & strDoc & "," & Amt * -1


    Kindly help with corrections..thanks..

    95 pointsBadges:
    report
  • jugal123
    PLease consider this.. ignore earlier reply

    Hi Todd,

    I  have developed the initial draft of the script.
    Challenge I am facing in 
    1. math conversion, when use * -1 type mismatch error.. 
    2.there is a blank row after each row  in file.

    My sample Code:

    'Initialize the file path and new csv file creation

    filefolder="C:\files\"


    Set fso = CreateObject("Scripting.FileSystemObject")
    strFile= filefolder & "JDa.dat"
    'Open existing dat file for reading
    Set f1 = fso.OpenTextFile(strFile, 1)

    'Create new .dat file with a .csv 
    strNewFileName=Left(strFile,Len(strFile)-4) & "-New" & ".csv"

    Set f2=fso.CreateTextFile(strNewFileName,True)

    //file reading code

    'file writing code


    f2.writeline strTranid & "," & strEntity & "," & strLastday & "," & strBu & "," & strAcct & "," & strSacct & "," & strIcpv & "," & strSledger & "," & strLedger & "," & strDoc & "," & Amt
    f2.writeline strTranid & "," & strEntity & "," & strLastday & "," & strBu & "," & strAcct & "," & strSacct & "," & strIcpv & "," & strSledger & "," & strLedger & "," & strDoc & "," & Amt * -1

    kindly help with corrections in above part of code..Many thanks
    regards


    95 pointsBadges:
    report
  • jugal123
    used write instead of writeline solved the blank line issue. now issue is with math conversion only..Thanks
    95 pointsBadges:
    report
  • jugal123
    Hi I noticed one thing, when I am parsing the file using  DW.Utilities.fParseString(strline,13,2,",") I get spaces before each character. this is the main issue.  any help?
    95 pointsBadges:
    report
  • ToddN2000
    Strange, had to check out your parse command and found this ...
    Hyperion FDM Parsing Function
    DW.Utilities.fParseString (string, total field count, field # to return, delimiter)
    The fParseString function is not a Visual Basic function. The function is a Hyperion FDM function used to parse strings that contain a delimiter.

    Are you writing this in VB or some other language.
    126,110 pointsBadges:
    report
  • jugal123
    hey..thanks .. I am done with the code.. there was some special character issue..which is resolved now..Thanks for your time.. I am working in Hyperion FDM only :) ...May be I ll need ur help in sorting the file..but before that..I will try to do it by myself.. Thanks again,.

    regards
    95 pointsBadges:
    report
  • ToddN2000
    No problem.

    I learned a little Hyperion FDM in the process.
    126,110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: