Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Csv splitting issue

Hi,

 

Im trying to split an exported csv file in power query. The split works for thousands of rows, but for some reason, few random rows do not react to the split, despite having the exact same delimiter. Any ideas as to why this is happening?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    In my experience, this problem usually occurs because the csv does not have properly quoted delimiters AND one of the fields contain a carriage return or line feed. I get around this issue by opening the file without delimiters and then using the SplitColumn function in PQ. Now you should be able to find the records causing the issue and handle accordingly.

     

    Regards,

    Mike

     

    let

Source= Table.FromColumns({Lines.FromBinary(File.Contents("C:\Transform Directory\Velocity Reports\12 + Month Reports\Bulk and Count Velocity.txt"), null, null, 1252)})

SplitCol= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), {"Column1.1", "Column1.2"})

in SplitCol

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    In my experience, this problem usually occurs because the csv does not have properly quoted delimiters AND one of the fields contain a carriage return or line feed. I get around this issue by opening the file without delimiters and then using the SplitColumn function in PQ. Now you should be able to find the records causing the issue and handle accordingly.

     

    Regards,

    Mike

     

    let

Source= Table.FromColumns({Lines.FromBinary(File.Contents("C:\Transform Directory\Velocity Reports\12 + Month Reports\Bulk and Count Velocity.txt"), null, null, 1252)})

SplitCol= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), {"Column1.1", "Column1.2"})

in SplitCol
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Could you show some sample data or screenshots?

     

    Maybe some rows have space so the function doesn't work well.

    If so, try to trim space in Edit queries

    https://www.youtube.com/watch?v=g3ws234FRes

    https://www.excelguru.ca/blog/2015/10/08/clean-whitespace-in-powerquery/

     

    Please check if you split the column in the right way.

    https://www.youtube.com/watch?v=zL_JXB_KfiA


    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case?
    If not, please let me know.
     
    Best Regards
    Maggie