How to fix CSV file where it shows _x000D_ LF in Notepad++


I have a python script which pulls the data from SQL and save the file in CSV Format. There are 80000 records but it shows in excel as 100000 records. So, I opened the csv file in Notepad++ and it shows _x000D_LF or LF and some other lines shows CR LF. I need to find a way how fix this issue. it should all show CR LF not _x000D_LF or LF. Attached is the CSV file.

it should be like this one below:

Please let me know what code can I write in VBA (excel macro) so that it can be fixed and I can save my file as xlsx format.



You will need to at least provide a sample file in Excel that uses dummy data in order to get accurate help. Otherwise, I would just tell you to do a find/replace using VBA that turns this _x000D_LF into this CR LF. However, without seeing that data, I couldn't be sure that that wouldn't mess other things up.

If you really want to fix the problem though, you will probably need to fix the python script.
don (rep: 1237) Jul 29, '16 at 2:40 pm
I have attached the .csv file for your reference.
manjarigoyal (rep: 2) Aug 5, '16 at 4:16 pm
Add to Discussion


Selected Answer

This is a problem with your python script exporting the data to CSV. 

However, the easiest way to get rid of these characters in Excel is to use Find/Replace.

Open in in Excel and hit Ctrl + F > go to the Replace tab > put _x000D_ in for Find what and nothing in for Replace with and then hit Replace All.



Thanks Don. It worked.
manjarigoyal (rep: 2) Aug 9, '16 at 9:24 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login