Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving as *.csv or *.txt with double quotes
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving as *.csv or *.txt with double quotes
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" wrote: 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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving as *.csv or *.txt with double quotes
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" wrote: 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" wrote: 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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving as *.csv or *.txt with double quotes
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 "Bhupinder Rayat" wrote: 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" wrote: 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" wrote: 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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double Quotes | New Users to Excel | |||
Double Quotes | Excel Discussion (Misc queries) | |||
double quotes when saving in CSV | Excel Programming | |||
Double Quotes | Excel Programming | |||
Double Quotes | Excel Programming |