Orrin on 26 May 2016 21:54:49
Many times extracts from legacy ("vintage artisanal") sources end up being fixed length files and these systems often times have hundreds if not thousands of columns. Additionally these systems provide a means to export the schema or layout of the data into standard formats like COBOL copybooks or XML documents. In order to use this with PowerBI today a conversion process has to take place between the source data definition layout and a Power Query formula (DAX?). This is a cumbersome process but one that could be automated given some industry standard (ETL tools like SSIS & Informatica offer capabilities like this) layout definitions.
Thank you.
Administrator on 27 May 2016 01:00:26
Thanks for the suggestion - Are you looking for a way to split lines of text into multiple columns where columns are defined as "fixed width" rather than by a "delimiter character", as part of the Get Data Text/CSV import experience?
- Comments (4)
RE: Fixed length files layout import
yes
RE: Fixed length files layout import
In Spain Tax Agency (AEAT) work at "vintage artisanal" files. The solution Get Data Text/CSV import make a split in cut at left length very slow and with errors. Excel or Power Query solution at import step query is more simple.
RE: Fixed length files layout import
For example. I started working with Area Health Resource Files from the US Department of Health & Human Services (from this site: http://ahrf.hrsa.gov/download.htm). This file contains 3,000 + columns and comes as an ASCII fixed length text file (there are other options but sometimes there are not). Efficiently loading this file into Power BI via Power Query would require a very lengthy transformation - but they also make a SAS layout available which would expedite the process of defining the data.
RE: Fixed length files layout import
Yes, but not just a method but a tool that allows me to (for example) upload a COBOL copybook, SAS layout, SSIS layout, etc. and it would convert that to the DAX query used by Power Query to transform the data.