End of Line
in [VbScript]
|
From: Odd on 8 Sep 2009 23:11 On Sep 8, 10:36 pm, Odd <audrey....(a)gmail.com> wrote: > On Sep 8, 7:05 pm, "ekkehard.horner" <ekkehard.hor...(a)arcor.de> wrote: > > > > > > > Odd schrieb:> 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. > > > [...] > > Using > > > the emailed Project.csv > > > this schema.ini entry > > > [Project_test.csv] > > ColNameHeader=True > > CharacterSet=1252 > > Format=Delimited(,) > > DecimalSymbol=. > > Col1=Project_ID Integer > > Col2=Project_Name Char Width 50 > > Col3=High_Level_Outline Text > > Col4=Project_Currency_ID Integer > > > the lines > > > Format=Delimited(,) > > DecimalSymbol=. > > > are needed for my German locale; I *think* you should use > > > Format=CSVDelimited > > > this modification of my proof of concept code from about 5 hours ago > > > Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" ) > > Dim sDDir : sDDir = oFS.GetAbsolutePathName( ".\adotext" ) > > Dim sTFName : sTFName = "Project_test.csv" > > Dim sFSpec : sFSpec = oFS.BuildPath( sDDir, sTFName ) > > Dim oADOCN : Set oADOCN = CreateObject( "ADODB.Connection" ) > > Dim sCS > > If False Then > > sCS = Join( Array( _ > > "Provider=MSDASQL" _ > > , "Driver={Microsoft Text Driver (*.txt; *.csv)}" _ > > , "DBQ=" & sDDir ), ";" ) > > Else > > sCS = Join( Array( _ > > "Provider=Microsoft.Jet.OLEDB.4.0" _ > > , "Data Source=" & sDDir _ > > , "Extended Properties=""" & Join( Array( _ > > "text" _ > > , "HDR=Yes" _ > > , "FMT=Delimited;""" ), ";" ) ), ";" ) > > End If > > Dim sSQL > > > WScript.Echo "Working with", sFSpec > > > WScript.Echo "Using CS", sCS > > oADOCN.Open sCS > > > If Not oFS.FileExists( sFSpec ) Then > > WScript.Echo "Missing", sFSpec > > WScript.Quit 1 > > End If > > > sSQL = "SELECT * FROM [" & sTFName & "]" > > WScript.Echo sSQL > > Dim oRS : Set oRS = oADOCN.Execute( sSQL ) > > Do Until oRS.EOF > > WScript.Echo "--------------" > > Dim oFld > > For Each oFld In oRS.Fields > > WScript.Echo "---", oFld.Name & ":" > > WScript.Echo Trim( oFld.Value ) > > WScript.Echo "---" > > Next > > oRS.MoveNext > > Loop > > oRS.Close > > oADOCN.Close > > > (i.e.: changed file name, no generation of test data, Trim() added > > to eliminate the excessive whitespace) > > > I get: > > > ... > > Using CS Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\wis\_vbs\0506\dev\foru > > SELECT * FROM [Project_test.csv] > > -------------- > > --- Project_ID: > > 5 > > --- > > --- Project_Name: > > ProjectA > > --- > > --- High_Level_Outline: > > TBD > > --- > > --- Project_Currency_ID: > > 2 > > --- > > -------------- > > --- Project_ID: > > 7 > > --- > > --- Project_Name: > > ProjectB > > --- > > --- High_Level_Outline: > > TBD > > --- > > --- Project_Currency_ID: > > 2 > > --- > > -------------- > > --- Project_ID: > > 24 > > --- > > --- Project_Name: > > ProjectC > > --- > > --- High_Level_Outline: > > TBD > > --- > > --- Project_Currency_ID: > > 2 > > --- > > -------------- > > --- Project_ID: > > 41 > > --- > > --- Project_Name: > > ProjectE > > --- > > --- High_Level_Outline: > > Eliminate the use of Returned Item > > Eliminate the use of Returned Item > > Creation of a new electronic means > > Creation of a new database > > internal applications only > > Addition of new return > > > --- > > --- Project_Currency_ID: > > 2 > > --- > > -------------- > > --- Project_ID: > > 46 > > --- > > --- Project_Name: > > ProjectG > > --- > > --- High_Level_Outline: > > TBD > > --- > > --- Project_Currency_ID: > > 2 > > --- > > === EmbeddedLineEndings: 0 done (00:00:00) ==================================== > > > QED: ADO/Text Driver does the job without any magic. > > Hi everyone, > > I honestly did not think it was going to be this difficult but I do > want to thank-you all for your kind effort. As mentioned, I think my > original script seemed to have work, however, it all also removed CRLF > at the end of the line which I did not want it to do. (i.e. I wanted > to remove only the CRLF that were embedded). I do apologize if I > seemed reluctant to send the file but I was trying to figure out what > the debug script was all about. This is why I asked for an e-mail > address. It's easier and faster. > > Nevertheless, I want to try ekkehard.horner's script above and see > where that takes me. The sample file provided, if you noticed, only > had the High_Level_Outline field that had the CRLF issue. Will the > code cater for multiple fields with this issue. Anyway, I will try. > Can someone let me know if I just copy and paste and run? > > Thank-you again, > > Audrey- Hide quoted text - > > - Show quoted text - Ok I tried it. It seems to be ok. How would I output all of this to a text file??
From: Todd Vargo on 9 Sep 2009 00:51 Pegasus [MVP] wrote: > Unfortunately they are CRLFs. If you're interested then I'll send you the > OP's sample file. Not necessary to send the file, I believe you. However, my version of Excel does not save .csv files with embedded CRLFs, it only embeds the LF. I did some poking around and found that MS Works saves the embedded CRLFs. Note, OP said the file opens fine in Excel but the CR is represented as a square. This is exactly what happens when I open a .csv that was saved by Works in Excel. Another observation I made is, if you remove all the CRs and leave the LFs intact, Excel will open the file fine. And then if you save the file back, the end of line CRs will be restored but none of the embedded ones. Perhaps all OP really needs is the remove the CRs, OP will have to verify. -- Todd Vargo (Post questions to group only. Remove "z" to email personal messages)
From: ekkehard.horner on 9 Sep 2009 03:36 Odd schrieb: [...] > Ok I tried it. It seems to be ok. How would I output all of this to a > text file?? Const adClipString = 2 ' 00000002 Const csSep = "," Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" ) Dim sDDir : sDDir = oFS.GetAbsolutePathName( ".\adotext" ) Dim sTFName : sTFName = "Project_test.csv" Dim sFSpec : sFSpec = oFS.BuildPath( sDDir, sTFName ) Dim oADOCN : Set oADOCN = CreateObject( "ADODB.Connection" ) Dim sCS : sCS = Join( Array( _ "Provider=MSDASQL" _ , "Driver={Microsoft Text Driver (*.txt; *.csv)}" _ , "DBQ=" & sDDir ), ";" ) Dim sSQL : sSQL = "SELECT * FROM [" & sTFName & "]" WScript.Echo "Working with", sFSpec WScript.Echo "Using CS", sCS WScript.Echo "SQL", sSQL oADOCN.Open sCS Dim oRS : Set oRS = oADOCN.Execute( sSQL ) Dim sHead : sHead = "" Dim oFld For Each oFld In oRS.Fields sHead = sHead & csSep & oFld.Name Next sHead = Mid( sHead, 2 ) Dim sFAll : sFAll = oRS.GetString( adClipString, , csSep, vbLf, "" ) oRS.Close oADOCN.Close Dim aRpl : aRpl = Array( vbTab, "", vbCrLf, "", vbLf, vbCrLf ) Dim nIdx For nIdx = 0 To UBound( aRpl ) Step 2 sFAll = Replace( sFAll, aRpl( nIdx ), aRpl( nIdx + 1 ) ) Next Dim oRE : Set oRE = New RegExp oRE.Global = True oRE.Pattern = " +" sFAll = oRE.Replace( sFAll, " " ) sFAll = sHead & vbCrLf & sFAll WScript.Echo sFAll Dim dtNow : dtNow = Date sFSpec = "Project_" & Year( dtNow ) & Right( 100 + Month( dtNow ), 2 ) & Right( 100 + Day( dtNow ), 2 ) & ".csv" oFS.CreateTextFile( oFS.BuildPath( sDDir, sFSpec ), True ).Write sFAll output: === EmbeddedLineEndings: how to deal with embedded line endings =============== Working with C:\wis\_vbs\0506\dev\forum\adotext\Project_test.csv Using CS Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\wis\_vbs\0506\dev\forum\adotext SQL SELECT * FROM [Project_test.csv] Project_ID,Project_Name,High_Level_Outline,Project_Currency_ID 5,ProjectA,TBD,2 7,ProjectB,TBD,2 24,ProjectC,TBD,2 41,ProjectE,Eliminate the use of Returned ItemEliminate the use of Returned ItemCreation of a new electronic meansCreation of a new database inte rnal applications onlyAddition of new return,2 46,ProjectG,TBD,2 === EmbeddedLineEndings: 0 done (00:00:01) ====================================
From: Odd on 9 Sep 2009 09:21 On Sep 9, 3:36 am, "ekkehard.horner" <ekkehard.hor...(a)arcor.de> wrote: > Odd schrieb: > [...] > > > Ok I tried it. It seems to be ok. How would I output all of this to a > > text file?? > > Const adClipString = 2 ' 00000002 > Const csSep = "," > > Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" ) > Dim sDDir : sDDir = oFS.GetAbsolutePathName( ".\adotext" ) > Dim sTFName : sTFName = "Project_test.csv" > Dim sFSpec : sFSpec = oFS.BuildPath( sDDir, sTFName ) > Dim oADOCN : Set oADOCN = CreateObject( "ADODB.Connection" ) > Dim sCS : sCS = Join( Array( _ > "Provider=MSDASQL" _ > , "Driver={Microsoft Text Driver (*.txt; *.csv)}" _ > , "DBQ=" & sDDir ), ";" ) > Dim sSQL : sSQL = "SELECT * FROM [" & sTFName & "]" > > WScript.Echo "Working with", sFSpec > WScript.Echo "Using CS", sCS > WScript.Echo "SQL", sSQL > > oADOCN.Open sCS > Dim oRS : Set oRS = oADOCN.Execute( sSQL ) > Dim sHead : sHead = "" > Dim oFld > For Each oFld In oRS.Fields > sHead = sHead & csSep & oFld.Name > Next > sHead = Mid( sHead, 2 ) > Dim sFAll : sFAll = oRS.GetString( adClipString, , csSep, vbLf, "" ) > oRS.Close > oADOCN.Close > > Dim aRpl : aRpl = Array( vbTab, "", vbCrLf, "", vbLf, vbCrLf ) > Dim nIdx > For nIdx = 0 To UBound( aRpl ) Step 2 > sFAll = Replace( sFAll, aRpl( nIdx ), aRpl( nIdx + 1 ) ) > Next > > Dim oRE : Set oRE = New RegExp > oRE.Global = True > oRE.Pattern = " +" > sFAll = oRE.Replace( sFAll, " " ) > > sFAll = sHead & vbCrLf & sFAll > > WScript.Echo sFAll > > Dim dtNow : dtNow = Date > sFSpec = "Project_" & Year( dtNow ) & Right( 100 + Month( dtNow ), 2 ) & Right( 100 + > Day( dtNow ), 2 ) & ".csv" > oFS.CreateTextFile( oFS.BuildPath( sDDir, sFSpec ), True ).Write sFAll > > output: > > === EmbeddedLineEndings: how to deal with embedded line endings =============== > Working with C:\wis\_vbs\0506\dev\forum\adotext\Project_test.csv > Using CS Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; > *.csv)};DBQ=C:\wis\_vbs\0506\dev\forum\adotext > SQL SELECT * FROM [Project_test.csv] > Project_ID,Project_Name,High_Level_Outline,Project_Currency_ID > 5,ProjectA,TBD,2 > 7,ProjectB,TBD,2 > 24,ProjectC,TBD,2 > 41,ProjectE,Eliminate the use of Returned ItemEliminate the use of Returned > ItemCreation of a new electronic meansCreation of a new database inte > rnal applications onlyAddition of new return,2 > 46,ProjectG,TBD,2 > > === EmbeddedLineEndings: 0 done (00:00:01) ==================================== Hi, I copy and pasted the code and ran it. Nothing happened. The Project_Test.csv file is under D:\Test\adotext and the script is under D:\Test. Please advise.
From: ekkehard.horner on 9 Sep 2009 09:53
Odd schrieb: [...] > I copy and pasted the code and ran it. Nothing happened. The > Project_Test.csv file is under D:\Test\adotext and the script is under > D:\Test. Mea culpa: The first lines Option Explicit Const adClipString = 2 ' 00000002 Const csSep = "," got lost in copy & paste. My apologies. If adding these lines doen't help: There should be at least some output (especially as the first version did seem "to be ok"). So post what you get. E.g.: If I remove the source file, I get an error message: Working with C:\wis\_vbs\0506\dev\forum\adotext\Project_test.csv Using CS Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\wis\_vbs\0506\dev\forum\adotext SQL SELECT * FROM [Project_test.csv] C:\programme\bin\xplore.wsf(0, 2) Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Text Driver] Das Microsoft Jet-Datenbankmodul konnte da s Objekt 'Project_test.csv' nicht finden. Stellen Sie sicher, dass das Objekt existiert und dass die Namens- und Pfadangaben richtig eingegeben wurden .. complaining about the missing file. Without a proper schema.ini, the script will create an output file, but the format will be wrong. You could try if cscript //X <yourfile.vbs> will start a debugger that lets you step thru the code. |