0

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

  • I've never used powerquery, but what if the csv had delimiters instead of fixed width, would that make it easier? – Raystafarian Mar 11 '15 at 16:14
  • If it is delimited - then it can be parsed without any problems. But I have to work with fixed delimited. – pashtetGP Mar 11 '15 at 20:03
0

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"

0

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.