Forum Discussion

stej's avatar
stej
Frequent Visitor
9 years ago
Solved

Why text is changed even when parsed as text

I try to parse IIS log files. The log files have this structure:      I load the files from folder, so I combine the files like this     I select "space" as separator, the line is par...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    stej

     

    After you Apply the Combine Binaries. In Queries Panel you will find a Folder Name Sample Query and inside of this a Query with the Name Transform Sample Binary From "Your Folder". Select this and go to Advance Editor and change the type date to text.

     

     

     

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi stej,

     

    You can try to use below formula which used to loop the specified folder and combine the file to one table.

     

    let
    LoadCSVFileAndCombine= (FilePath as text) as table => 
    let
        Source = Folder.Files(FilePath),
        #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv"),
        Custom = Table.SelectColumns(Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content])),"Custom"),
        Combine= Table.Combine(Custom[Custom])
in
    Combine
in 
    LoadCSVFileAndCombine

     

    Use steps:

    1. Open query editor

    2. Add blank query.

    3. Open the advanced editor.

    4. Paste above formula.

    5. Use folder path to invoke above function.

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi stej,

 

I'd like to suggest you save the iis log file to csv, then load csv file to query editor.

 

 

Then you can choose the specific type of column at "change type" steps.

 

 

Full query:

 

let
    Source = Csv.Document(File.Contents("C:\Users\xxxxx\Desktop\Log.csv"),15,"",null,1252),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type time}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", Int64.Type}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}})
in
    #"Changed Type"

 

Regards,

Xiaoxin Sheng

  • stej's avatar
    stej
    Frequent Visitor
    9 years ago

    Anonymous yes, that might work probably, But how do I combine more files from the folder together? Note that I don't know their name and count, just the folder path.

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      9 years ago

      stej

       

      After you Apply the Combine Binaries. In Queries Panel you will find a Folder Name Sample Query and inside of this a Query with the Name Transform Sample Binary From "Your Folder". Select this and go to Advance Editor and change the type date to text.

       

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi stej,

       

      You can try to use below formula which used to loop the specified folder and combine the file to one table.

       

      let
    LoadCSVFileAndCombine= (FilePath as text) as table => 
    let
        Source = Folder.Files(FilePath),
        #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv"),
        Custom = Table.SelectColumns(Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content])),"Custom"),
        Combine= Table.Combine(Custom[Custom])
in
    Combine
in 
    LoadCSVFileAndCombine

       

      Use steps:

      1. Open query editor

      2. Add blank query.

      3. Open the advanced editor.

      4. Paste above formula.

      5. Use folder path to invoke above function.

       

      Regards,

      Xiaoxin Sheng

      • stej's avatar
        stej
        Frequent Visitor
        9 years ago

        Anonymous

        That also did the trick. I had to change it a little bit and give it custom separator, so the result is this (pasing here mainly for me ;)

         

        let
    LoadCSVFileAndCombine= (FilePath as text) as table => 
    let
        Source = Folder.Files(FilePath),
        #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".log"),
        Custom = Table.SelectColumns(Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content], null, " ")),"Custom"),
        Combine= Table.Combine(Custom[Custom]),
        #"Renamed Columns" = Table.RenameColumns(Combine,{{"Column1", "date" }, {"Column2", "time" }, {"Column3", "s-sitename" }, {"Column4", "s-computername" }, {"Column5", "s-ip" }, {"Column6", "cs-method" }, {"Column7", "cs-uri-stem" }, {"Column8", "cs-uri-query" }, {"Column9", "s-port" }, {"Column10", "cs-username" }, {"Column11", "c-ip" }, {"Column12", "cs-version" }, {"Column13", "cs(User-Agent)" }, {"Column14", "cs(Cookie)" }, {"Column15", "cs(Referer)" }, {"Column16", "cs-host" }, {"Column17", "sc-status" }, {"Column18", "sc-substatus" }, {"Column19", "sc-win32-status" }, {"Column20", "sc-bytes" }, {"Column21", "cs-bytes" }, {"Column22", "time-taken" }})
    in
        #"Renamed Columns"
in 
    LoadCSVFileAndCombine

         

        Thanks, accepted.