Solved

Excel: Add double quotes on exported CSV from field

Posted on 2011-09-13
8
1,909 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

708 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

12 Experts available now in Live!

Get 1:1 Help Now