Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Issue with Split Column with Delimiter from Sharepoint CSV

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 g...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous 

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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