We are excited to announce that the IIS.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

Cut And Sum UpRSS

1 reply

Last post Aug 31, 2011 01:23 AM by KristoferG

  • Cut And Sum Up

    Aug 30, 2011 03:44 AM|Sylnois|LINK

    Hey guys I have a csv-file with two rows. Name and Number. Name looks like "http://www.google.ch/bla/bla.cfm,2343" and Number like "234". I want now to cut everything after the third slash. It should look like "http://www.google.ch/". And then i want to sum up all Numbers which contain "http://www.google.ch". This result i want to save in a .csv-file. The file looks currently like this: Name, Number http://www.google.ch/bla/bla.cfm,2343 http://www.google.ch/bla/.cfm,232 http://www.google.ch/bla/, 1 http://www.google.ch/asdfasdf/asdfjeasdf/, 2 http://yahoo.de/asdf/, 2 http://yahoo.de/adsf/asdf.casdfjeh, 3 And i want it like this: Name, Number http://www.google.ch, 2578 http://yahoo.de, 5 U know what i mean? btw: how can i continue working with the sql-result? i dont want always to save the result in a csv-file. Regards Sylnois
  • Re: Cut And Sum Up

    Aug 31, 2011 01:23 AM|KristoferG|LINK

    Hi,

    The following will group and sum the domain name and number, and will output what you have specified:

    D:\Program\Log Parser 2.2>logparser "SELECT EXTRACT_TOKEN(field1,2,'/') AS [Domain Name], SUM(field2) AS [Sum] FROM 'D:\TEMP\inputFile.csv' GROUP BY EXTRACT_TOKEN(field1,2,'/')" -i:CSV -headerRow:off

    The above may wrap, it should all be on one line written in the command window.

    If you want to continue working with the result, you can save it to a new file and use that file as the input file in the next query (preferable you script this if you have multiple queries and files you work with). For example with the following:

    D:\Program\Log Parser 2.2>logparser "SELECT EXTRACT_TOKEN(field1,2,'/') AS [Domain Name], SUM(field2) AS [Sum] INTO 'D:\TEMP\newFile.csv' FROM 'D:\TEMP\inputFile.csv' GROUP BY EXTRACT_TOKEN(field1,2,'/')" -i:CSV -headerRow:off

    This will save the result into the file newFile.csv.

    Before using the above, you need to check the bold text. You may need to change the path to the input file, and if you have headers in the file, you need to change -headerRow:off to -headerRow:on. I have tested the above with the following data (this is the exact data, no header line) stored in inputFile.csv:

    http://www.google.ch/bla/bla.cfm,2343
    http://www.google.ch/bla/.cfm,232
    http://www.google.ch/bla/,1
    http://www.google.ch/asdfasdf/asdfjeasdf/,2
    http://yahoo.de/asdf/,2
    http://yahoo.de/adsf/asdf.casdfjeh,3

    The output is the following (copied from the command windows):

    Domain Name      Sum
    ------------------
    www.google.ch     2578
    yahoo.de              5

    Kristofer Gafvert

    http://www.it-notebook.org