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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

No Text Delimiter Option While Importing Text/CSV Data in Power Query

 

Hello, 

I am trying to import Text/CSV file in Power Query, but while doing that I don't have an option to chose delimiter in the import window as it is shown in the following post response:

https://community.powerbi.com/t5/Power-Query/How-do-you-import-txt-with-tabs/m-p/955717#M32980

And there is only one column which I can't split into several columns as there is no delimiter choose option:

Only one column. No delimiter selectionOnly one column. No delimiter selectionWrong Source CodeWrong Source CodeProper code that I would like to have PQ inserted automaticallyProper code that I would like to have PQ inserted automatically

And after importing the source code looks like that:

= Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\MyUserName\Desktop\123.txt"), null, null, 1252)})

 

But should look like that:

= Csv.Document(File.Contents("C:\Users\MyUserName\Desktop\123.txt"),[Delimiter="#(tab)", Columns=19, Encoding=1252, QuoteStyle=QuoteStyle.None])

 

Why it can be like that? Why Power Query choses to use Table.FromColumns instead of Csv.Document? Are there any pre-settings required for Text/CSV file itself to be imported and to be able to turn on chose delimiter option?

 

7 REPLIES 7
ronrsnfld
Super User
Super User

I agree that is wierd to not see the delimiter field.  A sample file that can be used to reproduce your problem might be helpful in figuring out what's going on with that import wizard.

In the mean time, why don't you just write what you think is the proper code for that file yourself?

 

To start with:

    Source = File.Contents("C:\Users\MyUserName\Desktop\123.txt"),
    ToCSV = Csv.Document(Source,[Delimiter="#(tab)"])

Of course, you can and probably should add more arguments to the Csv.Document function and add some of the other things that the wizard usually does automatically, like promoting the first row to headers and setting the data types.

 

Hello

 

I have the same issue here 04/28/2022, I think something to submit to MS.

As a workaround I use the delimited manual option in Transform Power query after opening the file, as image below maybe it works for you

JOSELUISMTZRMZ1_1-1651164880442.png

 

Regards

 

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Which connector do you use in power bi?

We use "Text/csv", so to get a query like "Csv.Document(File.Contents(****".

 

Best Regards

Maggie

Anonymous
Not applicable

Hello @v-juanli-msft I use exactly text/csv but with specific file it doesn't work well as for the other files it's ok

Greg_Deckler
Super User
Super User

@Anonymous - What does your source data look like?

 

Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Ajinkya369
Resolver III
Resolver III

Import CSVImport CSV

 

Hey @Anonymous,

You just need to click on new source - > text/csv and then locate to your file. 

 

Then import screen will be opened where you can select the delimiter of your choice. 

 

You can find the Csv.Document in the source step.

 

You will get the CSV.document in the source stepeYou will get the CSV.document in the source stepe

 

 

There is no presetting for this. Please follow the above steps and let me know whether its working for you or not.

 

If your problem is solved then please accept this as solution. 

 

Thank you

 

Anonymous
Not applicable

Thank you, however it doesn't help. I tried to make my problem more clear with some pictures, please have a look.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors