End of Line
in [VbScript]
|
From: Odd on 8 Sep 2009 12:14 On Sep 8, 11:45 am, "Richard Mueller [MVP]" <rlmueller- nos...(a)ameritech.nospam.net> 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 > > 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 > --- Hide quoted text - > > - Show quoted text - Here is what I have and I still see the carriage returns and line feed Dim strLine, objFSO, objInput, objOutput Const ForReading = 1 Const ForWriting = 2 Set objFSO = CreateObject("Scripting.FileSystemObject") Set objInput = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading) objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) & ".csv" Set objOutput = objFSO.OpenTextFile("d:\Test\Project_" & DatePart ("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) & ".csv", ForWriting) Do Until objInput.AtEndofStream strLine = Trim(objInput.ReadLine) If (strLine <> "") Then strLine = Replace(strLine, vbCrLf, "") strLine = Replace(strLine, vbtab,"") objOutput.WriteLine strLine End If Loop objInput.Close objOutput.Close
From: Odd on 8 Sep 2009 12:21 On Sep 8, 12:09 pm, "Pegasus [MVP]" <n...(a)microsoft.com> wrote: > "Odd" <audrey....(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 fromhttp://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.- Hide quoted text - > > - Show quoted text - Pegasus, The file is in tabular format but comma-delimited. Excel opens the .csv file properly. I have a starting column (Project_ID) and an ending column (Project_Currency_ID). The problem that I have is that I have a column in between the first column and the last column that has entries that have carriage returns and line feeds. I need to get rid of those...
From: Odd on 8 Sep 2009 12:28 On Sep 8, 12:21 pm, Odd <audrey....(a)gmail.com> wrote: > On Sep 8, 12:09 pm, "Pegasus [MVP]" <n...(a)microsoft.com> wrote: > > > > > > > "Odd" <audrey....(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 fromhttp://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.- Hide quoted text - > > > - Show quoted text - > > Pegasus, > > The file is in tabular format but comma-delimited. Excel opens > the .csv file properly. I have a starting column (Project_ID) and an > ending column (Project_Currency_ID). The problem that I have is that I > have a column in between the first column and the last column that has > entries that have carriage returns and line feeds. I need to get rid > of those...- Hide quoted text - > > - Show quoted text - An example would be: Project_ID, Project_Name, Major Assumptions, Project_Currency_ID 3, ProjectABC, SomeAssumptionsaaa, 2 SomeAssumptionsbbb 4, ProjectDEF, SomeAssumptionsccc, 2 5, ProjectGHI, SomeAssumptionsddd,2 So, if you see Project_ID = 3, it is the carriagereturn/line feed in the Major Assumptions columns that I want to get rid of.
From: billious on 8 Sep 2009 12:33 Odd wrote: > 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? [snip] > > 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. "certain fields have carriage returns/new line feeds" Do you mean EMBEDDED within the field? If so, could you count the quotes - an odd number on a text-input line would indicate a newline-within-a-field. Do you mean randomly following a (nominally-comma) separator? Or in place of that separating (comma)? If so, could you count the number of fields in a line, where a field is terminated by a comma except if the comma occurs between one quote and the next? If you have insufficient fields, then the next line may be the final fields of the current record. CSV is a widespread but sloppy standard. Some people will insist that a "properly formatted" csv will contain the separators whenever the field is empty and others will insist that trailing commas are optional. I've heard both arguments. Will this affect how your processing proceeds? Would you be able to press a tool like SED or (g)awk into play to pre-process your file to "standard" format?
From: Richard Mueller [MVP] on 8 Sep 2009 12:38
"Odd" <audrey.nsh(a)gmail.com> wrote in message news:3ccd9e18-9354-4c57-8ec5-4298bc9be107(a)o9g2000yqj.googlegroups.com... On Sep 8, 11:45 am, "Richard Mueller [MVP]" <rlmueller- nos...(a)ameritech.nospam.net> 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 > > 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 > --- Hide quoted text - > > - Show quoted text - Here is what I have and I still see the carriage returns and line feed Dim strLine, objFSO, objInput, objOutput Const ForReading = 1 Const ForWriting = 2 Set objFSO = CreateObject("Scripting.FileSystemObject") Set objInput = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading) objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) & ".csv" Set objOutput = objFSO.OpenTextFile("d:\Test\Project_" & DatePart ("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) & ".csv", ForWriting) Do Until objInput.AtEndofStream strLine = Trim(objInput.ReadLine) If (strLine <> "") Then strLine = Replace(strLine, vbCrLf, "") strLine = Replace(strLine, vbtab,"") objOutput.WriteLine strLine End If Loop objInput.Close objOutput.Close ------------- Pegasus's suggestion may be best. The only thing I can think of is that your comma delimited file has carriage returns embedded in the field values. If the Replace function doesn't remove them, maybe they are vbCr or vbLf characters in addition to vbCrLf. I would try: ======= Do Until objInput.AtEndofStream strLine = Trim(objInput.ReadLine) If (strLine <> "") Then strLine = Replace(strLine, vbCrLf, "") strLine = Replaced(strLine, vbCr, "") strLine = Replaced(strLine, vbLf, "") strLine = Replaced(strLine, vbFormFeed, "") strLine = Replace(strLine, vbtab,"") objOutput.WriteLine strLine End If Loop ======== vbCrLf is Chr(13) & Chr(10) (ANSI carriage return and line feed) vbCr is Chr(13) (ANSI carriage return) vbLf is Chr(10) (ANSI line feed) vbFormFeed is Chr(12) (ANSI form feed) -- Richard Mueller MVP Directory Services Hilltop Lab - http://www.rlmueller.net -- |