Exporting to .txt converts single quotes to double quotes
in [Programming]
|
Prev: File not saving
Next: SendTo
From: Andrew P. on 25 May 2010 05:14 Hi All Im making a module that will create a new spreadsheet, populate it with some fixed and variable values and save it as a text file. Unfortunately excel 2007 is saving the following line: <?xml version="1.0" encoding="ISO-885h9-1" ?> as "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?>" when I open it in wordpad. I have tried coding this line as = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-885h9-1" & Chr(34) & " ?>" and = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?>" and it looks fine in the spreadsheet its in, but always bad when it gets to the .txt file. I also tried separating into separate cells and formatting the cell as text. Any idea how to correct this? Thanks a lot Andrew
From: GS on 26 May 2010 02:55 Andrew P. brought next idea : > Hi All > > Im making a module that will create a new spreadsheet, populate it with some > fixed and variable values and save it as a text file. Unfortunately excel > 2007 is saving the following line: > > <?xml version="1.0" encoding="ISO-885h9-1" ?> > > as > > "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?>" > > when I open it in wordpad. I have tried coding this line as > > = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & > "ISO-885h9-1" & Chr(34) & " ?>" > > and > > = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?>" > > and it looks fine in the spreadsheet its in, but always bad when it gets to > the .txt file. I also tried separating into separate cells and formatting the > cell as text. Any idea how to correct this? > > Thanks a lot > Andrew Not exactly sure what you mean by <snip> Unfortunately excel 2007 is saving the following line: <?xml version="1.0" encoding="ISO-885h9-1" ?> </snip> I pasted this line into a cell and wrote the text file using: WriteTextFileContents ActiveCell.Value, "C:\Test.txt" Here's what I got when viewed in Notepad: <?xml version="1.0" encoding="ISO-885h9-1" ?> ...which hasn't changed from what was entered in the cell. Here's the procedure to write the text file: Sub WriteTextFileContents(Text As String, FileName As String, Optional AppendMode As Boolean = False) ' A reuseable procedure to write or append large amounts of data to a text file Dim iNum As Integer Dim bIsOpen As Boolean On Error GoTo ErrHandler iNum = FreeFile() If AppendMode Then Open FileName For Append As #iNum Else Open FileName For Output As #iNum 'If we got here the file has opened successfully bIsOpen = True 'Print to the file in one single step Print #iNum, Text ErrHandler: 'Close the file If bIsOpen Then Close #iNum If Err Then Err.Raise Err.Number, , Err.Description End Sub 'WriteTextFileContents() -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc
From: Martin Brown on 26 May 2010 03:49 On 25/05/2010 10:14, Andrew P. wrote: > Hi All > > Im making a module that will create a new spreadsheet, populate it with some > fixed and variable values and save it as a text file. Unfortunately excel > 2007 is saving the following line: > > <?xml version="1.0" encoding="ISO-885h9-1" ?> > > as > > "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?>" > > when I open it in wordpad. That *is* the correct encoding of an ASCII text string in a .txt file! The opening quote at the start and end of the string means that internally every use of " must use an escape code. Excel chooses to use "" for escaping in ". Other languages choose similar methods. When Excel reads it back in then it will see the correct string data. Otherwise strings would be ambiguous in their .txt representation. This way you know that true end of string is a " not followed by another. > = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?>" > > and it looks fine in the spreadsheet its in, but always bad when it gets to > the .txt file. I also tried separating into separate cells and formatting the > cell as text. Any idea how to correct this? There is nothing to correct. Excel for once is doing the right thing. Regards, Martin Brown
From: GS on 26 May 2010 05:02 Martin Brown laid this down on his screen : > On 25/05/2010 10:14, Andrew P. wrote: >> Hi All >> >> Im making a module that will create a new spreadsheet, populate it with >> some >> fixed and variable values and save it as a text file. Unfortunately excel >> 2007 is saving the following line: >> >> <?xml version="1.0" encoding="ISO-885h9-1" ?> >> >> as >> >> "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?>" >> >> when I open it in wordpad. > > That *is* the correct encoding of an ASCII text string in a .txt file! > > The opening quote at the start and end of the string means that internally > every use of " must use an escape code. Excel chooses to use "" for escaping > in ". Other languages choose similar methods. When Excel reads it back in > then it will see the correct string data. > > Otherwise strings would be ambiguous in their .txt representation. This way > you know that true end of string is a " not followed by another. > >> = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?>" >> >> and it looks fine in the spreadsheet its in, but always bad when it gets to >> the .txt file. I also tried separating into separate cells and formatting >> the >> cell as text. Any idea how to correct this? > > There is nothing to correct. Excel for once is doing the right thing. > > Regards, > Martin Brown You are correct! But.., this is what happens when you use Save As and specify ".txt"! I didn't catch that right away, and so couldn't figure out why the text was being modified during the process. Obviously, this is not what the OP wants. That said, I think, for the OP's purpose, the context of the string (being XML) is incorrectly being saved to the text file by Excel<IMO>. This renders the text as useless in terms of being working XML, or being imported as useable XML without having to be reformatted or otherwise manipulated/reworked. Using VB[A] file I/O to write the text to file persists the original format. Importing the text to Excel via the File Open dialog requires setting the delimited option (as opposed to fixed length option) to avoid the double quotes persisting in the result. Using VB[A] file I/O to import the text persists the text 'as it was' in the text file. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc
From: Andrew P. on 27 May 2010 16:53 Fixed! http://www.mcgimpsey.com/excel/textfiles.html#csvwithquotes the one that was needed was "Text files with no modification". Thanks McGimpsey and Associates Regards Andrew "Andrew P." wrote: > Hi All > > Im making a module that will create a new spreadsheet, populate it with some > fixed and variable values and save it as a text file. Unfortunately excel > 2007 is saving the following line: > > <?xml version="1.0" encoding="ISO-885h9-1" ?> > > as > > "<?xml version=""1.0"" encoding=""ISO-885h9-1"" ?>" > > when I open it in wordpad. I have tried coding this line as > > = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & > "ISO-885h9-1" & Chr(34) & " ?>" > > and > > = "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?>" > > and it looks fine in the spreadsheet its in, but always bad when it gets to > the .txt file. I also tried separating into separate cells and formatting the > cell as text. Any idea how to correct this? > > Thanks a lot > Andrew
|
Pages: 1 Prev: File not saving Next: SendTo |