End of Line
in [VbScript]
|
From: Odd on 8 Sep 2009 11:20 Hi everyone, I have a .csv file where certain fields have carriage returns/new line feeds. Because of such, I cannot properly import my file. Hence, I wrote the following script and while it removes the carriage returns/ new line feeds through the file, it ALSO removes the END OF LINE carriage return/line feed. So now, I have one file with one record. Basically,I need to know how to remove the carriage returns/line feeds in the file EXCEPT for the ones that is END OF LINE. Can somebody help me please? Dim strSearchString, objFSO, objFile Const ForReading = 1 Const ForWriting = 2 Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading) strSearchString = objFile.ReadAll objFile.Close objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) & ".csv" Set objFile = objFSO.OpenTextFile("d:\Test\Project_" & DatePart ("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) & ".csv", ForWriting) strSearchString = Replace(strSearchString, VbCrLf, "") strSearchString = Replace(strSearchString, Vbtab, "") objFile.Write strSearchString objFile.Close
From: Pegasus [MVP] on 8 Sep 2009 11:41 "Odd" <audrey.nsh(a)gmail.com> wrote in message news:8a2b8b75-480a-4315-88d4-849b5ceb3c96(a)r39g2000yqm.googlegroups.com... > Hi everyone, > > I have a .csv file where certain fields have carriage returns/new line > feeds. Because of such, I cannot properly import my file. Hence, I > wrote the following script and while it removes the carriage returns/ > new line feeds through the file, it ALSO removes the END OF LINE > carriage return/line feed. So now, I have one file with one record. > > Basically,I need to know how to remove the carriage returns/line feeds > in the file EXCEPT for the ones that is END OF LINE. Can somebody help > me please? > > Dim strSearchString, objFSO, objFile > Const ForReading = 1 > Const ForWriting = 2 > > > Set objFSO = CreateObject("Scripting.FileSystemObject") > Set objFile = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading) > strSearchString = objFile.ReadAll > objFile.Close > > > objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) & > Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) > & ".csv" > Set objFile = objFSO.OpenTextFile("d:\Test\Project_" & DatePart > ("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & > DatePart("d", Date),2) & ".csv", ForWriting) > strSearchString = Replace(strSearchString, VbCrLf, "") > strSearchString = Replace(strSearchString, Vbtab, "") > > objFile.Write strSearchString > objFile.Close > How do you distinguish a CRLF at the end of a field from a CRLF at the end of a line?
From: Richard Mueller [MVP] on 8 Sep 2009 11:45 "Odd" <audrey.nsh(a)gmail.com> wrote in message news:8a2b8b75-480a-4315-88d4-849b5ceb3c96(a)r39g2000yqm.googlegroups.com... > Hi everyone, > > I have a .csv file where certain fields have carriage returns/new line > feeds. Because of such, I cannot properly import my file. Hence, I > wrote the following script and while it removes the carriage returns/ > new line feeds through the file, it ALSO removes the END OF LINE > carriage return/line feed. So now, I have one file with one record. > > Basically,I need to know how to remove the carriage returns/line feeds > in the file EXCEPT for the ones that is END OF LINE. Can somebody help > me please? > > Dim strSearchString, objFSO, objFile > Const ForReading = 1 > Const ForWriting = 2 > > > Set objFSO = CreateObject("Scripting.FileSystemObject") > Set objFile = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading) > strSearchString = objFile.ReadAll > objFile.Close > > > objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) & > Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) > & ".csv" > Set objFile = objFSO.OpenTextFile("d:\Test\Project_" & DatePart > ("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & > DatePart("d", Date),2) & ".csv", ForWriting) > strSearchString = Replace(strSearchString, VbCrLf, "") > strSearchString = Replace(strSearchString, Vbtab, "") > > objFile.Write strSearchString > objFile.Close > When I read a text file I use the ReadLine method of the file object. I loop through all lines of the file until the AtEndOfStream condition is met. I also check each line to see if it is blank: ====== Do Until objFile.AtEndOfStream strLine = Trim(objFile.ReadLine) If (strLine <> "") Then ' Process the line... End If Loop ===== Alternatively, I use the ReadAll method and use the Split function to convert into an array of lines. Again, any line can be blank, especially the last line: === arrLines = Split(objFile.ReadLine, vbCrLf) For Each strLine In arrLines If (strLine <> "") Then ' Process the line... End If Next -- Richard Mueller MVP Directory Services Hilltop Lab - http://www.rlmueller.net --
From: Odd on 8 Sep 2009 11:55 On Sep 8, 11:41 am, "Pegasus [MVP]" <n...(a)microsoft.com> wrote: > "Odd" <audrey....(a)gmail.com> wrote in message > > news:8a2b8b75-480a-4315-88d4-849b5ceb3c96(a)r39g2000yqm.googlegroups.com... > > > > > > > Hi everyone, > > > I have a .csv file where certain fields have carriage returns/new line > > feeds. Because of such, I cannot properly import my file. Hence, I > > wrote the following script and while it removes the carriage returns/ > > new line feeds through the file, it ALSO removes the END OF LINE > > carriage return/line feed. So now, I have one file with one record. > > > Basically,I need to know how to remove the carriage returns/line feeds > > in the file EXCEPT for the ones that is END OF LINE. Can somebody help > > me please? > > > Dim strSearchString, objFSO, objFile > > Const ForReading = 1 > > Const ForWriting = 2 > > > Set objFSO = CreateObject("Scripting.FileSystemObject") > > Set objFile = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading) > > strSearchString = objFile.ReadAll > > objFile.Close > > > objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) & > > Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) > > & ".csv" > > Set objFile = objFSO.OpenTextFile("d:\Test\Project_" & DatePart > > ("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & > > DatePart("d", Date),2) & ".csv", ForWriting) > > strSearchString = Replace(strSearchString, VbCrLf, "") > > strSearchString = Replace(strSearchString, Vbtab, "") > > > objFile.Write strSearchString > > objFile.Close > > How do you distinguish a CRLF at the end of a field from a CRLF at the end > of a line?- Hide quoted text - > > - Show quoted text - Hmm...well, through trial and error. Basically, when I open the .csv file in Excel, the field (on one record) that has a carriage return is represented as a square and the line feed is represented as a new line (in Excel, it is similar to ALT-ENTER). Then, again, when I ran my script, I know that there is a CRLF at the end of the line because I can see that the second line moved up to the first........sorry for the explanation, but truly, that's how I figured out.
From: Pegasus [MVP] on 8 Sep 2009 12:09
"Odd" <audrey.nsh(a)gmail.com> wrote in message news:7c4672bc-7245-4f05-883c-dabe25f0227e(a)y21g2000yqn.googlegroups.com... On Sep 8, 11:41 am, "Pegasus [MVP]" <n...(a)microsoft.com> wrote: > "Odd" <audrey....(a)gmail.com> wrote in message > > news:8a2b8b75-480a-4315-88d4-849b5ceb3c96(a)r39g2000yqm.googlegroups.com... > > > > > > > Hi everyone, > > > I have a .csv file where certain fields have carriage returns/new line > > feeds. Because of such, I cannot properly import my file. Hence, I > > wrote the following script and while it removes the carriage returns/ > > new line feeds through the file, it ALSO removes the END OF LINE > > carriage return/line feed. So now, I have one file with one record. > > > Basically,I need to know how to remove the carriage returns/line feeds > > in the file EXCEPT for the ones that is END OF LINE. Can somebody help > > me please? > > > Dim strSearchString, objFSO, objFile > > Const ForReading = 1 > > Const ForWriting = 2 > > > Set objFSO = CreateObject("Scripting.FileSystemObject") > > Set objFile = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading) > > strSearchString = objFile.ReadAll > > objFile.Close > > > objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) & > > Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) > > & ".csv" > > Set objFile = objFSO.OpenTextFile("d:\Test\Project_" & DatePart > > ("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & > > DatePart("d", Date),2) & ".csv", ForWriting) > > strSearchString = Replace(strSearchString, VbCrLf, "") > > strSearchString = Replace(strSearchString, Vbtab, "") > > > objFile.Write strSearchString > > objFile.Close > > How do you distinguish a CRLF at the end of a field from a CRLF at the end > of a line?- Hide quoted text - > > - Show quoted text - Hmm...well, through trial and error. Basically, when I open the .csv file in Excel, the field (on one record) that has a carriage return is represented as a square and the line feed is represented as a new line (in Excel, it is similar to ALT-ENTER). Then, again, when I ran my script, I know that there is a CRLF at the end of the line because I can see that the second line moved up to the first........sorry for the explanation, but truly, that's how I figured out. ========== If you want your script to be robust then you must stop guessing and use a proper tool to examine your file structure. XVI32.exe is such a tool. You can download it from http://www.chmaas.handshake.de. It lets you examine any file, binary or ASCII so that you can tell exactly where one record ends and the next one starts. |