PowerQuery to parse fixed width csv

Question

I have fixed width csv.txt, e.g.

Martin    Junger    12    
John      Wing      124

To load it with PowerQuery this syntax is used:

let
    Source = Csv.Document(File.Contents("C:\csv.txt"),null,";",null,1251),
    #"Split Column name" = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByPositions({0, 10}, false),{"name", "ColumnOther"}),
    #"Split Column surname" = Table.SplitColumn(#"Split Column name","ColumnOther",Splitter.SplitTextByPositions({0, 10}, false),{"surname", "ColumnOther"}),
    #"Split Column salary" = Table.SplitColumn(#"Split Column surname","ColumnOther",Splitter.SplitTextByPositions({0, 3}, false),{"salary", "ColumnOther"})
in
    #"Split Column salary"

So for every column in csv I need to create new Table.SplitColumn command which is not convenient at all. Is there any syntax to do it in one command?

Thank you

Answer 1

Although it's not mentioned in the doco, Csv.Document accepts a list as the "optional delimiter" argument, specifying the (zero-based) start position of each column. You can see this below in the Query generated by PQ when I pointed it at that file (e.g. using Excel 2016: Data ribbon / New Query / From File / From Text). The "optional delimiter" argument is: {0,10,20}

let Source = Csv.Document(File.Contents("C:\csv.txt"),null,{0,10,20},null,1252), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}}) in #"Changed Type"

Answer 2

You don't need to use the powerquery plugin for excel to import fixed width - it's built in.

Steps:

1. Data Tab From Text (Get External Data section)
2. Select your csv file
3. See that it brings up the Text Import Wizard
4. In: Original data type, choose "Fixed width"
5. Proceed through the wizard as appropriate