Exporting to .txt converts single quotes to double quotes

[Andrew P.]

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

 

[GS]

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()

 

[Martin Brown]

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

 

[GS]

Martin Brown laid this down on his screen :

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.


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.

 

[Andrew P.]

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