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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User
Super User

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
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors