Force Imported as Text

[Emmett52]

Hi
How would you adjust below code where :
• the source is based on a table so is dynamic.
• I want to force the loaded file (type is .prn) to be treated as text file rather than csv.

As is, I am running into "DataFormat.Error: There were more columns in the result than expected."

[COLOR=#373737][FONT='inherit'][FONT=arial]let
DirectoryTable = Excel.CurrentWorkbook(){[Name="tbl_Directory"]}[Content],
DirectoryColumn = Table.Column(DirectoryTable , "Directory"),
DirectoryFirstValue = List.First(DirectoryColumn),
Source = Folder.Files(Text.From(DirectoryFirstValue )),
LoadBinaryAsTable = Table.AddColumn(Source, "Custom", each Csv.Document([Content])),[/FONT][/FONT][/COLOR]
[COLOR=#373737][FONT='inherit'][FONT=arial]#"Expanded Custom" = Table.ExpandTableColumn(LoadBinaryAsTable, "Custom", {"Column1"}, {"Custom.Column1"}),
[/FONT][/FONT][/COLOR]

I've tried to combine with below snippet of code, but not sure how to combine:

[COLOR=#373737][FONT=Georgia][I][FONT=arial]let
fnRawFileContents = (fullpath as text) as table =>
let
Value = Table.FromList(Lines.FromBinary(File.Contents(fullpath)),Splitter.SplitByNothing())
in Value,[/FONT][/I][/FONT][/COLOR]
[COLOR=#373737][FONT=Georgia][I][FONT=arial]Source = fnRawFileContents("C:\Test\ThisIsAnExample.CSV")[/FONT][/I][/FONT][/COLOR]
[COLOR=#373737][FONT=Georgia][I][FONT=arial]in
Source[/FONT][/I][/FONT][/COLOR]

I'm not sure how to apply the function to the first section of code

Many Thanks for any Assistance!!

 

[Emmett52]

When I try something like below, I get error "Expression.Error: We cannot convert a value of type Table to type Text."

let
DirectoryTable = Excel.CurrentWorkbook(){[Name="tbl_Directory"]}[Content],
DirectoryColumn = Table.Column(DirectoryTable , "Directory"),
DirectoryFirstValue = List.First(DirectoryColumn),


let
fnRawFileContents = (fullpath as text) as table =>
let Value = Table.FromList(Lines.FromBinary(File.Contents(fullpath)),Splitter.SplitByNothing())
in Value,


Source = fnRawFileContents(Folder.Files(Text.From(DirectoryFirstValue ))),
LoadBinaryAsTable = Table.AddColumn(Source, "Custom", each Csv.Document([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(LoadBinaryAsTable, "Custom", {"Column1"}, {"Custom.Column1"}),

 

[gazpage]

Are you on a recent / the latest version of Power Query?

You shouldn't need to futz with the code. Import as csv, and when you are in the query editor click the cog next to Source and change to Open as Text File. You've probably got a dodgy header row so you will need to get rid of that and split the column by you delimiters in the editor.

 

[Emmett52]

Its not a simple CSV file, it's full of bad rows all the way through the file, hence the need to import as single column text. I then use a number of various Text.Start to pull out the data I need.
Is it possible to merge the 2 codes above to get what I need?

 

[gazpage]

Well what happens when you select open as text file?

 

[Emmett52]

I have opened as text file, and applied all the formatting steps. Issue is that when user next refreshes the data, the workbook name / location will not be same, so I added a worksheet which is easily updateable (by someone with so little knowledge of excel that this was easiest method) so that they could populate the name and location into a table in the workbook.

 

[gazpage]

You could do it with a function. I would do it as follows

1. Create a one cell defined name in your workbook, enter your full filepath as it appears in the code, excluding the quotes
2. Create a new blank query and type =Excel.CurrentWorkbook()
3. In the list of table and range names, click the green table next to your range
4. You should have a one row, one column table with you file name. Right click in the heading and choose drill down.
5. You should now have a one item list. Rename the query something like MyFilename
6. Replace the filepath, including the quotes, in your original code with MyFilename{0}