Saving as *.csv or *.txt with double quotes

[Bhupinder Rayat]

Hi,

I want to save a spreadsheet that contains text strings with double quote in
csv or txt format, but when I save and open in textpad or notepad, its adds
extra double quotes.

e.g.
cell A1 - "TEST"
textpad - ""TEST""

Also if you have double quotes inside a string its also adds them to start
and end of the string...

cell A1 - This i "a" Test
textpad - "This is ""a"" Test"

I am creating various files that are read by a bespoke application and
requires double quotes in certain parts of a string to interpret the code.

My current code just takes the strings simliar to below and saves as a text
file.

SYMBOL="SYMBOL123"
DESCRIPTION="Historic Data
EXCHANGE=UBSC
INSTRUMENT-TYPE=3
VOLATILITY=0
TRADING-UNITS=100

Is there anyway around this duplication of double quotes?

Thanks,

B/

 

[Joel]

You have 3 choices. I usually use the third one.

1) Edit the file in Notepad or some other editor after you export the file
from excel. CSV files are text and can be changed like any otherr text file.
2) Write a macro that modifies the CSV file to replace the double quotes
with single quotes and to remove the 1st and last double quote in a line. My
macro below will do exactly that.
3) Writte your own macro to creatte the CSV file. Similar to the code below
but reads the spreadsheet and outputs the data putting commas between the
data in each column. I can generate the code pretty quickly if you need it.


Sub removedouble()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "test.csv"
WriteFileName = "outtest.csv"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.Readline

OutputLine = Replace(InputLine, Chr(34) & Chr(34), Chr(34))
If Left(OutputLine, 1) = Chr(34) Then
OutputLine = Mid(OutputLine, 2)
End If
If Right(OutputLine, 1) = Chr(34) Then
OutputLine = Left(OutputLine, Len(OutputLine) - 1)
End If

tswrite.WriteLine OutputLine
Loop


tswrite.Close
tsread.Close


End Sub

 

[Bhupinder Rayat]

Hi Joel,

Thanks for the reply.

I am good with writing the code but it you could let me know the format for
the csv file with comma separation, that would be great.

So eg...

Cell A1 - "String1","String2","String3" - Something like this?

I want an output with the following text...

SYMBOL="SYMBOL123"
DESCRIPTION="Historic Data"
EXCHANGE=UBSC
INSTRUMENT-TYPE=3
VOLATILITY=0
TRADING-UNITS=100

Thanks,

B/

 

[Joel]

The code is simple, just span each row and put comma between each column of
data. The code is just a simple example. You need to determine where the
last row and last column is going to be.

for RowCount = 1 to last row
'add code here to determine lastcol
'each row may have different length
'otherwise there will be extra commas att end of some rows
for col = 1 to lastcol
If Len(OutputLine) > 0 Then
OutputLine = OutputLine & "," & cells(rowcount,Col)
Else
OutputLine = cells(rowcount,col)
End If
next col
'write row with CR and end of line
ts.writeline
next rowcount