Save as CSV with quotes only in non blank cells

[Little_Anj]

I am trying to run a macro to save a sheet in a book as a csv file with quotation marks only around non blank cells.

Not every row or column contains data that needs "marks".

For example, A1 is blank, B1 is not, C1 is not.
Then A2 is not, B2 is not and C2 is blank

SO the formatting I need is
,"B1","C1"
"a2","B2",

However I am getting
"","B1","C1"
"A2","B2",""

Basically I am trying to have an excel workbook that formats and saves as a csv (to import into another program) and forgoes the need to save in open office...

I can post the macro if needed, but I did not write it.

Thanks

 

[cgcamal]

Hi Little_Anj

If it is an option for you., it can be done opening the csv file and replacing the "" by nothing or without opening
using DOS maybe. With this you'll get the format you need

Hope this helps.

 

[Little_Anj]

Hi Cesar,

I was hoping to be able to do it within one spreadsheet.

I am setting it up for someone else to use (not very computer literate) and I am trying to set the whole thing up with just one click (button) macro's that format, convert & save etc.

Is it possible to only add quotation marks to cells with data? Or is there a manual way within excel to do it e.g. through using other sheets with if statements to add it or something similar?

If not, I can just go with open office

I am not sure if this is the original author of the code (as it has been used on a few web pages), but this is what I am currently using.

http://www.markinns.com/articles/full/export_excel_csvs_with_double_quotes

Thanks

 

[Ruddles]

If it is an option for you., it can be done opening the csv file and replacing the "" by nothing or without opening
using DOS maybe.

Be careful if you decide to do this as this will remove any double quotes embedded in a string, as these are doubled up when written to a CSV file.

For example, if you write this to a CSV file:-

I said "Hello" to Mary.

you will get:-

"I said ""Hello"" to Mary."

If you merely replaced "" with nothing, that would change the actual data - it would be reimported as:-

I said Hello to Mary.

 

[Ruddles]

this is what I am currently using.
http://www.markinns.com/articles/full/export_excel_csvs_with_double_quotes

Replace this:-

CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep

with this:-

if isempty(currcell) then
  CurrTextStr = CurrTextStr & ListSep
else
  CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
endif

 

[cgcamal]

You're right Ruddles, the idea actually was replace ,"" or "", with only ,.

But the adaptations always depends the structure data may have.

With your adaptation to the code should work, but I've trying with that and others
things for some reason, without success so far. I hope it works Little_Anj.

Regards

 

[Ruddles]

the idea actually was replace ,"" or "", with only ,

That still makes me nervous in case there's genuinely "", in the actual data.

This in the worksheet:-

I said "Hello", but he ignored me.

would become this in the CSV:-

"I said ""Hello[B][COLOR=red]"",[/COLOR][/B] but he ignored me."

then it gets edited to:-

"I said ""Hello but he ignored me."

and it's reimported as:-

I said "Hello but he ignored me.

With your adaptation to the code should work, but I've trying with that and others things for some reason, without success so far.

Odd... I just tried it and it works fine. In what way does it not work for you?

 

[cgcamal]

Odd... I just tried it and it works fine. In what way does it not work for you?

I have this in a CSV file (Users.csv):

Excel Workbook
	A	B	C
1	User	Device	User
2	Carl	Desktop	Admin
3	Mary	Laptop
4	Jane		Jane123
Users_Table

When I run the code from another workbook, the macro runs without errors but when has finished,
I open the Users.csv and the content has been deleted. From what I see, it looks this happens
because of this line:

Open FName For Output As #1

Regards

 

[Ruddles]

So... is Users.csv your input file? And you specify the same filename as your output file?

Try it with your source data and code in an Excel workbook and export it to a new CSV file.

 

[cgcamal]

That's right Ruddles, that´s was where I was wrong, I had not understood how is worked.

Now I get the correct result. Thanks!!!.

Best regards