Eliminate carriage returns in fields in CSV

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Eliminate carriage returns in fields in CSV

Logged in as: Guest

arrSession:exec spGetSession 2,2,62284

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Eliminate carriage returns in fields in CSV
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Eliminate carriage returns in fields in CSV - 7/9/2008 9:10:05 PM

inselaffe

Posts: 2
Score: 0
Joined: 7/9/2008
Status: offline

I have some CSV files which I usually read by grabbing each line with ReadLine and doing Split on the commas.

However I have a few files which may contain carriage returns in some fields, which means Read Line does not grab the entire record.

for example

"Name","Address","Tel"
"Bob","34 Main Street","0123456789"
"Doug","12 High Street
Hicksville","0123456787"
"Fred","32 Main Street","0123456788"

Where the second record gets split onto different lines when read as a text file. How can this be best dealt with?

Post #: 1

RE: Eliminate carriage returns in fields in CSV - 7/9/2008 9:47:55 PM

ehvbs

Posts: 2201
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi inselaffe,

use the ADO text Driver - like this:

Dim oFS    : Set oFS = CreateObject( "Scripting.FileSystemObject" )
 Dim sTDir  : sTDir   = oFS.GetAbsolutePathName( ".\adotext" )
 Dim sTName : sTName  = "inselaffe02.csv"
 Dim sFSpec : sFSpec  = oFS.GetAbsolutePathName( sTDir & "\" & sTName )

WScript.Echo "----------------------"
 WScript.Echo oFS.OpenTextFile( sFSpec ).ReadAll
 WScript.Echo "----------------------"

Dim sCS   : sCS     = Join( Array( _
                          "Provider=MSDASQL.1" _
                        , "Driver={Microsoft Text Driver (*.txt; *.csv)}" _
                        , "DBQ=" & sTDir _
                                  ), ";" )
 Dim oCN   : Set oCN = CreateObject( "ADODB.Connection" )
 Dim sSQL  : sSQL    = "SELECT * FROM [" & sTName & "]"
 Dim oRS

oCN.Open sCS
 Set oRS = oCN.Execute( sSQL )
 Do Until oRS.EOF
    Dim oFld
    For Each oFld In oRS.Fields
        WScript.Echo oFld.Name, Replace( oFld.Value, vbCrLf, "\r\n" )
    Next
    WScript.Echo "----------------------"
    oRS.MoveNext
 Loop
 oCN.Close

output:

As you can see, I had to change the field separator to ";", because of my German regional settings. For
English locales the code should work with "," without change; in case of problems try to describe your
input file using a schema.ini file in the same directory.

Good luck!

ehvbs

(in reply to inselaffe)

Post #: 2

RE: Eliminate carriage returns in fields in CSV - 7/9/2008 10:48:45 PM

inselaffe

Posts: 2
Score: 0
Joined: 7/9/2008
Status: offline

That's great, thanks. I had thought of automating Excel to read the contents but that seemed overkill. That should do nicely.

(in reply to ehvbs)

Post #: 3

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Eliminate carriage returns in fields in CSV

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts