Vista - How to remove carriage returns in a text file.

Hello there, I have visited this forum before a lot of times and have found some useful information. But I can't seem to find some posts where it can help me with this.

I have a 35mb text file with 300,000 lines of data. All of which are needed.

Here is what one line should contain:


And here is a sample data:

How do I remove the carriage returns so that that whole info is only in one line? The text with the sentences/descriptions is only under one column. How do I make it like that so that when I convert the text file into MySQL, the whole personal info thing would be only under one column?

I know so little about VB Scripting.

Thanks, I do hope I can get some replies.

Sigheart wrote:This strips out CR/LF first, then CR and last LF from "orgText" and
places the result in "tmpText".

tmpText = orgText
tmpText = Replace(tmpText, vbCrLf, "")
tmpText = Replace(tmpText, vbCr, "")
tmpText = Replace(tmpText, vbLf, "")

--
..::[ Hz ]::.

"Hz" <hz@xxxxxx> wrote in message
news:uFq8nBD5JHA.972@xxxxxxSince CRLF is a combination of CR and LF, the following code should suffice:
tmpText = Replace(orgText, vbCr, "")
tmpText = Replace(tmpText, vbLf, "")

Sigheart schrieb:Is that a complete list of the (28?) field names of the MySQL table the data
should be imported to/in?
Is this one logical record? Is "412" the pk1 for this record? Do *all* records
start with a number and "|" (as RegExp.Pattern "^\d+\|")?
Is "2008-10-09 18:41:10.857000000" the dtmodified of this record? Could
you post/attach a small sample of your input file?
[...]

For me it seems that replacing lineendings with nothing globally (without
regard to the structure of your input file) won't solve your problem.

A kind of plan:

Loop over the 300,000 lines of your input file
If a line starts a new record
If you have a collection of ca 29 data items
join them with "|" and write the line to output
start collecting non blank lines into a Dim aParts( 28 ) array
Else
If blank line
ignore (or prepare for adding space to previous/next? item
Else
collect
join and write last collection

Good luck!

Pegasus [MVP] wrote:Yes I know. I just meant to give him an idea how it works so he can
figure out the rest by himself. That's why I explained what each line
does. Instead of writing 100 lines of code for him, I wanted to give
him the basic idea.

--
..::[ Hz ]::.

"Sigheart" <guest@xxxxxx-email.com> wrote in message
news:d2d318c63e4d3c7a2288bffc8f521b42@xxxxxx-gateway.com...I hate to hijack this thread, but...

How can we post and read long lines like the one above without line wrap
occurring?
My OE6 allows me to copy that 325-character line with no wrap! Apparently
line wrap is a function of the message sending client, not the receiving
client.

-Paul Randall

Well, it has 51 columns. When I pasted it here, the other columns where not included. But you get the idea.


Yes.. ALL logical record starts with a number AND followed by a "|". You are right about the dtmodified. Every column is separated by a "|". So if you find 2 vertical bars, it means that the value is NULL.

So, the only possible solution I can do is create a program that counts the number of "|" and checks if the first column is a number followed by a "|", then if the counter reaches 51 it will go back to 1 then do it again.
What do you think of that?

Sigheart wrote:If you have a separator for each column "|" and an end of line char ";",
then all you have to do is (pseudo code):

open txtFile
DO
read txtLine
txtCols = split(txtLine, "|")
insert txtCols -> DB as a new row
UNTIL EOF

Then decide if "||" = NULL or = "" in the SQL insert. I would avoid NULL
if I was doing it.

Remember that
"any text" + NULL = NULL
but
"any text" + "" = "any text"

--
..::[ Hz ]::.

Sigheart schrieb:[...][...]

HowTo: Start (and finish) a simple VBScript project

[My first interest is to help Sigheart to solve the problem of converting
the multiline input file(s) to a format suitable for importing into a MySQL
database. But I think this project is a good opportunity to discuss the
ways/rules/strategies/methods to do simple projects in VBScript. If you
feel my pursuit of this second aim distract from Sigheart's problem, just
stop me by saying so.]

A (VBScript) project is an undertaking to achieve the solution for a problem
(by writing at least one VBS script).

So starting a project means to create a folder and two files: a .notes file
to collect all information pertaining to the problem and the intended solution
and a .vbs file to implement the later. For that you need a good name forthe
project. I choose "Sigheart", because right now I don't know enough about
the project to come up with something better.

C:\wis\_vbs\0506\dev\forum
md Sigheart

C:\wis\_vbs\0506\dev\forum
cd Sigheart

C:\wis\_vbs\0506\dev\forum\Sigheart
copy con sigheart.notes
## sigheart.notes - notes for project Sigheart
^Z
1 Datei(en) kopiert.

C:\wis\_vbs\0506\dev\forum\Sigheart
copy con sigheart.vbs
''# sigheart.vbs - convert multiline text for for import into MySQL
^Z
1 Datei(en) kopiert.

[yes, I will correct the "for" barrage immediately.] If your editor supports
project management, create a project (named sigheart for consistency) and
add those files to it. I use UltraEdit; so my project folder now looks like:

C:\wis\_vbs\0506\dev\forum\Sigheart
tree /A /F ..\Sigheart >>sigheart.notes

C:\wis\_vbs\0506\dev\forum\Sigheart
cscript sigheart.vbs
Auflistung der Ordnerpfade
Datentr"gernummer: 0006FE80 F4C8:437E
C:\WIS\_VBS\0506\DEV\FORUM\SIGHEART
sigheart.notes
sigheart.prj
sigheart.vbs

Es sind keine Unterordner vorhanden

As you can see, I use device rerouting (>>sigheart.notes) to append information
to my .notes file for later copy & paste. If you don't use an editor withproject
management, push "find a decent editor/IDE" an your ToDo stack and start two
instances of Notepad to have both sigheart.notes and sigheart.vbs at yourfinger
tips.

After some editing, my first version of sigheart.vbs looks like:

01 ''# sigheart.vbs - convert multiline text file for import into MySQL
02 ' <preamble: author, copyright, ...>
03
04 ''# Config
05 Option Explicit
06
07 ''# Globals
08 Dim goFS : Set goFS = CreateObject( "Scripting.FileSystemObject" )
09
10 ''# Dispatch
11
12 WScript.Quit doMain()
13
14 ''# Main Functions
15
16 ''= doMain - convert multiline text file for import into MySQL
17 Function doMain()
18 Dim nErrCode : nErrCode = 0 ' assume no error
19 WScript.Echo WScript.ScriptName & "::doMain()"
20 WScript.Echo "not implemented yet."
21 doMain = nErrCode
22 End Function

A test run

C:\wis\_vbs\0506\dev\forum\Sigheart
cscript sigheart.vbs
sigheart.vbs::doMain()
not implemented yet.

proves that we have a correct program. It adheres to some important "best
practice" rules:

(1) purpose of the script clearly stated
(2) "Option Explicit" used to avoid silly mistakes (typos)
(3) Few (just one) global variable(s), clearly marked by "g" prefix;
all interesting things are done in Subs/Functions, so further
globals can't be used without introducing them explicitly
(4) The script returns ERRORLEVEL to the operating system (so it
can be used in .bat/.cmd files or called as a child process)

To remedy the slight flaw - it does not solve the problem - is left
as an exercise for the reader. [just trying to joke]

The second advantage of the "do the work in a function" approach is that
we can let the programm do different things in an orderly fashion: To
tackle a sub problem of the main task, to try out different strategies
to solve the same (sub) problem, to make sure a newly discovered VBScript
function works the way we think, or just to make writing or talking about
code more comfortable by developing some tools, all you have to do is:

(1) Add another Function - e.g.:

24 ''= lineNumberFile - prepend line numbers to text file lines
25 Function lineNumberFile()
26 Dim nErrCode : nErrCode = 0 ' assume no error
27 WScript.Echo WScript.ScriptName & "::lineNumberFile()"
28
29 Dim sFSpec : sFSpec = ".\sigheart.vbs"
30 Dim aLines : aLines = Split( goFS.OpenTextFile( sFSpec ).ReadAll(), vbCrLf )
31 Dim nWidth : nWidth = Len( CStr( UBound( aLines ) + 1 ) )
32 Dim sLnPfx : sLnPfx = String( nWidth, "0" )
33 Dim nLine
34 For nLine = 0 To UBound( aLines )
35 aLines( nLine ) = Right( sLnPfx & (nLine + 1), nWidth ) &" " & aLines( nLine )
36 Next
37 sFSpec = sFSpec & ".ln"
38 goFS.CreateTextFile( sFSpec, True ).Write Join( aLines, vbCrLf )
39
40 lineNumberFile = nErrCode
41 End Function

(2) Call it by editing the ''# Dispatch section:

10 ''# Dispatch
11 WScript.Quit lineNumberFile()
12 WScript.Quit doMain()

This is the most pedestrian/low tech way to give a program a multiple personality.
If you like the idea of schizo scripts, say so, and I will discuss more elaborate
strategies to create them.

Back to the convert problem. We need some data to work with. I copied thesample
record *twice* into a text file (.\data\short.txt):

01
412||7|2|S||'http://|||http://||1268|http://||!6469616e6e65...dhenice—)
02
03 *a gorgeous, and happy-go-lucky girl
04
05 *a spoiled bratt
06
07 *loves to go from place to place
08
09 *also loves shopping and partying
10
11 *sensitive
12
13 *often times moody
14
15 *loves to play the guitar and sing
16
17 *easy to be with
18
19 *loving
20
21 *whaatt eeelssee?....
22
23 *well, let’s just all find out!
24 ||
25 *dianne bernice c.cristobal*
26
27 --only child--
28
29 .born on the 7th of april 1992 at the metropolitan hospital.
30
31 --my mom is a single parent--
32
33 .my hometown is pulilan,bulacan.
34
35 --nothing more to say--
36
37 .you'll know me sooner or later.|N|Y|1|0|2008-06-19 10:33:25.310000000|2008-10-09
18:41:10.917000000|1|1|2008-10-09 18:41:10.857000000|
38
413||7|2|S||'http://|||http://||1268|http://||!6469616e6e65...dhenice—)
39
40 *a gorgeous, and happy-go-lucky girl
41
42 *a spoiled bratt
43
44 *loves to go from place to place
45
46 *also loves shopping and partying
47
48 *sensitive
49
50 *often times moody
51
52 *loves to play the guitar and sing
53
54 *easy to be with
55
56 *loving
57
58 *whaatt eeelssee?....
59
60 *well, let’s just all find out!
61 ||
62 *dianne bernice c.cristobal*
63
64 --only child--
65
66 .born on the 7th of april 1992 at the metropolitan hospital.
67
68 --my mom is a single parent--
69
70 .my hometown is pulilan,bulacan.
71
72 --nothing more to say--
73
74 .you'll know me sooner or later.|N|Y|1|0|2008-06-19 10:33:25.310000000|2008-10-09
18:41:10.917000000|1|1|2008-10-09 18:41:10.857000000|
75

(sorry about the length; I hope the line numbers will compensate the linewrap)

To get line numbers for short.txt, I had to edit the

29 Dim sFSpec : sFSpec = ".\sigheart.vbs"
==>
29 Dim sFSpec : sFSpec = ".\short.txt"

line. Cumbersome! This is an example for working code that was good enough for
solving the initial task - line numbering sigheart.vbs - but proved second best
for further use. Here it's evident, that we need a strategy for using command
line arguments. Let's push that on the ToDo stack.

Now let's see if we can loop over the lines and detect records. Based on my
published plan:

Loop over the 300,000 lines of your input file
If a line starts a new record
If you have a collection of ca 29 data items
join them with "|" and write the line to output
start collecting non blank lines into a Dim aParts( 28 ) array
Else
If blank line
ignore (or prepare for adding space to previous/next? item
Else
collect
join and write last collection

I add

10 ''# Dispatch
11 WScript.Quit loopRecords()
12 WScript.Quit lineNumberFile()

And

45 ''= loopRecords - loop thru the file, detect records
46 Function loopRecords()
47 Dim nErrCode : nErrCode = 0 ' assume no error
48 WScript.Echo WScript.ScriptName & "::loopRecords()"
49
50 Dim sFSpec : sFSpec = ".\data\short.txt"
51 Dim oTS : Set oTS = goFS.OpenTextFile( sFSpec )
52 Do Until oTS.AtEndOfStream
53 Dim sLine : sLine = oTS.ReadLine()
54 Loop
55 WScript.Echo "Lines processed:", oTS.Line
56 oTS.Close
57
58 loopRecords = nErrCode
59 End Function

I get:

C:\wis\_vbs\0506\dev\forum\Sigheart
cscript sigheart.vbs
sigheart.vbs::loopRecords()
Lines processed: 75

Looks good, but needs further work:

45 ''= loopRecords - loop thru the file, detect records
46 Function loopRecords()
47 Dim nErrCode : nErrCode = 0 ' assume no error
48 WScript.Echo WScript.ScriptName & "::loopRecords()"
49
50 Dim sFSpec : sFSpec = ".\data\short.txt"
51 Dim oTS : Set oTS = goFS.OpenTextFile( sFSpec )
52 Dim reRStart : Set reRStart = New RegExp
53 reRStart.Global = False ' (redundant: False is default; we look at one
line)
54 reRStart.IgnoreCase = False ' (redundant: False is default; wee look for
digits and |)
55 reRStart.Multiline = False ' (redundant: False is default; we look at one
line)
56 reRStart.Pattern = "^(\d+)\|" ' line starts with sequenceof digits and
(literal) |
57 Dim aCntS : aCntS = Array( "Lines processed:", " Records:" )
58 Dim aCntN : aCntN = Array( 0, 0 )
59 Do Until oTS.AtEndOfStream
60 aCntN( 0 ) = aCntN( 0 ) + 1
61 Dim sLine : sLine = oTS.ReadLine()
62 Dim oMTS : Set oMTS = reRStart.Execute( sLine )
63 Select Case True
64 Case "" = Trim( sLine )
65 Case 1 = oMTS.Count
66 WScript.Echo oTS.Line, "Record", oMTS( 0 ).SubMatches( 0)
67 aCntN( 1 ) = aCntN( 1 ) + 1
68 Case Else
69 End Select
70 Loop
71 WScript.Echo "Lines processed:", oTS.Line
72 oTS.Close
73 Dim nIdx
74 For nIdx = 0 To UBound( aCntS )
75 WScript.Echo aCntS( nIdx ), aCntN( nIdx )
76 Next
77 loopRecords = nErrCode
78 End Function

output:

C:\wis\_vbs\0506\dev\forum\Sigheart
cscript sigheart.vbs
sigheart.vbs::loopRecords()
2 Record 412
39 Record 413
Lines processed: 75
Lines processed: 74
Records: 2

How about collecting the data for each record: Starting with

081 ''= loopCollect - loop thru the file, detect records, and collect their data
082 Function loopCollect()
083 Dim nErrCode : nErrCode = 0 ' assume no error
084 WScript.Echo WScript.ScriptName & "::loopCollect()"
085
086 Dim sFSpec : sFSpec = ".\data\short.txt"
087 Dim oTS : Set oTS = goFS.OpenTextFile( sFSpec )
088 Dim reRStart : Set reRStart = New RegExp
089 reRStart.Pattern = "^(\d+)\|" ' line starts with sequence of digits and
(literal) |
090 Do Until oTS.AtEndOfStream
091 Dim sLine : sLine = oTS.ReadLine()
092 Dim oMTS : Set oMTS = reRStart.Execute( sLine )
093 Select Case True
094 Case "" = Trim( sLine )
095 Case 1 = oMTS.Count
096 WScript.Echo oTS.Line, "Record", oMTS( 0 ).SubMatches( 0 )
097 Case Else
098 End Select
099 Loop
100 oTS.Close
101 loopCollect = nErrCode
102 End Function

I provide for an array to put the line in:

081 ''= loopCollect - loop thru the file, detect records, and collect their data
082 Function loopCollect()
083 Dim nErrCode : nErrCode = 0 ' assume no error
084 WScript.Echo WScript.ScriptName & "::loopCollect()"
085
086 Dim sFSpec : sFSpec = ".\data\short.txt"
087 Dim oTS : Set oTS = goFS.OpenTextFile( sFSpec )
088 Dim reRStart : Set reRStart = New RegExp
089 reRStart.Pattern = "^(\d+)\|" ' line starts with sequence of digits and
(literal) |
090 ReDim aLines( 100 ) ' <-- speculative
091 Dim nLine : nLine = -1
092 Do Until oTS.AtEndOfStream
093 Dim sLine : sLine = oTS.ReadLine()
094 Dim oMTS : Set oMTS = reRStart.Execute( sLine )
095 Select Case True
096 Case "" = Trim( sLine )
097 Case 1 = oMTS.Count
098 If -1 < nLine Then
099 processRecord aLines, nLine
100 ReDim aLines( 100 ) ' <-- speculative
101 nLine = -1
102 End If
103 WScript.Echo oTS.Line, "Record", oMTS( 0 ).SubMatches( 0 )
104 nLine = nLine + 1
105 aLines( nLine ) = sLine
106 Case Else
107 nLine = nLine + 1
108 aLines( nLine ) = sLine
109 End Select
110 Loop
111 oTS.Close
112 If -1 < nLine Then
113 processRecord aLines, nLine
114 Else
115 WScript.Echo "Surprise, no last record"
116 End If
117 loopCollect = nErrCode
118 End Function

and a Sub to display the resulting fields:

120 ''- processRecord - process record data contined in nLine(s) of line array aLines
121 Sub processRecord( aLines, nLine )
122 ReDim Preserve aLines( nLine )
123 Dim sLine : sLine = Join( aLines, "" )
124 Dim aFields : aFields = Split( sLine, "|" )
125 WScript.Echo "Fields", UBound( aFields ) + 1
126 Dim nField
127 For nField = 0 To UBound( aFields )
128 WScript.Echo Right( " " & nField, 3 ) & ">" & aFields( nField ) & "<"
129 Next
130 WScript.Echo "---------------"
131 End Sub

output:

01 sigheart.vbs::loopCollect()
02 2 Record 412
03 Fields 28
04 0>412<
05 1><
06 2>7<
07 3>2<
08 4>S<
09 5><
10 6>'http://<
11 7><
12 8><
13 9>http://<
14 10><
15 11>1268<
16 12>http://<
17 13><
18 14>!6469616e6e65206267.jpg<
19
15>--dhenice—'(http://%7c%7c%7chttp//%7C%7C1268%7Ch...nice—)*a
gorgeous, and happy-go-lucky girl*a spoiled bratt*loves to go from place to place*also
loves shopping and partying*sensitive*often times moody*loves to play theguitar and
sing*easy to be with*loving*whaatt eeelssee?....*well, let’s just all find out!<
20 16><
21 17>*dianne bernice c.cristobal*--only child--.born on the 7th ofapril 1992 at
the metropolitan hospital.--my mom is a single parent--.my hometown is
pulilan,bulacan.--nothing more to say--.you'll know me sooner or later.<
22 18>N<
23 19>Y<
24 20>1<
25 21>0<
26 22>2008-06-19 10:33:25.310000000<
27 23>2008-10-09 18:41:10.917000000<
28 24>1<
29 25>1<
30 26>2008-10-09 18:41:10.857000000<
31 27><
32 ---------------
33 39 Record 413
34 Fields 28
35 0>413<
36 1><
37 2>7<
38 3>2<
39 4>S<
40 5><
41 6>'http://<
42 7><
43 8><
44 9>http://<
45 10><
46 11>1268<
47 12>http://<
48 13><
49 14>!6469616e6e65206267.jpg<
50
15>--dhenice—'(http://%7c%7c%7chttp//%7C%7C1268%7Ch...nice—)*a
gorgeous, and happy-go-lucky girl*a spoiled bratt*loves to go from place to place*also
loves shopping and partying*sensitive*often times moody*loves to play theguitar and
sing*easy to be with*loving*whaatt eeelssee?....*well, let’s just all find out!<
51 16><
52 17>*dianne bernice c.cristobal*--only child--.born on the 7th ofapril 1992 at
the metropolitan hospital.--my mom is a single parent--.my hometown is
pulilan,bulacan.--nothing more to say--.you'll know me sooner or later.<
53 18>N<
54 19>Y<
55 20>1<
56 21>0<
57 22>2008-06-19 10:33:25.310000000<
58 23>2008-10-09 18:41:10.917000000<
59 24>1<
60 25>1<
61 26>2008-10-09 18:41:10.857000000<
62 27><
63 ---------------
64

I hope that now we have a base to discuss details like the format and themapping
of the fields and a good example for how to start a VBScript project. To finish it,
a short subset of the 300,000 lines file posted by Sigheart would be helpful.

I attach my current version of the .vbs

Paul Randall schrieb:I don't think that a few word wraps in the message's text or a short
code sample cause a problem. But data to be parsed or long scripts should
be attached, because special characters, tabs, or trailings blanks do
often lead to misunderstandings, errors, and wasted efforts.