End of Line
in [VbScript]
|
From: Odd on 8 Sep 2009 15:39 On Sep 8, 3:34 pm, Odd <audrey....(a)gmail.com> wrote: > On Sep 8, 3:20 pm, "Pegasus [MVP]" <n...(a)microsoft.com> wrote: > > > > > > > "Odd" <audrey....(a)gmail.com> wrote in message > > >news:4ded89ba-24f6-4d46-9d5f-537c8e3a47c2(a)a21g2000yqc.googlegroups.com.... > > On Sep 8, 2:51 pm, "Pegasus [MVP]" <n...(a)microsoft.com> wrote: > > > > "ekkehard.horner" <ekkehard.hor...(a)arcor.de> wrote in message > > > >news:4aa69d8e$0$31867$9b4e6d93(a)newsspool3.arcor-online.net... > > > > > Pegasus [MVP] schrieb: > > > >> "ekkehard.horner" <ekkehard.hor...(a)arcor.de> wrote in message > > > >>news:4aa69868$0$31873$9b4e6d93(a)newsspool3.arcor-online.net... > > > >>> Pegasus [MVP] schrieb: > > > >>> [...] > > > >>>> This is getting a little confusing. A CSV is by definition a text > > > >>>> file. > > > >>>> It has a CRLF at the end of each line, that's all. > > > >>> [...] > > > >>> A .CSV file may have embedded in line endings in (properly delimited) > > > >>> text fields. All Microsoft applications and tools can process such a > > > >>> file without any problem. > > > > >> And what might this "in line ending" be? The OP seems a little > > > >> reluctant > > > >> post it. > > > > It may well be vbCrLf as a dump of my demo file shows: > > > > > 00000000h: 22 49 64 22 3B 22 4E 61 6D 65 22 3B 22 52 65 6D ; > > > > "Id";"Name";"Rem > > > > 00000010h: 61 72 6B 22 3B 22 54 61 69 6C 22 0D 0A 31 3B 22 ; > > > > ark";"Tail"..1;" > > > > 00000020h: 41 6C 70 68 61 22 3B 22 72 65 6D 31 0D 0A 72 65 ; > > > > Alpha";"rem1..re > > > > 00000030h: 6D 32 22 3B 34 37 31 31 0D 0A 32 3B 22 42 65 74 ; > > > > m2";4711..2;"Bet > > > > 00000040h: 61 22 3B 22 72 65 6D 33 0D 0A 72 65 6D 34 22 3B ; > > > > a";"rem3..rem4"; > > > > 00000050h: 34 37 31 32 0D 0A ; 4712.. > > > > > Just to be sure, I changed > > > > > sSQL = "INSERT INTO [" & sTFName & "] VALUES ( 1, 'Alpha', '" & > > > > Join( Array( "rem1", "rem2" ), vbCrLf ) & "', 4711 )" > > > > > to > > > > > sSQL = "INSERT INTO [" & sTFName & "] VALUES ( 1, 'Alpha', '" & > > > > Join( Array( "rem1", "rem2" ), vbLf ) & "', 4711 )" > > > > > in my demo script - no problems at all. > > > > The dump you posted does not really seem to be a "CSV" - it uses > > > semicolons > > > as delimiters, not commas. In an attempt to put an end to the guessing > > > game, > > > I created my own Excel file and made one of the cells a two-liner, by > > > pressing Alt+Enter after the first line. When I saved the file as a CSV, > > > this generated a $0a in this particular field, which is not really > > > surprising. Each record was terminated by a $0d$0a, which is also as > > > expected. > > > > If the OP's file has the same structure then he could resolve his problem > > > by > > > performing a triple replace: > > > 1. Replace all CRLFs with $00$00. > > > 2. Replace all LFs with $20 (space) > > > 3. Replace all $00$00 with CRLFs. > > > > If the OP's file is not a CSV but a tab-delimited file then he can still > > > use > > > the same three-step replacement process.- Hide quoted text - > > > > - Show quoted text - > > > Does not do anything..... > > > ============= > > > You also need to perform the triple change on the whole data block, not on a > > line-by-line basis: > > Const ForReading = 1 > > Const ForWriting = 2 > > > Set objFSO = CreateObject("Scripting.FileSystemObject") > > Set objInput = objFSO.OpenTextFile("d:\t.csv", ForReading) > > > objFSO.CreateTextFile "d:\Project_" & DatePart("yyyy", Date) _ > > & Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2) & > > ".csv" > > Set objOutput = objFSO.OpenTextFile("d:\Project_" _ > > & DatePart("yyyy", Date) & Right("0" & DatePart("m", Date), 2) _ > > & Right("0" & DatePart("d", Date),2) & ".csv", ForWriting) > > > sData = objInput.ReadAll > > sData = Replace(sData, VbCrLf, Chr(0) & Chr(0)) > > sData = Replace(sdata, vbLf, " ") > > sData = Replace(sData, Chr(0) & Chr(0), VbCrLf) > > objOutput.WriteLine sData > > objOutput.Close- Hide quoted text - > > > - Show quoted text - > > Nope. Hasn't changed anything.- Hide quoted text - > > - Show quoted text - Ok, Ijust sent you to your e-mail address......let me know.....
From: ekkehard.horner on 8 Sep 2009 16:12 Pegasus [MVP] schrieb: [...] > The dump you posted does not really seem to be a "CSV" - it uses semicolons > as delimiters, not commas. In an attempt to put an end to the guessing game, That is the German locale form of a .CSV; cross language processing may be cumbersome some times, but as long as you stay with your locale, PROCESSING .CSVs WITH EMBEDDED LINE-ENDINGS CAUSE NO PROBLEMS AT ALL AS LONG AS YOU USE THE PROPER (MICROSOFT) TOOLS > I created my own Excel file and made one of the cells a two-liner, by > pressing Alt+Enter after the first line. When I saved the file as a CSV, > this generated a $0a in this particular field, which is not really > surprising. Each record was terminated by a $0d$0a, which is also as > expected. > > If the OP's file has the same structure then he could resolve his problem by > performing a triple replace: > 1. Replace all CRLFs with $00$00. > 2. Replace all LFs with $20 (space) > 3. Replace all $00$00 with CRLFs. > > If the OP's file is not a CSV but a tab-delimited file then he can still use > the same three-step replacement process. ALL THIS EXTRA WORK MAY BE OF DIDACTIC/ACADEMIC MERITS, BUT DISTRACT THE OP FROM SOLVING HIS PROBLEM IN A FAST, ROBOUST, AND RELIABLE WAY
From: ekkehard.horner on 8 Sep 2009 16:28 Odd schrieb: [...] > On Sep 8, 12:38 pm, "Richard Mueller [MVP]" <rlmueller- > nos...(a)ameritech.nospam.net> wrote: [...] >> 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 [...] > > What Richard is saying is correct.....the carriage returns and line > feeds is embedded in the field values. Richard: I tried your code..it > removed the tab,but did not remove the carriage return nor the line > feed. That is to be expected, because ReadLine will read data from the file upto the bytes it considers line endings (vbLf, vbCrLf; I'm fairly sure that vbCr isn't a line ending, but don't believe, do your own tests), discard the line ending, and return the 'clean' string/line. [Richard, you use ADO frequently - please help me to convince Odd and Pegasus that giving the text driver a try is NOT stupid]
From: ekkehard.horner on 8 Sep 2009 17:10 Pegasus [MVP] schrieb: [...] >> I created my own Excel file and made one of the cells a two-liner, by >> pressing Alt+Enter after the first line. When I saved the file as a CSV, >> this generated a $0a in this particular field, which is not really $0a == vbCr >> surprising. Each record was terminated by a $0d$0a, which is also as >> expected. >> >> If the OP's file has the same structure then he could resolve his problem >> by >> performing a triple replace: >> 1. Replace all CRLFs with $00$00. 1. Replace all CRLFs with vbLf - optional >> 2. Replace all LFs with $20 (space) 2. Replace all ($0a == vbCr) with $20 (space) - important >> 3. Replace all $00$00 with CRLFs. 3. Replace all vbLf with CRLFs. - optional >> >> If the OP's file is not a CSV but a tab-delimited file then he can still >> use >> the same three-step replacement process.- Hide quoted text - >> >> - Show quoted text - > > Does not do anything..... > > =========== > > $00 means hex 0. In VB Script, you must write chr(0). > >
From: Todd Vargo on 8 Sep 2009 17:20
"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 From your other posts it sounds like the .csv file was created by Excel and you see blocks (in Notepad or other editor) for the embedded line feeds. Removing the embedded line feeds is simple but then you have the lines running into each other. I suggest replacing the the line feeds with spaces so you wont have lines crashing together. To do this, 1) Replace all Chr(10) with a space. 2) Replace all Chr(13) + Chr(32) with Chr(13) + Chr(10). 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 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, VbLf, " ") strSearchString = Replace(strSearchString, VbCr & " ", VbCrLf) objFile.Write strSearchString objFile.Close MsgBox "done." -- Todd Vargo (Post questions to group only. Remove "z" to email personal messages) |