file formats

  • Thread starter Thread starter Richer
  • Start date Start date
R

Richer

I have a (.txt) file in notepad that has errors in it. I need to correct the
errors in excel and then save it back to the exact (.txt) format. I am using
Excel 2003 and I am having difficulties trying to accomplish this task.
Example: original file
"cellcontent1","cellcontent2","cellcontent3","cellcontent4",

Example: after corrections and saving back to (.txt)
""cellcontent1"",""cellcontent2"",""cellcontent3"",""cellcontent4"", -
[double qoutes]

or
" cellcontent1 ",'" cellcontent2 ", - [space on each
side cell content]

I must be missing a step somewhere - can someone help identify my missing
step?
 
Use filescripting method for reading and writing shown below. The Open #1
method is creating the double quotes in your file.


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
 
Joel,
I lack programming savvy, how and where do I use this script?
--
Richer


Joel said:
Use filescripting method for reading and writing shown below. The Open #1
method is creating the double quotes in your file.


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


Richer said:
I have a (.txt) file in notepad that has errors in it. I need to correct the
errors in excel and then save it back to the exact (.txt) format. I am using
Excel 2003 and I am having difficulties trying to accomplish this task.
Example: original file
"cellcontent1","cellcontent2","cellcontent3","cellcontent4",

Example: after corrections and saving back to (.txt)
""cellcontent1"",""cellcontent2"",""cellcontent3"",""cellcontent4"", -
[double qoutes]

or
" cellcontent1 ",'" cellcontent2 ", - [space on each
side cell content]

I must be missing a step somewhere - can someone help identify my missing
step?
Click to expand...
Click to expand...
 
Richer,

How are you opening the txt file in XL? Are you clicking "Open..." ([Ctrl]
+ O) from within XL, selecting the txt file in the "Open File" dialog box,
then XL starts the "Text Import Wizard" automatically?

If so, on step 2 of 3 in the wizard, there is a "Text qualifier:" dropdown.
Make sure the double quotes is selected.

Now, when you close it, are you saving over the existing file or do you need
to create a new file with a different name.

If saving over original, just close/save and click yes on all of the
messages.

If you need to create a new file (Save as...), then make sure you select the
"CSV (Comma delimited) (*.csv)" type in the "Save as type:" dropdown. When
you do this, XL will add ".csv" to what ever you type into the "File name:"
box...........

FileName will become FileName.csv
FileName.txt will become FileName.txt.csv

.........UNLESS you surround your file name with quotes (").......

"FileName.txt" will become FileName.txt (but it will be in the CSV
format)

If you already have a FileName.csv or a FileName.txt.csv, just rename the
file in Windows (Windows Explorer) changing the .csv to .txt or removing the
..csv respectively

HTH,

Conan
 
Richer,

Just expiermenting........I can't get XL to put quotes around text in the
CSV format. It will only put quotes around numbers that have commas in
them, i.e. 1,234,567.89 will become "1,234,567.89".

Sorry, don't know how to get around this. Maybe someone else will know.

Conan




Conan Kelly said:
Richer,

How are you opening the txt file in XL? Are you clicking "Open..."
([Ctrl] + O) from within XL, selecting the txt file in the "Open File"
dialog box, then XL starts the "Text Import Wizard" automatically?

If so, on step 2 of 3 in the wizard, there is a "Text qualifier:"
dropdown. Make sure the double quotes is selected.

Now, when you close it, are you saving over the existing file or do you
need to create a new file with a different name.

If saving over original, just close/save and click yes on all of the
messages.

If you need to create a new file (Save as...), then make sure you select
the "CSV (Comma delimited) (*.csv)" type in the "Save as type:" dropdown.
When you do this, XL will add ".csv" to what ever you type into the "File
name:" box...........

FileName will become FileName.csv
FileName.txt will become FileName.txt.csv

........UNLESS you surround your file name with quotes (").......

"FileName.txt" will become FileName.txt (but it will be in the CSV
format)

If you already have a FileName.csv or a FileName.txt.csv, just rename the
file in Windows (Windows Explorer) changing the .csv to .txt or removing
the .csv respectively

HTH,

Conan




Richer said:
I have a (.txt) file in notepad that has errors in it. I need to correct
the
errors in excel and then save it back to the exact (.txt) format. I am
using
Excel 2003 and I am having difficulties trying to accomplish this task.
Example: original file
"cellcontent1","cellcontent2","cellcontent3","cellcontent4",

Example: after corrections and saving back to (.txt)
""cellcontent1"",""cellcontent2"",""cellcontent3"",""cellcontent4"", -
[double qoutes]

or
" cellcontent1 ",'" cellcontent2 ", - [space on each
side cell content]

I must be missing a step somewhere - can someone help identify my missing
step?
Click to expand...
Click to expand...
 
Here is slightly modified code. The code deosn't use a worksheet, just use
the VBA envirnoment to run VBA. the code opens an input and output file.
You need to modify the three lines below
Const MyPath = "C:\temp\"
Const ReadFileName = "test.txt"
Const WriteFileName = "outtest.txt"

Copy the code below as follows
1) Worksheet menu Tools - Macro - visual Basic editor
2) VBA menu Insert-Module
3) Copy code below into module window
4) You can run code either from the VBA window or excel window
From excel Tools - Macro -Macros - add_double
from VBA either select any line of code and press F5. Or from menu Run- Run

Note: you may need to change your secutiy level to medium. Macros only run
in security mode low or medium.

From worksheet menu Tools - Macro - Security



Sub add_double()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const ReadFileName = "test.txt"
Const WriteFileName = "outtest.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


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

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

tswrite.writeline OutputLine
Loop


tswrite.Close
tsread.Close


End Sub


Richer said:
Joel,
I lack programming savvy, how and where do I use this script?
--
Richer


Joel said:
Use filescripting method for reading and writing shown below. The Open #1
method is creating the double quotes in your file.


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


Richer said:
I have a (.txt) file in notepad that has errors in it. I need to correct the
errors in excel and then save it back to the exact (.txt) format. I am using
Excel 2003 and I am having difficulties trying to accomplish this task.
Example: original file
"cellcontent1","cellcontent2","cellcontent3","cellcontent4",

Example: after corrections and saving back to (.txt)
""cellcontent1"",""cellcontent2"",""cellcontent3"",""cellcontent4"", -
[double qoutes]

or
" cellcontent1 ",'" cellcontent2 ", - [space on each
side cell content]

I must be missing a step somewhere - can someone help identify my missing
step?
Click to expand...
Click to expand...
Click to expand...
 
Hey Conan, thanks for your thoughts. I will play with this a bit more.
--
Richer


Conan Kelly said:
Richer,

Just expiermenting........I can't get XL to put quotes around text in the
CSV format. It will only put quotes around numbers that have commas in
them, i.e. 1,234,567.89 will become "1,234,567.89".

Sorry, don't know how to get around this. Maybe someone else will know.

Conan




Conan Kelly said:
Richer,

How are you opening the txt file in XL? Are you clicking "Open..."
([Ctrl] + O) from within XL, selecting the txt file in the "Open File"
dialog box, then XL starts the "Text Import Wizard" automatically?

If so, on step 2 of 3 in the wizard, there is a "Text qualifier:"
dropdown. Make sure the double quotes is selected.

Now, when you close it, are you saving over the existing file or do you
need to create a new file with a different name.

If saving over original, just close/save and click yes on all of the
messages.

If you need to create a new file (Save as...), then make sure you select
the "CSV (Comma delimited) (*.csv)" type in the "Save as type:" dropdown.
When you do this, XL will add ".csv" to what ever you type into the "File
name:" box...........

FileName will become FileName.csv
FileName.txt will become FileName.txt.csv

........UNLESS you surround your file name with quotes (").......

"FileName.txt" will become FileName.txt (but it will be in the CSV
format)

If you already have a FileName.csv or a FileName.txt.csv, just rename the
file in Windows (Windows Explorer) changing the .csv to .txt or removing
the .csv respectively

HTH,

Conan




Richer said:
I have a (.txt) file in notepad that has errors in it. I need to correct
the
errors in excel and then save it back to the exact (.txt) format. I am
using
Excel 2003 and I am having difficulties trying to accomplish this task.
Example: original file
"cellcontent1","cellcontent2","cellcontent3","cellcontent4",

Example: after corrections and saving back to (.txt)
""cellcontent1"",""cellcontent2"",""cellcontent3"",""cellcontent4"", -
[double qoutes]

or
" cellcontent1 ",'" cellcontent2 ", - [space on each
side cell content]

I must be missing a step somewhere - can someone help identify my missing
step?
Click to expand...
Click to expand...
Click to expand...
 
Joel, thanks for the additional steps. I will try to get this to work.
--
Richer


Joel said:
Here is slightly modified code. The code deosn't use a worksheet, just use
the VBA envirnoment to run VBA. the code opens an input and output file.
You need to modify the three lines below
Const MyPath = "C:\temp\"
Const ReadFileName = "test.txt"
Const WriteFileName = "outtest.txt"

Copy the code below as follows
1) Worksheet menu Tools - Macro - visual Basic editor
2) VBA menu Insert-Module
3) Copy code below into module window
4) You can run code either from the VBA window or excel window
From excel Tools - Macro -Macros - add_double
from VBA either select any line of code and press F5. Or from menu Run- Run

Note: you may need to change your secutiy level to medium. Macros only run
in security mode low or medium.

From worksheet menu Tools - Macro - Security



Sub add_double()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const ReadFileName = "test.txt"
Const WriteFileName = "outtest.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


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

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

tswrite.writeline OutputLine
Loop


tswrite.Close
tsread.Close


End Sub


Richer said:
Joel,
I lack programming savvy, how and where do I use this script?
--
Richer


Joel said:
Use filescripting method for reading and writing shown below. The Open #1
method is creating the double quotes in your file.


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


:

I have a (.txt) file in notepad that has errors in it. I need to correct the
errors in excel and then save it back to the exact (.txt) format. I am using
Excel 2003 and I am having difficulties trying to accomplish this task.
Example: original file
"cellcontent1","cellcontent2","cellcontent3","cellcontent4",

Example: after corrections and saving back to (.txt)
""cellcontent1"",""cellcontent2"",""cellcontent3"",""cellcontent4"", -
[double qoutes]

or
" cellcontent1 ",'" cellcontent2 ", - [space on each
side cell content]

I must be missing a step somewhere - can someone help identify my missing
step?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top