Skip to main content

Issues

Under Review

TEXT import in the new version no longer imports plainly

Vote (1) Share
Erik's profile image

Erik on 30 Aug 2016 00:51:14

I'm importing a csv file that has a header record with some text on when it's created, and a trailer record stating the amount of rows. It imports fine in Excel 2013.
PowerBI desktop could do an "untouched" text import, with all lines intact, allowing me to remove header and trailer records, and then split the column using CSV-style splitting on Comma, and keeping all data between "" (including carriage returns) in one field.
Again, this worked before, and still works in Excel 2013, but today's version of PowerBI Desktop is hell-bent on interpreting the imported textfile with a delimiter, also when I change the filename to a .TXT extension (from .csv).
Even if I set a custom delimiter of '&^^' or so, it still screws up the carriage return between "".

I now work around this by importing into Excel first, but it adds manual steps to my workflow.

Can you please unbreak the import features?

PS
I note that I have a PBIX-file that I created in the past and I refresh each week that also does this to another CSV file, and that one still works. So I conclude the wizard-to-code is broken, not the importcode itself.

Comments (5)
Erik's profile image Profile Picture

4e9cb463 9881-4d18-803c-8dbad7865e75 on 05 Jul 2020 22:29:54

RE: TEXT import in the new version no longer imports plainly

I found a workaround in PowerBI Desktop.
1) Import the file as a CSV
2) The file will import and will be screwed up
3) Go back to the Source "wheel" icon and edit the CSV settings
a) You can set the file type (i.e. mac, Windows, ISO..)
b) You can set the comma separator
c) You can set to ignore CF/LF between ""

It will work sweet afterwards.

Erik's profile image Profile Picture

4e9cb463 9881-4d18-803c-8dbad7865e75 on 05 Jul 2020 22:29:24

RE: TEXT import in the new version no longer imports plainly

I asked, but cannot even send the anonymized testfile. We'll have to do an online session.

The main difference between old and new seems to be the use of Source = Csv.Document in stead of Source = Table.FromColumns.

I do have to say with this CSV file, Excel 2013 imports perfectly, but no matter what I do now, the PowerBIDesktopfunction Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv) seems to not handle CR's in field well anymore.

Erik's profile image Profile Picture

ac643e7c e800-ea11-a811-000d3a4f1ebb on 05 Jul 2020 22:29:06

RE: TEXT import in the new version no longer imports plainly

My apologies
My email is
Chass@microsoft.com

Erik's profile image Profile Picture

4e9cb463 9881-4d18-803c-8dbad7865e75 on 05 Jul 2020 22:29:04

RE: TEXT import in the new version no longer imports plainly

I can do a subset extract and anonymize. How can I get the file to you?

Is there an advanced editor for the import-process? I was not aware.

Erik's profile image Profile Picture

ac643e7c e800-ea11-a811-000d3a4f1ebb on 05 Jul 2020 22:29:00

RE: TEXT import in the new version no longer imports plainly

Hello Erik,
In the advanced editor are the steps for the two PBIX the same?
Any chance of sending me a small sample of the CSV file so i can file the bug against the wizard?
Thanks
Chuck