How to remove quotations " from an auto generated file?

Hi all,

Hope all are doing well.

A bit unusual request this time. Attached file contains a macro which when run based on certain conditions generates 2 CSV files namely import_alert_status_change_template and import_investigation_status_change_template.

Out of the 2 CSV files, 2nd one has no issues and even the 1st one doesn't show any issues unless its investigated thoroughly. To elaborate, CSV file import_alert_status_change_template has " at the beginning and end of most cells. The same can be seen when the file is opened in Notepad++ application as shown in snip below.

Is there any way that these " can be removed either by modifying code in MSTool file or by creating another macro such that when its run, it shall ask to select the file and thereafter shall remove all the " in it thereby saving it? Currently i have to open the file in Notepad++ each time and use replace option.

Can someone please help ?

Originally Posted by TMS

Neither of the csv files shows double quotes for me.

I see them only in the alert status file - inconsistantly, same as OP's image.
They only appear in Notepad++. Normal Notepad (W10) and Excel don't show them.

After the CSV has been generated, call the ReplaceQuotes routine on it to remove the quotes.

@TMS didn't spot that or post #5 in which case don't replace the quotes.

Maybe you need to replace any commas in the data with semi-colons during the data-build instead.

I haven't worked out why notepad++ showed the quotes but standard notepad didn't.

Originally Posted by rizwanulhasan

... these " can be removed ... by modifying code in MSTool file ...

Try to modify the 'CreateCSV' procedure in the second, inner 'For' loop for 'ii' variable:

Please Login or Register  to view this content.

Originally Posted by rizwanulhasan

@ByteMarks:
I tried the code and it does remove the quotations . But does the file need to be always in a specified folder with a fixed name because the file name will change each day.

You can just pass the path to the routine wherever it is, but as it turns out you really don't want to be doing this. Removing the quotes is why some text appears in second column instead of being a combination with text in first column.

If the quotes are an issue, you want to replace/remove commas in the data, eg after Pack active in ATTP.

@TMS @ByteMarks:

I replaced comma with hyphen in target file but that dint change anything in CSV file except that it no more shows comma. But i still have quotations in notepad++.

If there are now no commas in the data, you should be able to remove the quotes without it messing up the column split.

@ByteMarks:

One thing I am getting to know now is that maybe its not because of comma because I tried inserting hyphen and even removed comma. I entered value as Pack active in ATTP but decommissioned elsewhere in target file yet the generated CSV file has quotations. Its just that now its in single column instead of splitting into two.

@TMS:

I am not inserting any quotations but the code itself is generating a CSV file with quotations which can only be seen in notepad++

Hi both,

Sorry but you were right. It was because of comma. Actually column AI had data validation. Although I removed comma, replaced with hyphen etc. and it appeared rightly in cell but looks like when macro was fetching data, it was considering data validation and pulling comma. Hence the CSV files showed quotations.

Now I have chnaged all data validations in all files and looks like issue is solved. Also, the previous code was still showing quotations but the one in post # 14 works perfectly. I will test it again tomorrow and hope it doesn't give any problem.

Meanwhile, a huge thanks to all of you for finding the root cause and providing the solution. Greatly appreciate your time and efforts

Happy to have contributed.

Originally Posted by rizwanulhasan

... However, when i see in Excel, some text appears in second column instead of being a combination with text in first column ...

Originally Posted by TMS

... it's a csv file with commas in the data. If you open in Excel it will split on the commas ...

Data in the presented csv file is separated by a semicolon, not a comma.
The problem is, on the one hand, the inappropriate places where quotation marks appear, and on the other hand, a different separator in the system (comma) than in the csv file (semicolon) - probably ... (?)

There is:
"DE-420a300e-282b-4edc-b745-fb2dc408a7a9;Under Investigation;RC-001;Pack active in ATTP, decommissioned elsewhere"

??? It should be ???:
"DE-420a300e-282b-4edc-b745-fb2dc408a7a9";"Under Investigation";"RC-001";"Pack active in ATTP, decommissioned elsewhere"

??? or ???
"DE-420a300e-282b-4edc-b745-fb2dc408a7a9","Under Investigation","RC-001","Pack active in ATTP, decommissioned elsewhere"

You need to check in your regional settings what separator you have.

Ok TMS, but OP probably didn't do it.

... and ...

Originally Posted by rizwanulhasan

... Note: Files after generation are to be uploaded in a portal which accepts only CSV format. But I am unable to upload in portal since it gives error saying file contains ".

What kind of separators, delimiters are accepted by "this portal" ?
... or maybe ...
What should the correct structure of the csv file for "this portal" look like ?

As identified and rightly suggested about comma, I modified all the target files and removed the comma. Now everything is working perfectly with the code provided in post # 14.

Huge thanks to all. Wish you a great weekend

... but it's not "my code", it's just my "modification" of someone else's code ...

@mjr veverka:

Greatly appreciate your modesty. But thanks a ton for the modification

@jindon:

The original maker of the code and one of the greatest geniuses i have ever come across. Hats off to you