End of Line
in [VbScript]
|
From: ekkehard.horner on 8 Sep 2009 18:17 Pegasus [MVP] schrieb: [...] > Unfortunately they are CRLFs. If you're interested then I'll send you the > OP's sample file. Please, do so.
From: Pegasus [MVP] on 8 Sep 2009 18:24 "ekkehard.horner" <ekkehard.horner(a)arcor.de> wrote in message news:4aa6d568$0$32668$9b4e6d93(a)newsspool2.arcor-online.net... >> > Because all these "use FSO & string replacements" trials & errors did > cost the OP half a working day Maybe the OP's reluctance to post a dump of his file played a major role in this saga.
From: Pegasus [MVP] on 8 Sep 2009 18:27 "ekkehard.horner" <ekkehard.horner(a)arcor.de> wrote in message news:4aa6d736$0$32668$9b4e6d93(a)newsspool2.arcor-online.net... > Pegasus [MVP] schrieb: > [...] >> Unfortunately they are CRLFs. If you're interested then I'll send you the >> OP's sample file. > Please, do so. It's on its way.
From: ekkehard.horner on 8 Sep 2009 19:05 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.
From: Odd on 8 Sep 2009 22:36
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 |