End of Line
in [VbScript]
|
From: Odd on 8 Sep 2009 14:21 On Sep 8, 2:11 pm, "ekkehard.horner" <ekkehard.hor...(a)arcor.de> wrote: > 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. Hi everyone, Thanks for the quick response. Is there anyway that I can e-mail this file to you?
From: Odd on 8 Sep 2009 14:36 On Sep 8, 12:38 pm, "Richard Mueller [MVP]" <rlmueller- nos...(a)ameritech.nospam.net> wrote: > "Odd" <audrey....(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 > --- Hide quoted text - > > - Show quoted text - 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.
From: Odd on 8 Sep 2009 14:48 On Sep 8, 2:36 pm, Odd <audrey....(a)gmail.com> wrote: > On Sep 8, 12:38 pm, "Richard Mueller [MVP]" <rlmueller- > > > > > > nos...(a)ameritech.nospam.net> wrote: > > "Odd" <audrey....(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 > > --- Hide quoted text - > > > - Show quoted text - > > 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.- Hide quoted text - > > - Show quoted text - -d 100 500 13D9:0100 50 72 6F 6A 65 63 74 5F-49 44 2C 50 72 6F 6A 65 Project_ID,Proje 13D9:0110 63 74 5F 4E 61 6D 65 2C-48 69 67 68 5F 4C 65 76 ct_Name,High_Lev 13D9:0120 65 6C 5F 4F 75 74 6C 69-6E 65 2C 45 61 72 6C 79 el_Outline,Early 13D9:0130 5F 49 64 65 6E 74 69 66-69 65 64 5F 52 69 73 6B _Identified_Risk 13D9:0140 73 2C 50 72 6F 6A 65 63-74 5F 43 75 72 72 65 6E s,Project_Curren 13D9:0150 63 79 5F 49 44 0D 0A 35-2C 50 72 6F 6A 65 63 74 cy_ID.. 5,Project 13D9:0160 20 41 2C 54 42 44 20 20-20 20 20 20 20 20 20 20 A,TBD 13D9:0170 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0180 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0190 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:01A0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:01B0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:01C0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:01D0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:01E0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:01F0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0200 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0210 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0220 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0230 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0240 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0250 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0260 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0270 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0280 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0290 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:02A0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:02B0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:02C0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:02D0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:02E0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:02F0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0300 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0310 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0320 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0330 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0340 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0350 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0360 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0370 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0380 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0390 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:03A0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:03B0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:03C0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:03D0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:03E0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:03F0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0400 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0410 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0420 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0430 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0440 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0450 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0460 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0470 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0480 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0490 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:04A0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:04B0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:04C0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:04D0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:04E0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:04F0 20 20 20 20 20 20 20 20-20 20 20 20 20 20 20 20 13D9:0500 20 -q
From: Pegasus [MVP] on 8 Sep 2009 14:51 "ekkehard.horner" <ekkehard.horner(a)arcor.de> wrote in message news:4aa69d8e$0$31867$9b4e6d93(a)newsspool3.arcor-online.net... > Pegasus [MVP] schrieb: >> "ekkehard.horner" <ekkehard.horner(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.
From: Pegasus [MVP] on 8 Sep 2009 15:03
"Odd" <audrey.nsh(a)gmail.com> wrote in message news:5351a2c0-625e-4abd-92f9-bb4ad3bafbfc(a)o41g2000yqb.googlegroups.com... On Sep 8, 2:36 pm, Odd <audrey....(a)gmail.com> wrote: > On Sep 8, 12:38 pm, "Richard Mueller [MVP]" <rlmueller- > > > > > > nos...(a)ameritech.nospam.net> wrote: > > "Odd" <audrey....(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 > > --- Hide quoted text - > > > - Show quoted text - > > 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.- Hide quoted text - > > - Show quoted text - The dump you posted shows that you have a CSV (Comma-Separated Values) file. So far so good. Unfortunately your header record contains a large number of spaces and there is no data record in your dump! If you still need assistance then you could rerun my command sequence, modified like so: 1. Copy the "CSV" file to c:\CSV.CSV. Note: you *must* select an 8.3 file name! 2. Type these commands at the Command Prompt: debug c:\csv.csv {Enter} d 1000 {Enter} (repeat this command with different numbers until you can see one of your problem records) q {Enter} 3. Type these commands at the Command Prompt: debug c:\csv.csv {Enter} d xxxx yyyy {Enter} (xxxx is the number you used in Step 2 above. yyyy is xxxx plus 1000) q {Enter} notepad c:\csv.deb{Enter} 4. Paste what you see into your reply. Alternatively, if the file is of reasonable size, you could send it to pegasus_fnlATyahooDOTcom. |