How do you import txt with tabs?

[Anonymous]

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.

[mjsch8686]

Hey,

Not sure if you're still struggling with this all these years later but I suddenly updated to Power BI 2021 and am now having the same issue.

This was my workaround = 

In previous versions of Power BI this was the source formula when importing tab delimited files:

= Csv.Document(Parameter1,[Delimiter="            ", Columns=25, Encoding=65001, QuoteStyle=QuoteStyle.None])  (The Delimter here is not spaces but rather an actual result of hitting "tab" on the keyboard)

I checked the source formula for the Power BI 2021 file (made in Jan 2021 Power BI).  The source formula shows as follows:

= Csv.Document(Parameter1,[Delimiter="#(tab)", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None])

I went in to my source formula and changed the "Columns" to 25 so now the Power BI 2021 formula shows like this:

= Csv.Document(Parameter1,[Delimiter="#(tab)", Columns=25, Encoding=65001, QuoteStyle=QuoteStyle.None])

This seems to fix my issue temporarily.   

I hope this helps.  If not you, then someone else struggling with issue.

-M-

[Greg_Deckler]

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"

[Anonymous]

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'

.

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?

[Josh_BI_UK]

---

@Anonymous 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'

.

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 @Anonymous ,

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 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" 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