Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
file formats
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","cell content4", 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? -- Richer |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
file formats
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" wrote: 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","cell content4", 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? -- Richer |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
file formats
Joel,
I lack programming savvy, how and where do I use this script? -- Richer "Joel" wrote: 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" wrote: 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","cell content4", 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? -- Richer |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
file formats
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" wrote in message ... 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","cell content4", 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? -- Richer |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
file formats
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" wrote in message ... 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" wrote in message ... 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","cell content4", 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? -- Richer |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
file formats
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" wrote: Joel, I lack programming savvy, how and where do I use this script? -- Richer "Joel" wrote: 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" wrote: 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","cell content4", 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? -- Richer |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
file formats
Hey Conan, thanks for your thoughts. I will play with this a bit more.
-- Richer "Conan Kelly" wrote: 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" wrote in message ... 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" wrote in message ... 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","cell content4", 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? -- Richer |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
file formats
Joel, thanks for the additional steps. I will try to get this to work.
-- Richer "Joel" wrote: 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" wrote: Joel, I lack programming savvy, how and where do I use this script? -- Richer "Joel" wrote: 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" wrote: 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","cell content4", 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? -- Richer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel file formats | Excel Discussion (Misc queries) | |||
Converting file formats into .xls files | Excel Discussion (Misc queries) | |||
New custom % formats are only in XL file they are set up in. WHY? | Excel Worksheet Functions | |||
"Too many different cell formats" is preventing file from opening | Excel Discussion (Misc queries) | |||
can not open excel file, too many different cell formats | Excel Discussion (Misc queries) |