Save to a CSV with Double Quotes EXCEPT the First Column with NO Quotes

**justforthis1** · 02-09-2012, 12:20 PM

I am a little lost in making this work.

I found a few examples around the web of exporting to CSV with Double Quotes. For example:

http://www.markinns.com/articles/ful..._double_quotes

http://windowssecrets.com/forums/sho...-around-values

But I can't come up with a way to export to CSV with the first column not receiving the double quotes.

Any thoughts?

**arlu1201** · 02-09-2012, 01:02 PM

Provide us some sample data.

**justforthis1** · 02-09-2012, 05:31 PM

Hopefully these work...

The xls is an example of the data I would be using.

The txt is an example of the output I would be looking for.

Thanks.

**arlu1201** · 02-10-2012, 04:32 AM

I checked the output file. Why is there an extra set of double quotes at the end of each line?

**Kyle123** · 02-10-2012, 05:09 AM

Maybe:

Sub test()
Dim arr, col1
Dim stri$
Dim x As Integer

With Application
    arr = Sheet1.Cells(1, 1).CurrentRegion.Value
    col1 = .Transpose(.Index(arr, 0, 1))
    
    Open "D:\My Documents\Testcsv.csv" For Output As #1
        For x = 1 To UBound(arr)
            stri = Replace(Join(.Index(arr, x, 0), """,""") & """", ",""""", "")
            Print #1, col1(x) & Right(stri, Len(stri) - Len(col1(x)) - 1)
        Next x
    Close
End With
End Sub

You can add the additional quotes at the end if you like

**justforthis1** · 02-10-2012, 08:18 PM

Originally Posted by arlu1201

I checked the output file. Why is there an extra set of double quotes at the end of each line?

The extra set is because that field is 99.99% of the time empty. I would like to include it if possible. Not sure if it is while also removing the blanks in the top and bottom rows?

Originally Posted by Kyle123

Maybe:

Sub test()
Dim arr, col1
Dim stri$
Dim x As Integer

With Application
    arr = Sheet1.Cells(1, 1).CurrentRegion.Value
    col1 = .Transpose(.Index(arr, 0, 1))
    
    Open "D:\My Documents\Testcsv.csv" For Output As #1
        For x = 1 To UBound(arr)
            stri = Replace(Join(.Index(arr, x, 0), """,""") & """", ",""""", "")
            Print #1, col1(x) & Right(stri, Len(stri) - Len(col1(x)) - 1)
        Next x
    Close
End With
End Sub

You can add the additional quotes at the end if you like

That seems to work. If you don't mind talking me through some of it? I don't have a lot of experience with Arrays. (though I have been reading on them quite a bit since your solution)

arr is an array composed of all the active data right?
col1 is a "transposed" version of the first column into a 2nd array? could you explain what the .Index(...) portion is?

x is really just a counter that caps at the UBound of the arr array?

stri is where i think i am having the hardest time following. Replace is finding the delimiter in the array and replacing them with """,""" etc? If you could break that whole line down for me that would be awesome...

then we take the Col1 array which is just the first line then add back in the arr array subtracting out the length of the Col1 array correct?

If i wanted to keep the extra double quotes at the end - would that end up in the stri line of code?
If any of the cells are left blank it seems to shift over without leaving blank delimiters - could you explain why that is from a coding perspective? and can it be changed?

If you don't have time to explain it to me i understand, and thanks VERY much for your help.

If you do have time to explain then great

I like to understand what I am doing and apply it to things i do in the future ... rather than just getting "free coding support" kind of thing... :D

Thanks again for both of your time.