End of Line
in [VbScript]
|
From: Odd on 8 Sep 2009 15:07 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..... 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, $00$00) strLine = Replace(strLine, vbLf, $20) strLine = Replace(strLine, $00$00, vbCrLf) objOutput.WriteLine strLine End If Loop objInput.Close objOutput.Close
From: Pegasus [MVP] on 8 Sep 2009 15:12 "Odd" <audrey.nsh(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..... =========== $00 means hex 0. In VB Script, you must write chr(0).
From: Pegasus [MVP] on 8 Sep 2009 15:20 "Odd" <audrey.nsh(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
From: Odd on 8 Sep 2009 15:22 On Sep 8, 3:12 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..... > > =========== > > $00 means hex 0. In VB Script, you must write chr(0).- Hide quoted text - > > - Show quoted text - Thanks Pegasus. That too did not work. I am sending you a sample file via e-mail. Thanks.
From: Odd on 8 Sep 2009 15:34
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. |