remove quotes inserted around a " for exported CSV?

[wdeleo]

I need to export from Excel a CSV file and one of the fields needs to be exactly:

"w.c.

The software that is importing this file is not handling the double quotes that are automatically inserted to bracket the ". Since I am writing the files with a macro already, I'd like to add some code that will remove the extra double quotes from the saved CSV file.

Or in general, can anyone offer advice as to how I can produce a CSV file with:

"w.c.

instead of

"""w.c."

as it results right now?

TIA
Billy

 

[GrayMPA]

Is this the only element in your macro? I do something similar, but it is part of "fixing" the downloaded file, line by line. Inside a loop I use a CASE function to determine what is in a cell and what actions need to occur. If all you have is this one item, I would recommend copying a "find and replace with" macro.

 

[wdeleo]

My macro has several aspects. But the basic function is to save a series of worksheets as CSV to be loaded into a VisualFoxPro based database (GIS/Key). The database is not handling the extra quotes. Only one column of one sheet has this quote issue, and every row of that column has the same value, representing units of inches water column "w.c. .

I figured the easiest way would be to modify the recently saved CSV file before exiting the macro, but I have no idea how to code that.

Thanks

 

[KenCriss]

I would take a look at Aaron Blood's page here.
http://www.xl-logic.com/pages/vba.html

Look at the code in csv.zip.

You may be able to use parts of this to accomplish what you want.

 

[wdeleo]

Thank you ... I'll try looking for help there.

Take care.

 

[Damon Ostrander]

Hi wdeleo,

There is another--and rather easy way. You can write a CSV file using VBA I/O, and this give you virtually total control over the formatting rules. Here is a macro that does this. It will write out the active worksheet as a CSV file, but will not double-quote quotation marks. It will prompt you for the CSV file name you want to create.


Sub WriteCSV()
'Uses VBA I/O to write activesheet as a csv file. This gives
'precise control over formatting. This particular example write
'out quotes in strings in non-csv-standard way--without
'double-quoting them.
Dim Rec As String
Dim FileNo As Integer
Dim FileName As Variant
Dim iRow As Long
Dim iCol As Integer
Dim MaxRow As Long
Dim MaxCol As Integer

FileName = ActiveWorkbook.Name
If FileName Like "*.???" Then
FileName = Left(FileName, Len(FileName) - 4) & ".csv"
End If
FileName = Application.GetSaveAsFilename(FileName, "CSV/text files (*.csv;*.txt),*.csv;*.txt", _
1, "Save As CSV file")

If FileName = False Then Exit Sub

FileNo = FreeFile
Open FileName For Binary Access Write As FileNo

With ActiveSheet.UsedRange
For iRow = 1 To .Row + .Rows.Count - 1
For iCol = 1 To Cells(iRow, 256).End(xlToLeft).Column
If iCol = 1 Then
Rec = Cells(iRow, iCol).Text
Else
Rec = Rec & "," & Cells(iRow, iCol).Text
End If
Next iCol
Put FileNo, , Rec & vbCrLf
Next iRow
End With

Close FileNo

End Sub


Keep Excelling.

Damon

 

[wdeleo]

I'll try this and it sure seems like exactly what I need.

But if you are still there Damon, let me know if I understand the logic ...

The CSV file gets created and saved before the data is written to it. The data is written to the file without having to do a CSV file save at the end, which is the problem. Is that right?

Anyway ... THANK YOU!!!

 

[Damon Ostrander]

Hi again wdeleo,

Yes, I believe you have the essence of it. Yes, the file is created by the Open statement, but is of zero length until the Put statement writes data to it. Basically, VBA is capable of reading and writing files without Excel opening or saving them--or knowing anything about it. And these files can be in formats that Excel knows nothing about and is incapable of reading or writing as well.

All programming languages (C, Java, VB6, etc.) provide mechanisms for doing file I/O independently of any existing application, and VBA is no exception even though it is more in the category of a scripting language.

Damon

 

[mhermez1977]

Hi
what if I have single line that has double and quad quotes and at the end I should only keep double quotes from the quad quotes. when I export to text file I am getting new quotes one at the beginning and one at the end also added extra quotes to an existing quotes.

I am using this command to export to textv

Sub ExportSheetsToText()
Dim xWs As Worksheet
Dim xTextFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xTextFile = CurDir & "" & xWs.Name & ".txt"
Application.ActiveWorkbook.SaveAs filename:=xTextFile, FileFormat:=xlText
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next

End Sub

and getting below results
"BKUP-PLD:::::PLD_BACKUP_FILE_REF=""/log/conf/ENB/xxxxx/eNBxxxxx_backuppld.tar.gz""; 1"

I should get rid of edge quotes and get rid of extra quotes added to (/log/conf/ENB/xxxxx/eNBxxxxx_backuppld.tar.gz) and leave one set of quotes only for that sentence so it will be (BKUP-PLD:::::PLD_BACKUP_FILE_REF="/log/conf/ENB/xxxxx/eNBxxxxx_backuppld.tar.gz"; 1)

Thank you