Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

0

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.

excel
posted by: manjarigoyal (rep: 2) on Fri Jul 29, 2016 at 1:47 pm
Answer
Discuss

Discussion

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: 1989) 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

Answers

0
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.

posted by: don (rep: 1989) on Mon Aug 8, 2016 at 4:24 pm
Discuss

Discussion

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