Solved

Excel: Add double quotes on exported CSV from field

Posted on 2011-09-13
8
1,941 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

803 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