Csv splitting issue

[Anonymous]

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?

[Anonymous · Accepted solution]

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]

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

[v-juanli-msft]

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.

[AnkitBI]

Can you share sample data or PBIX file?