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 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])

idaddressscore
129023new york, USA30

 

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.

idaddressscorecolumn1
129023new yorkUSA30

 

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

 

Thank you

 

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"

     

     

9 Replies

  • 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