cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

How do you import txt with tabs?

I have a text file that I can open up in excel and it comes thru just fine (with tabs) but when I import in PowerQuery it puts everything in one column?

 

First screen show from PowerBI...there is only one column (there should be many). Second screen shot is from Excel and this is what I want. I'm using the same source file to open so not sure why PowerBI opens it in one column but Excel will break it apart. I must be missing a simple step.

text with tabs pb.png

 

text with tabs.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: How do you import txt with tabs?

Make sure you are using Text/CSV as your data source. You should get a window like the following (see below). Make sure the Delimiter is set to Tab, Power BI may have not auto-recognized it. Also, I tried this on a tab delimited file and here is the Power Query code that worked to bring it in:

 

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\tabs.txt"),[Delimiter="	", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}})
in
    #"Changed Type"

 

 

image.png


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

Re: How do you import txt with tabs?

I am using the import functionality but it's not giving me a choice on the columns. First I select Get Data and chose 'Text/CSV'get data.png

 

 

 

.

 

 

 

 

 

 

 

 

 

I then select and open the text file...but only one column appears and it sticks all of the data in the first column. how do I get PowerBI to recognize there are tabs?

 
Highlighted
Advocate I
Advocate I

Re: How do you import txt with tabs?


@jcueland wrote:

I am using the import functionality but it's not giving me a choice on the columns. First I select Get Data and chose 'Text/CSV'get data.png

 

 

 

.

 

 

 

 

 

I then select and open the text file...but only one column appears and it sticks all of the data in the first column. how do I getPowerBII to recognize there are tabs?

 

 

 

Hi @jcueland ,

 

Picking up from your follow up question, please have a look at the following screenshots.

 

1) After you select "Text/CSV", via the Get Data window (depending on what version of Power BI desktop you're using) you should see the "Load/Transform" window. This is your first opportunity to tell Power BI what type of CSV file it's dealing with.

 

a. The menu option to select different delimiters used to breaks-up / separate CSV data columns should be at the top of the window, in a drop-down called "Delimiter". For your file you need to select "Tab".

 

b. "File Origin": The CSV encoding format used to encode the CSV can also be important if your CSV file contains special character sets outside of ASCII (e.g. Non-American Standard Code). See below for links which explain this in more detail.

 

c. I usually hit "Transform Data" at this point to further clean my CSV file.

 

Load/Transform windowLoad/Transform window

 

 

2) If you already have a query underway, then you can access the CSV configuration window via the

 

d. "Source" step within the "Query Settings pane". This should open the CSV configuration window again (See d. in the below screenshot).

 

e. the "Delimiter" options can be found at the bottom of this window.

 

Configuration window for the "Source" transformConfiguration window for the "Source" transform

 

If this doesn't solve your problem, there may be an issue with the way your CSV/Text file has been formatted. It may have been corrupted or not saved using a consistent delimiter (like tabs, or commas). If you’re able to post the file or a screenshot of it opened in notepad, one of our PWRBI family community members might be able to spot the issue.

 

Failing that, if your file opens in Excel and is formatted in columns correct, try saving it as an excel (*.xlsx) file and then importing it into Power BI.

 

 

Links to more info on CSVs

 

General advice on CSVs
https://donatstudios.com/CSV-An-Encoding-Nightmare

 

w3schools on charsets (i.e. ASCII, Windows-1252, UTF-8)
https://www.w3schools.com/charsets/default.asp

 

Wikipedia.org article on Comma-separated_values
https://en.wikipedia.org/wiki/Comma-separated_values

 

 

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021