Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
dcasorso
New Member

import text file

I have multiple text files I want to import into tables. They say it is ASCII format, but there are no delimiters. The way it was setup is the first row has a number dashes and then a space, the space indicates the number of characters for each column. My issue is I cannot figure out how to repeat this import without counting the columns each time and entering it in as a delimiter, for example - Csv.Document(File.Contents("C:\Users\Dean\Downloads\SPUD0507.txt"),null,{0, 20, 53, 63, 70, 100, 108, 132, 145, 151, 180, 194},null,1252).

 

Is there someone out there that has completed a task similar to this and is willing to show me how to do it? There is an example of the data

 

Capture.PNG

2 REPLIES 2
Eric_Zhang
Microsoft Employee
Microsoft Employee

@dcasorso

 


@dcasorso wrote:

I have multiple text files I want to import into tables. They say it is ASCII format, but there are no delimiters. The way it was setup is the first row has a number dashes and then a space, the space indicates the number of characters for each column. My issue is I cannot figure out how to repeat this import without counting the columns each time and entering it in as a delimiter, for example - Csv.Document(File.Contents("C:\Users\Dean\Downloads\SPUD0507.txt"),null,{0, 20, 53, 63, 70, 100, 108, 132, 145, 151, 180, 194},null,1252).

 

Is there someone out there that has completed a task similar to this and is willing to show me how to do it? There is an example of the data

 


I do think you should enter the {0, 20, 53, 63, 70, 100, 108, 132, 145, 151, 180, 194} to make things not complicated. However you don't have to count the columns. What you need is a good text editor, such as NotePad++. You'd get the position numbers when putting the input cursor before a space.
Capture.PNG


ImkeF
Community Champion
Community Champion

I'd try the following:

 

Select the first record (Source{0}) and add a column: Text.PositionOf([Column1], " ", Occurrence.All)

 

This should return a list with all positions of blanks. Fill in this stepname as a variable into the Csv.Document call (instead of the list with the hardcoded numbers)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.