Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Import text with fixed width columns based on delimiter?

Hi Everybody,

 

I’m trying to import data from a text file which is formatted with fixed widths which can be determined by delimiters on the second row (dashes with spaces as delimiters).

 

The column widths can change with each data load, so I would like to dynamically detect the column widths based on the dashes and spaces in the second row and do the splitting according to that. Power BI’s text import does quite a poor job of detecting the column widths, so it would be quite laborious to try to use that each time.

 

Could anybody suggest how I could split this type of data – I assume I would need to generate a list of the positions of the delimiters in the second row and use that in a splitter function.

 

Below is an example of the first two rows of the text file:

 

Column1    Column2       Column3   
---------- ------------- ---------


Many thanks,

Mark.

Need Help

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Anonymous 

    I'm sure there's a more elegant solution but this works.  You'll need to change the source filelocation/name to suit your environment.

     

    let

RT = (values as list) as list =>

let
    RTList = List.Generate
    ( 
        ()=> [ RT = values{0}, i = 0 ],

        each [i] < List.Count(values),

        each [RT = [RT] + values{[i] + 1}, i = [i] + 1],
        
        each [RT]
    )
in
    RTList,

    Source = Csv.Document(File.Contents("D:\temp\fixed-width-file.txt"),null,{0, 11, 25},ExtraValues.Ignore,1252),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Kept First Rows" = Table.FirstN(#"Removed Top Rows",1),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Widths", each Text.Length([Column1])),
    Widths = List.Combine({{0},RT(#"Added Custom"[Widths])}),
    RealSource = Csv.Document(File.Contents("D:\temp\fixed-width-file.txt"),null,List.RemoveLastN(Widths),ExtraValues.Ignore,1252)
in
    RealSource

     

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi PhilipTreacy ,

       

      Thanks for helping and sorry for my late reply.

       

      I tried the code and changed the Source and RealSource to point to my file. 

       

      It isn't quite working yet. The source statement includes a list of numbers {0, 11, 25} which splits the source into three coumns at these points. I've added a screengrab below:

       

      The step where the RT function is called returns a list of these column widths:

      I wondered if the RT function should have a condition to identify the position of the spaces since they are the delimiters?

       

      Many thanks,

       

      Mark.

       

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Anonymous 

    Try this code with some modifications

     

    let

Filename = "D:\temp\fixed-width-file.txt",

RT = (values as list) as list =>

let
    RTList = List.Generate
    ( 
        ()=> [ RT = 0, i = 0 ],

        each [i] < List.Count(values),

        each [RT = if values{[i]} <> "-" then [i]+1 else null, i = [i] + 1],
        
        each [RT]
    )
in
    RTList,

    Source = Csv.Document(File.Contents(Filename),[Delimiter="0", Columns=1, Encoding=1252]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Added Custom1" = Table.AddColumn(#"Removed Top Rows", "Custom", each Text.ToList([Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    Widths = RT((#"Expanded Custom"[Custom])),    
    RealSource = Csv.Document(File.Contents(Filename),null,List.RemoveNulls(Widths),ExtraValues.Ignore,1252)
in
    RealSource

     

    I'm loading this file from my D:\temp folder.

    If you have more issues please supply the CSV you are using, or at least the first few rows of it.

    Thanks

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.