Hi Mynda
I have Microsoft 365. I could not use the .txt and .csv file. They were both in only 1 column. It looks like this 65,Returned . I save the .csv file as a .xlsx file, and made a Table of the 1 column. I then save a copy as .xlsx. In the copy I made an extra column in the Table, and deleted all the info in the Table. I then made a Function in each column. In each Function I used the original Table as reference.
For the numbers:
=LEFT(pp_3_returns.xlsx!Tabel1[@[Order ID,Status]];LEN(pp_3_returns.xlsx!Tabel1[@[Order ID,Status]])-(LEN(pp_3_returns.xlsx!Tabel1[@[Order ID,Status]])-SEARCH(",";pp_3_returns.xlsx!Tabel1[@[Order ID,Status]]))-1)
For the text:
=RIGHT(pp_3_returns.xlsx!Tabel1[@[Order ID,Status]];LEN(pp_3_returns.xlsx!Tabel1[@[Order ID,Status]])-SEARCH(",";pp_3_returns.xlsx!Tabel1[@[Order ID,Status]]))
I then imported it to Power Pivot. It looks good.
I would like to know, if you have other solutions Mynda?
Thank you.
Hi Lasse,
Welcome to our forum! It's odd that you couldn't use either file. I wonder if it's a regional setting causing the issue with your system.
In regards as to whether there is another solution, you could have used Text to Columns (on the Data tab of the ribbon) to split the text by the delimiter in Excel.
Mynda
Hi Mynda
Thank you for the Text to Columns suggestion. Much easier to use.
It could be a regional setting, as I live in Denmark.
Thanks again.
Hi Mynda,
Just to note that I had the same issue as Lasse on 3.06, also using 365 but it remained as 1 column when importing to Power Pivot. I took your advice using the text to columns and imported as an xlsx file. (I am in the UK)
Many thanks , enjoying the courses so far
Mike
Hi Mike,
That's odd as we would have very similar regional settings and I am able to import the CSV file with no problems using 365.
Of course, the ideal way to get your data to Power Pivot is via Power Query, where I'm confident you won't have this issue.
Mynda
Hi Mynda,
Just echoing what Lasse and Mike indicated. I had to use Power Query to split the columns to get the data into Power Pivot. Same results, only one column when trying directly from the text file to Power Pivot. Do the quotation marks at being and end impact anything? I'm in the United States.
Bob
Hi Robert,
It appears to be caused by a change in 365. The file used to work and now with 365 it causes problems. There's a note under the video where you can download a CSV file to use instead.
Mynda
Update - I ended up just creating an .xlsx file and adding that so I could move on. I hope I haven't interfered with a step further on.
I am having the same problems. On my personal laptop with 365 it works beautifully with the .CVS file. However, on my work laptop with 2016 I can't get either the text or .CVS files to work. It remains one column, not two. I noticed others found work around, but I have no idea what they are saying. I haven't taken the Power Query class yet. Any idea what is causing this and how I can fix it so I can proceed?
Thanks,
Angela
Hi Angela,
There must be a bug in that version of Excel. You can import the data by copying and pasting it in for the purpose of moving on to the next lesson. It won't cause any issues later on.
Mynda
