cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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?

 

 

1 ACCEPTED SOLUTION
mcybulski
Solution Specialist
Solution Specialist

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

View solution in original post

4 REPLIES 4
mcybulski
Solution Specialist
Solution Specialist

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

View solution in original post

v-juanli-msft
Community Support
Community Support

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
Community Support
Community Support

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
Solution Sage
Solution Sage

Can you share sample data or PBIX file?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors