Splitting Data from a Text file into Columns using Power Query

[jbragg6625]

Hello,

I am having trouble splitting the data I have in a Text file into separate columns in power query. The columns in the text file are separated by a series of five spaces instead of a tab. This means that the normal method of using split comma with the delimiter does not work.

The character length of the data in each column is variable. Also the text file is automatically put into the folder in the current format and have no way of changing that.

Is there some way that I would be able to split the data into separate columns?

 

[sheetspread]

Can't you use ctrl-h to replace five spaces with another delimiter? Or the trim function then split by space?

 

[Weazel]

maybe split column by delimiter....custom....enter 5 spaces....at each occurrence

actually it might be easier to use replace values....value to find would be 5 spaces....replace with would be 1 space then split column by delimiter....space....at left most....click ok

 

[jbragg6625]

maybe split column by delimiter....custom....enter 5 spaces....at each occurrence

actually it might be easier to use replace values....value to find would be 5 spaces....replace with would be 1 space then split column by delimiter....space....at left most....click ok

I will try the replace values idea but replace it with a comma instead. I can't believe I didn't think of that myself.

Thank you for the help.

 

[anvg]

Hi
It is an example text

1     text N1     12345.33     10/15/2014
2     text N3     456     10/19/2014
3     text A7     78965126     10/22/2014

It is a power query code for splitting it to columns

let
    source = Table.FromColumns({Lines.FromBinary(File.Contents("d:\Path\FileName.txt"), null, null, 1252)}),
    customSplitter = Splitter.SplitTextByDelimiter("     ", QuoteStyle.None),
    return = Table.SplitColumn(source, "Column1", customSplitter, {"Id", "Company", "Amount", "Date"})
in
    return

Regards,