Create a csv with quotes

[nevergiveup]

I need to create a csv text file with the fields acutally delimited by commas and with quotes around each field. I am starting from an excel file and saving the file as a csv. But this approach leaves me with out the quotes.

So if I start with this in an excel csv
<TABLE style="WIDTH: 303pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=404><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2958" width=83><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2304" width=65><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=64>M</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=83>Blended_Mix</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>P</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>P</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>S</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">FKINX</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">S</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>28</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>32</TD></TR></TBODY></TABLE>

And even though I saved this as a csv, it seems like it is really a tab delimited file. So that if I simply copies and pasted the above into a text file, the fields are separated with a tab.

What I need, though I need to end with this in a text file.

"M","Blended_Mix"," ","P","P"
"S","FKINX","S","30","28","32",""

I am using Excel 2010.

 

[Domenic]

Assuming that your data starts at A1, try...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CreateCSV()

    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastColumn [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] j [color=darkblue]As[/color] [color=darkblue]Long[/color]

    [color=darkblue]With[/color] ActiveSheet.UsedRange
        LastRow = .Rows.Count + .Rows(1).Row - 1
        LastColumn = .Columns.Count + .Columns(1).Column - 1
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=green]'Change the path and file name accordingly[/color]
    [color=darkblue]Open[/color] "C:\Path\Filename.csv" [color=darkblue]For[/color] [color=darkblue]Output[/color] [color=darkblue]As[/color] #1
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] LastRow
            [color=darkblue]For[/color] j = 1 [color=darkblue]To[/color] LastColumn
                [color=darkblue]If[/color] j <> LastColumn [color=darkblue]Then[/color]
                    [color=darkblue]Write[/color] #1, [color=darkblue]CStr[/color](Cells(i, j).Value);
                [color=darkblue]Else[/color]
                    [color=darkblue]Write[/color] #1, [color=darkblue]CStr[/color](Cells(i, j).Value)
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Next[/color] j
        [color=darkblue]Next[/color] i
    [color=darkblue]Close[/color] #1
    
    MsgBox "Completed...", vbInformation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[nevergiveup]

First, many thanks for your reply.

Running this does turn the .xls file into a .csv. And while my import function dictates either a .txt or .csv extension, it also states that the fields MUST be separated by commas.

The only example is this:

"M","Custom Aggressive Growth","sample description","P","P","20080312","769000007","20080315","769000007"
"S","MSFT","S",100.000,100.000,100.000,""
"A","179000123","FID","B"

So, is there a way to go from an .xls file to a .txt file, to match the above example.

Many many thanks.

 

[nevergiveup]

Please ignore prior response.

First, many thanks for your reply. It certainly appears that this is working.

However, please notice this:

M Blended_Mix P P 20110118 7690039677 20110118 7690039677
S FKINX S 30 28 32
S FTABX S 25 21 26
S PASHX S 15 12 17
S PONDX S 30 27 33

The first row has 4 extra columns, therefor, the macro also seems to insert double quotes for each row equal to the number of fields in the first row.
I need to limit the number of columns in any row starting with an "S" to 7.

So instead of getting this:

"M","Blended_Mix"," ","P","P","20110118","7690039677","20110118","7690039677"
"S","FKINX","S","30","28","32","","",""
"S","FTABX","S","25","21","26","","",""
"S","PASHX","S","15","12","17","","",""

I would get this:

"M","Blended_Mix"," ","P","P","20110118","7690039677","20110118","7690039677"
"S","FKINX","S","30","28","32",""
"S","FTABX","S","25","21","26",""
"S","PASHX","S","15","12","17",""
"S","PONDX","S","30","27","33",""


Many many thanks.

 

[Ruddles]

Out of curiosity, why do you need the quotes?

The CSV format only requires quotes around a string when the string contains a comma.

 

[Domenic]

A couple of things I don't quite understand...

1) Why one would want quotes around numerical values.

2) Why one would want to limit the number of columns for certain rows.

Usually, one would want the CSV file in the following format...

"M","Blended_Mix","P","P",20110118,7690039677,20110118,7690039677
"S","FKINX","S",30,28,32,,
"S","FTABX","S",25,21,26,,
"S","PASHX","S",15,12,17,,
"S","PONDX","S",30,27,33,,

Can you please confirm which format you require?

 

[nevergiveup]

Good questions about the quotes. My answer is that I have no idea. You have to ask the unknown programmer at a unmentioned but very large brokerage firm who decided that this was the way their import specs were. I agree that it makes no sense.

In the meantime, I did find a solution that works extremely well. A bit more than I needed but oh well. You can find it here:

http://www.smokeylake.com/excel/text_write_program.htm

It's a whopper but it does work very efficiently.
Thanks for everyone's help.