CODEWITHSUNDEEP
excel
user1466918
user1466918

Reputation:

Export to CSV, with wrap characters and skip headers

Is there a way to export Excel to a csv file that wraps EVERY cell in " and skips the headers? Save-as .csv is not the answer I am looking for.

I am looking for a way to do this from within excel if it is possible. Not a coding solution. Thanks

Upvotes: 0

Views: 2218

Answers (2)

Rob Gale
Rob Gale

Reputation: 325

Assuming you have your data in Sheet1, the best non-VBA solution I can think of is to add a second sheet and enter a formula in A1 like the following:

=""""&Sheet1!A1&""","""&Sheet1!B1&""","""&Sheet1!C1&""""

Then copy that down for as many rows as you have in Sheet1. You need to extend this formula for as many columns as you have as well.

This will give you a single column looking like the CSV file format you want. You would then save that page as a text file.

Upvotes: 3

nutsch
nutsch

Reputation: 5962

Assuming your data starts in the activesheet at cell A1 with a first header row, the attached code should write it to a csv file to your specs. 

Sub WriteToCSV()

Const forReading = 1, forAppending = 3, fsoForWriting = 2
Dim fs, objTextStream, sText As String
Dim lLastRow As Long, lRowLoop As Long, lLastCol As Long, lColLoop As Long

Set fs = CreateObject("Scripting.FileSystemObject")
Set objTextStream = fs.opentextfile("c:\temp\myResult.csv", fsoForWriting, True)

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

For lRowLoop = 2 To lLastRow
    sText = ""
    For lColLoop = 1 To lLastCol
        sText = sText & Chr(34) & Cells(lRowLoop, lColLoop) & Chr(34) & ","
    Next lColLoop

    objTextStream.writeline (Left(sText, Len(sText) - 1))
Next

objTextStream.Close
Set objTextStream = Nothing
Set fs = Nothing

End Sub

Upvotes: 0

Related Questions