uploading giving funny results again?

[dpenny]

I went through a period of 1-2 wks where I could not upload a csv produced by Excel 2013. It always errored out. I fixed it by opening the file with a text editor and resaving, then uploading this version.  Oh well........

Today I am trying to upload a different csv produced by Excel 2013, it went into BI, but there is an extra column in BI that is not in my csv file. It's heading is "_Column" without the quotes.

All I did to produce this file is save a particular sheet (no formulas at all) as type csv.

What happened?

Where did this come from?
How do I prevent it?

[v-yuezhe-msft]

Hi @dpenny,

Do you connect to the CSV file from Power BI Service or Power BI Desktop?

I am not able to reproduce your scenario when connecting to CSV file in Power BI Desktop or Service. Would you please share me your CSV file with which I can test and post a screenshot about your current scenario?

Thanks,
Lydia Zhang

[dpenny]

I gave up on using the Service with csv files unless I run the csv files from Excel through a true "editor" like TexPad or Note++ and re-save to a txt file, then manually change the extension to csv.

What a pain. You'd think sticking within the MS stack that things would work well.

[v-yuezhe-msft]

Hi @dpenny,

How about connecting to CSV files from Power BI Desktop? We will appreciate that if you can share us your CSV file so that I can test.

Thanks,
Lydia Zhang

[dpenny]

1) sure I can send/share my csv file, but how, there is no "attached button/icon" in this forum blog that I see.

1) i insert  next a screen snip of what happens

2) how do I attach a csv file? there is no icon/button

3) this is from BI DeskTop

[v-yuezhe-msft]

Hi @dpenny，

In your scenario, please select Column8 in Query Editor of Power BI Desktop, then click “Remove Columns” under Home ribbon as shown in the following  screenshot to remove the column8.

And in Query Editor of Power BI Desktop , you can select the first row in  your table, then click “Use First Row As Headers” under Transform ribbon, this way, you will get original column title.

In addition, you can upload the CSV file to OneDrive and post shared link of it in this forum.

Thanks,
Lydia Zhang

[dpenny]

I found out the hard way to use query editor to "fix" the csv.

The real point is that BI should not have interpretted the csv file this way. This is a bug or a design flaw.

And why after I run it through a real text editor, and then upload to BI does it work just fine????

With respect to putting my csv onto OneDive, I dont have an account, and I should not have to get one just to share files to help debug BI.

If I sound angry and annoyed, I am. I expect MS products to work seamlessly with each other (maybe not with other companies products).

[v-yuezhe-msft]

---

@dpenny wrote:

I found out the hard way to use query editor to "fix" the csv.

---

Hi @dpenny,

What is the problem when you use query editor to remove column and change column heading? Would you mind sharing me the CSV file with dummy data so that I can test it?

Thanks,
Lydia Zhang

[dpenny]

I can't re-produce the situation. I will try again.

I think I may have deleted the cvs file. I started running all my data through a "text editor" before uploading to BI desketop or BI service.

[v-yuezhe-msft]

Hi @dpenny,

Is your issue resolved after performing the above steps? The issue you get doesn't happen to all CSV files. In my sceanario, Power BI Desktop connects to CSV as expected.

Sometimes, due to incorrect data format in CSV, Power BI Desktop cannot recognize the column headings. In this scenairo, we can use features in Query Editor to solve it.

Thanks,
Lydia Zhang

[dpenny]

what does

incorrect data format in CSV

mean?

Your suggestion is likely true, BUT,

1. Excel does not have any probs with this file
2. text editors (TexPad, Note++, etc.) don't have any probs with this file.

Since BI is Miscrsoft's next BIG thingy, you'd think it would be smarter than all prior products, no?

And furthermore, if it is incorrect data format in CSV you'd also think that BI could tell you something like

at line xyz, unrecognized char (in hex), etc etc.

[Baskar]

cool,

U r saying the latest changes are not shown right ?

May i know where u looking for the latest change in Report or Dashboard ?

if in Dashboard, dashboard won;t give u the latest file data untill the data resfresh .

[dpenny]

Report, not dashboard.