Issue with Split Column with Delimiter from Sharepoint CSV

[Anonymous]

Hi

I have a csv file with 14113 columns to load to Power Query. It goes something like this

"id","address","score"

"129023","new york, USA","30"

If I download the csv file to my local and get the data to Power Query via "From Text/CSV", Power Query can recognise the it as csv and split the column with column delimeter: [Delimiter=",", Encoding=1252])

id	address	score
129023	new york, USA	30

However, if I upload the csv to sharepoint and get the data to Power Query via "From Web", Power Query recognise as unstructured text and only load a single column to the editor. I did "Split Column with Delimiter" in the editor, but it is not doing the splitting well via due to occurance of delimiter (comma) in the data.

id	address	score	column1
129023	new york	USA	30

Is there anyway to fix it? This is being chosen under the manual column split

Thank you

[Luis98]

Hi @Anonymous ,

I have worked with CSV in Sharepoint many times, i usually do it using the connector from Sharepoint folder. Try with this connector and if you have problems try to upload here a sample and i will try solve it.

[Anonymous]

Hi @Luis98 

Thanks for the suggestion. I am facing the same issue if connected to Sharepoint folder. It seems the issue starts when "alt-enter" is used in a cell.

https://we.tl/t-3JVvWglUzb

I have a test csv here, the last row column T (address) causing the issue if I split the columns manually with delimeter after connected to Sharepoint. However, it works well if I connect the file via local csv, when Power Query did the splitting.

Thanks

[Luis98]

Ok, i am trying with the CSV you sent, i think the problem you had is with the Encoding. Try to use this formula to extract de Csv file from the Sharepoint folder, adding a custom column:

Csv.Document([Content],[Delimiter=",", Encoding=1252])

This should be work, i am getting 6 rows with 14114 columns

[Anonymous]

I got a little confused, when the "sharepoint folder", do you mean this?

it asked for my site url, and I did the same to extract the file with the code:

let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data")),
    #"test online csv_https://jardon sharepoint com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/" = #"Filtered Rows"{[Name="test online.csv",#"Folder Path"="https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/"]}[Content],
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"test online csv_https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/",null,null,1252)})
in
    #"Imported Text"

 this is what I received:

 then I added your code, but I get an error: Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type.

let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data")),
    #"test online csv_https://jardon sharepoint com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/" = #"Filtered Rows"{[Name="test online.csv",#"Folder Path"="https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/"]}[Content],
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"test online csv_https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/",null,null,1252)}),
    #"Column Split" = Csv.Document(#"Imported Text",[Delimiter=",", Encoding=1252])
in
    #"Column Split"

I suspect my code is "column split" is wrong... 

[Luis98]

Ok, when you import the folder from Sharepoint you have to use the Csv.Document, not the SharePoint.files.

let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data"))
    #"Other columns removed" = Table.SelectColumns(Origen,{"Content"}),
    #"Custom column added" = Table.AddColumn(#"Other columns removed", "Custom Column", each Csv.Document([Content],[Delimiter=",", Encoding=1252]))

in
    #"Custom column added"

Try this

[Anonymous]

Hey, thanks for tips!

I can't get it yet, pretty sure I missed some steps, because when I extract the file, it is binary, and there is an error of converting type binary to text. (Expression.Error: We cannot convert a value of type Binary to type Table.)

let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data")),
    #"The Source" = #"Filtered Rows"{[Name="test online.csv",#"Folder Path"="https://jardon.sharepoint.com/sites/Supply/Shared Documents/Data Analysis/The Raw Data/"]}[Content],
    #"Custom column added" = Table.AddColumn(#"The Source", "Custom Column", each Csv.Document([Content],[Delimiter=",", Encoding=1252]))
in
    #"Custom column added"

this is shown in #"Filtered Rows" step, I think the source is still binary.

Is there any way to convert it to table and fix the error? Thanks!

[Luis98 · Accepted solution]

Yes, to this binary type column you have to apply the formula i told you in the second message.

Create a custom column and write that:

Csv.Document([Content],[Delimiter=",", Encoding=1252])

This would be the code:

let
    Source = SharePoint.Files("https://jardon.sharepoint.com/sites/Supply/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "The Raw Data")),
    #"Custom column added" = Table.AddColumn(#"Filtered Rows", "Custom Column", each 
Csv.Document([Content],[Delimiter=",", Encoding=1252]))

in
    #"Custom column added"

[Anonymous]

i got it now! thank you so much 🙂

[Luis98]

You´re welcome🙂