Solved

Excel: Add double quotes on exported CSV from field

Posted on 2011-09-13
8
1,924 Views
Last Modified: 2012-06-27
I tried to export CSV with this command:
ActiveWorkbook.SaveAs Filename:=fileresult, FileFormat:=xlCSV, CreateBackup:=False

But the exported CSV doesn't have double quote for each cell except the "Wrap Text" ones (item.csv as attached)
.

Then i tried to add this command:
 Set SrcRg = ActiveSheet.UsedRange
    For Each CurrRow In SrcRg.Rows
        For Each CurrCell In CurrRow.Cells
            CurrCell.Value = Chr(34) & CurrCell.Value & Chr(34)
        Next
    Next

it visually display the double quotes, but when i open the raw source of CSV, it consist 3 double quotes, for example:
"""product_sku""","""product_price""","""category_path""","""product_type""","""parameter""","""product_length"""

(Item2.csv as attached)

So... how to solve this problem ?



Item.csv
Item2.csv
0
Comment
Question by:veematics
8 Comments
 
LVL 17

Accepted Solution

by:
Shanmuga Sundaram earned 500 total points
ID: 36534343
I dont know how to fore see your problem. the quickest option, since you know vba is to write to a text file.so try as below

Sub main()

Open "C:\results.csv" For Output As #1
Set SrcRg = ActiveSheet.UsedRange
    For Each CurrRow In SrcRg.Rows
        For Each CurrCell In CurrRow.Cells
            Print #1, """" & CurrCell.Value & """,";
        Next
        Print #1, ""
    Next
Close #1

End Sub
0
 
LVL 4

Expert Comment

by:curtiswong
ID: 36534353
Please see the following website if it can give you a hint.

http://www.mrexcel.com/forum/showthread.php?t=320531
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 36534361
please see the excel with macro and results attached
sample.xlsm
results.csv
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 45

Expert Comment

by:aikimark
ID: 36536661
The quotes are being added to the "wrap text" fields because they contain one or more comma characters.

Since the Chr(34) character is, itself,  used as a delimiter in CSV files, they must be handled differently.  The three consecutive quote character sequences you see are the Chr(34) character that is being delimited by quote characters.

I'm not sure I fully understand what you need.  Have you considered tab-separated (delimited) values, rather than comma-separated?
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 36962679
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 

Author Comment

by:veematics
ID: 36962680
Hello i was out of town for almost a month...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now