Hello. The ERP system we're using at my place of employment is old and not sequel - boo. Anyhows, when I attempt to import text files generated from this system (Progress database - BOO!) Power Query is not detecting data properly. Are there any workarounds in Power Query to import data from text files not being detected by Power Query. Currently the file origin exported from our ERP system is 437 : OEM United States. There are several other file origin options available however I'm clueless on another file origin I cold use which might be recognized by Power Query.
Feedback would be appreciated but I'm getting a sinking feeling, this database isn't going to be compatible with Power Query which means my employment here is in jeopardy.
Hi Eric,
File Origin is not the problem. You can use 437, or 1252, you will have the same result, file origin will not detect the custom file structure. File origin is just the character encoding, you can even use 65001 - Unicode UTF-8 char encoding.
When you import the text file, all the data will be placed in a single column. As mentioned already, you have to split the column by delimiter (Power Query will properly identify the pipe delimiter)
In the first step, power query automatically used file origin 1252:
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:UsersCatalinDesktop2018-LAF-COGS-DATED-AVG-SOLR20.txt"), null, null, 1252)}) (you can change here 1252 with 437 or any encoding you want to test.)
I applied a few steps to clean and reformat the data, but there are small chances to work on another exported file from that system if the structure will not be identical.
You should not rely on magic, just ask the employer to export data with a tabular export template, not with a template designed for visualizing data, any system has more than 1 export template configured, and usually it is possible to configure your own export template.
See attached file, hope this is the format you wanted, I removed some data rows as they were duplicated.
