Excel 2016 - Removing quote marks in CSV

[Tanquen]

I wish Excel had defaults to open CSV file as a CSV and I wish it would not make and changes or add random commas and so on...

I've opened a CSV and did a search and replace for some 4 digit numbers. After saving the CSV some strings have an added space at the end or so I thought. It's removing the quote marks.

This:
,"Configuration of Severity for On ",

Becomes this:
,Configuration of Severity for On ,

 

[Joe4]

That is because the double-quotes are really not necessary if there is no commas in the data.
The main purpose of the double-quotes (text qualifiers) is to differentiate literal commas in the data from commas that are used as field delimiters.

Note that if you are just doing a simple Find/Replace, you really don't need Excel to do that.
Most text editors (including NotePad, which comes with Windows) can do Find/Replaces, and won't mess up your formatting.

 

[Tanquen]

Not sure if removing the double-quotes will affect the application importing the CSV back in. The application only added it to some so I'm guessing it needs them to be there when you import them back in.

Using Excel because I need to search and see all the strings it finds before doing the replace. In the Find and Replace window I can step through them all add see that the replace will make sense. But then it deletes things and adds things and can't do a simple regex to find all these "T-1212" or "T_1212" and make them "T*1414".

Trying to use NotePad++ but it's harder as I can't see all the strings it finds first but I'm hoping it also will not add or delete things.

Would also be cool if you could just give it a list of string you wanted to swap.

 

[Joe4]

The application only added it to some so I'm guessing it needs them to be there when you import them back in.

It should automatically add them when required (i.e. you have a little comma in the middle of your data that is NOT a delimiter), and probably won't when they are not needed.
I would recommend testing trying to import this file into the other application and see if it works. Many applications can handle with/without.

Trying to use NotePad++ but it's harder as I can't see all the strings it finds first but I'm hoping it also will not add or delete things.

I use NotePad++, and their "Find" menu is almost exactly like Excel's, where you can go through one-by-one to identify the matches and replace the ones you want (just hit CTRL+F, exactly like you do in Excel, to bring up the Find/Replace menu).

Would also be cool if you could just give it a list of string you wanted to swap.

You could conceivably create VBA code to go through a list of values to replace (though if you need to individually inspect each one anyhow, I am not if that would work for you).
Note that there is also a caveat to using Find/Replace in Excel - it can do things like drop leading zeroes from entries that are all numeric. That can sometimes cause issues.

 

[Tanquen]

I use NotePad++, and their "Find" menu is almost exactly like Excel's, where you can go through one-by-one to identify the matches and replace the ones you want (just hit CTRL+F, exactly like you do in Excel, to bring up the Find/Replace menu).

But the list is not in the Find and Replace window and the text/document view is a mess of text. In Excel there are columns with headers and so on so it's much easier to see what string it found and what attribute it belongs to. I guess I can find them in Excel and then do the replace in NotePad++.

 

[Joe4]

But the list is not in the Find and Replace window and the text/document view is a mess of text. In Excel there are columns with headers and so on so it's much easier to see what string it found and what attribute it belongs to. I guess I can find them in Excel and then do the replace in NotePad++.

That "mess of text" is just what a CSV file looks like! It is simply a records (rows) of data, each field (column) separated by a comma.
It isn't anything "NotePad++" is doing, it is what a CSV file is, by its very definition.

Personally, I rarely (if ever) use Excel to view/open CSV files. If you really want to see what is contained in a CSV file, Excel is a horrible tool to do so (as matter as fact, one of the first things I do when I get a new computer is change the Windows default program for opening CSV file from Excel to a text editor like NotePad or NotePad++).
The reason why it looks so nice in Excel is because when you open it, it converts it to Excel format, and Excel may perform some of its own conversions on the data.
These include dropping text qualifiers, leading zeroes, converting anything that looks like a number to a number, and changing date formats.

Before you go through all the trouble though, I would first test it out to see if it really presents an issue if Excel make slight formatting changes to the file.
If it doesn't cause any issues, then there is probably no reason to jump through a bunch of hoops.

Another option would be to create some advanced VBA code that use something like ADO or DAO recordsets to loop through the CSV file, and make the edits it needs without ever actually opening the file in Excel. That involves a little more advanced VBA coding (I haven't used this method myself in about 10 years).

Old last trick I have seen some people do is to use VBA to change the file extension from "CSV" to "TXT" and open it as a Fixed Width (Space Delimited) or Tab-Delimited File.
Then it will return everything for each row into column A, unaltered. So Excel won't do any unwanted formatting changes.
You can do all your Find/Replace steps, save the file as the TXT, close it, and rename it as CSV.

Just some ideas to kick around.
Hope you find one that works for you.

 

[rlv01]

If you are ***SURE*** that your CSV data does not have commas in the data fields, then another quick and dirty method is

1. Use notepad to replace all double-quotes in the file with another character that you pick and are SURE is not used in the file, say "~" or "§" {char(167)}
2. Open the modified csv file in excel and do your search /replace. Save.
3. Use notepad to reverse what you did in step 1 and restore all the double quotes.

 

[Tanquen]

That "mess of text" is just what a CSV file looks like! It is simply a records (rows) of data, each field (column) separated by a comma.
It isn't anything "NotePad++" is doing, it is what a CSV file is, by its very definition.

Personally, I rarely (if ever) use Excel to view/open CSV files. If you really want to see what is contained in a CSV file, Excel is a horrible tool to do so (as matter as fact, one of the first things I do when I get a new computer is change the Windows default program for opening CSV file from Excel to a text editor like NotePad or NotePad++).
The reason why it looks so nice in Excel is because when you open it, it converts it to Excel format, and Excel may perform some of its own conversions on the data.
These include dropping text qualifiers, leading zeroes, converting anything that looks like a number to a number, and changing date formats.

Before you go through all the trouble though, I would first test it out to see if it really presents an issue if Excel make slight formatting changes to the file.
If it doesn't cause any issues, then there is probably no reason to jump through a bunch of hoops.

Another option would be to create some advanced VBA code that use something like ADO or DAO recordsets to loop through the CSV file, and make the edits it needs without ever actually opening the file in Excel. That involves a little more advanced VBA coding (I haven't used this method myself in about 10 years).

Old last trick I have seen some people do is to use VBA to change the file extension from "CSV" to "TXT" and open it as a Fixed Width (Space Delimited) or Tab-Delimited File.
Then it will return everything for each row into column A, unaltered. So Excel won't do any unwanted formatting changes.
You can do all your Find/Replace steps, save the file as the TXT, close it, and rename it as CSV.

Just some ideas to kick around.
Hope you find one that works for you.

I know that NotePad++ is just showing the text as is and that Excel is putting it into columns. That is what makes it easy to read and see the header for each row and know what the cell is used for. In NotePad++ you can't see how any of the columns line up.

I just wish Excel would leave it alone. Had a leave it alone mode.
It seems almost anti CSV. Like making you open Excel first and then telling it every time to show all file because it hides the CSVs and that the CSV you just selected is a CSV and that you want to use the comma to delimit and having to tell it every time that it just a plan CSV when saving it.
And this and more:
"These include dropping text qualifiers, leading zeroes, converting anything that looks like a number to a number, and changing date formats."

 

[Joe4]

I just wish Excel would leave it alone. Had a leave it alone mode.

Or I wish Excel would give you the option of controlling the import mapping, like it does for other Text file types!
I hate that it does "automated" conversions, and doesn't let you control it (at least not without jumping through some of these hoops we tossed out there).

I love Excel, but Excel and Windows handling of CSV files could be improved, IMO.
The fact that Windows chooses Excel as the default program to open CSV files is one of my biggest pet peeves (that and "merged cells"!).

 

[Tanquen]

On top of everything else I just found these:

,°C, becomes ,øC,

,"Manual Mode (0 ? Computer, 1 - Operator)", becomes ,"Manual Mode (0 – Computer, 1 - Operator)",

What the flip? So I can not use Excel to edit the simple CSV and must use NotePad++ and its harder to follow the Find and Replace.