Folder load text file , Where can i info Power quary to load which column data ?

[Anonymous]

Hi All

I have 2 similar raw data file text format , i wanted to load using folder :-

Below link is my raw data file :-

https://www.dropbox.com/sh/irllhkmjtlegwz6/AAAZHoRtvfbdWoVAHo1Ei8f8a?dl=0

file 1

File 2

Upon load the both data into PBI m i am unable locate how to enter text delimited column number to enter , as i notice that it combine CUST_ID and COMPANY and SALES field into one field :-

I go to advance editor  , i cannot find text delimited for me to enter the column number :-

Can some one share with me where go wrong ?

when i try to folder load CSV file , i don't have any issue.

Paul

[Anonymous]

Hi All

Does Folder load only suppot CSV ?

Paul

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

The problem here is that you are using files without delimiters.  The data is separated by spaces when it columns shoudl be separated by something like a comma.

You will continue to have issues like this unless you can get these source files formatted differently.  Using spaces as delimiters is not good practice.

I don't know why the Get Data -> Folder isn't recognising the files as delimited, but it's to do with the spaces.  

To get around the problem, I opened the text files, added a comma after each column like so

A11111,   AIRYCOOL ENGRG & SERVICES,                                   42 
A22222,   ALLTRONIC COMPUTER (S) PTE LTD,                              29 
C33333,   CHEN HOCK HENG MACHINERY P/L,                                26 

Then used Get Data-> Folder and the files were recognised as delimited.  In the import dialog I changed it to fixed width rather than delimited and entered the column numbers 0,7,45

After completing the query I edited the text files again and removed the commas.

This all results in a set of queries contained in this PBIX file that can be used to import text files with a similar structure from a folder.  You will need to open the files query to change the Source step to the location of the folder where the files areon your PC.

Also, if you are importing files with more than 3 columns, you will need to work out the width of those columns and then change the column numbers in the Transform Sample File from files query to suit.

FYI, Get data -> Folder works for lots of different file types.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hi philip

thank you so much for your detail reply. I will convert my raw data to CSV FORMAT.

PAUL