Creating CSV file using VBA, problem with "" and ,

[MCubitt]

I have a line of text on an input file as:

db_files 				= 1024
control_files 			= ("/oracledata/IFSL/control01.ctl","/oracleindex/IFSL/control02.ctl","/oracledata/IFSL/control03.ctl") 
open_cursors 			= 100

I want to create, as a CSV file:
1024,("/oracledata/IFSL/control01.ctl","/oracleindex/IFSL/control02.ctl","/oracledata/IFSL/control03.ctl"), 100

As three columns when opened in Excel.

Now, the , in the middle column is going to cause problems, ordinarily catered for with enclosing it in "". But this line has that as part of it's actual value!

Any ideas? Please.






Applications Support
UK

 

[HughLerwill]

MCubitt,

Have you considered using a Text file (extension .txt) rather than a .csv, and replacing all (delimiter) commas with tabs.

If you want to include quotes within fields, replace double quotes with single quotes.

regards Hugh.

 

[MCubitt]

Well I had not, no. I prefer CSV because it can be opened simply by double clicking and Excel brings it up nicely (ish!)

I guess I could replace the double quotes with single, though the output data no longer respresents the input data, but it's a small sacrifice.


Thanks for the idea.

regards


Applications Support
UK

 

[HughLerwill]

Yes the double click from Explorer will (typically) bring the file up in NotePad but Right click Open With ... Excel is not so far away (after the first time you have done it).

regards Hugh

 

[TonyJollans]

Hi MCubitt,

You need to use a lot of quotes!

The whole string needs to be in quotes so that it is treated as a complete string, comma and all, and the individual quoted components need to be delimited by doubled-up quotes, so you get your CSV looking like this ..

[blue][tt]1024,"(""/oracledata/IFSL/control01.ctl"",""/oracleindex/IFSL/control02.ctl"",""/oracledata/IFSL/control03.ctl"")", 100[/tt][/blue]

I don't entirely follow what you're doing. Are you reading an input file and parsing the text into VBA variables? If so, try using the replace function to replace " with "" before wrapping it in quotes for the output. If something else and you can't get it right, please post back with a bit more detail.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[MCubitt]

Hi Tony - thanks for the thoughts.

Actually, I am not entirely sure I need it any more and I'll explain why.

I am reading an init.ora file (Oracle config) which has this as part of it:

db_files 				= 1024
open_cursors 			= 100
control_files 			= ("/oracledata/IFSL/control01.ctl", 
                 			   "/oracleindex/IFSL/control02.ctl",
			    	         "/oracledata/IFSL/control03.ctl")
max_enabled_roles 		= 30
db_file_multiblock_read_count = 96

I am putting each config label into a column and then the value into a row of an excel file (for multiple init.ora files), to give:

,db_files, open_cursors, control_files, max_enabled_roles, db_file_multiblock_read_count
init1.ora,1024,100,("/oracledata/IFSL/control01.ctl","/oracleindex/IFSL/control02.ctl","/oracledata/IFSL/control03.ctl"),30,96
init2.ora,512,100,("/oracledata/IFSD/control01.ctl","/oracleindex/IFSD/control02.ctl","/oracledata/IFSD/control03.ctl"),20,128

As you can see, the value for control_files is the problem.

1st it goes to three lines which makes life awkward enough for a "simple script".

So I cheated, made the line a single one:

control_files 			= ("/oracledata/IFSD/control01.ctl","/oracleindex/IFSD/control02.ctl","/oracledata/IFSD/control03.ctl")

but that caused me the problem I reported.

Now I have another problem, it doesn't like such long values so I had to revert back.

The joys of casual scripting!






Applications Support
UK