import

[pennym]

Hi,
I want to import this file to msaccess but the problems is that i use minus - as field delimiter.

base-system               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST
basesystem               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST
libtermcap               - 2.0.8     - 46.1 - Fri 04 May 2012 12:34:01 PM CEST
tcp-wrappers             - 7.6       - 40.7.el5 - Fri 04 May 2012 12:34:06 PM CEST
make                     - 3.81      - 3.el5 - Fri 04 May 2012 12:34:09 PM CEST
pcsc-lite-libs           - 1.4.4     - 4.el5_5 - Fri 04 May 2012 12:34:11 PM CEST

The problems is that the word base-system contains a minus  -  the program this it is a record.
How can i skip words with minus for import.

The import to access is no problem bit how can i skip words with a minus


part of the code

add record
        rs.addnew
     ;special voor access
      ;accessdata= StrReplace(data,@CRLF,"")
      rs.fields("RCM").value =  num
      ;rs.fields("Servername").value =  sss
      ;rs.fields("Commando").value = record2

      record1 = StrReplace(record1,@LF,@CRLF)
      sss=strcat("  ","- ",sss,"  "," ",@CRLF)
      record1 = StrReplace(record1,@CRLF,sss)
      
   
      rs.fields("RAW Result").value = record1
      rs.fields("Result").value = Result

perry

[pennym]

part of the import code

s.Open(sqlstr, adoConn, 1,4,1)

counter=1
   
; Parse out floating point numbers
arrArray = ArrayFileGet(inputfile)
For strItem = 0 to ArrInfo(arrArray,1)-1
;debug(@on)
   num = 0
   str =  arrArray[strItem]
   if StrSub( str, 1, 2 ) == "</" then Continue
   if StrSub( str, 1, 1 ) == "<"
      num = StrSub( str, 2, StrLen(str)-2 )
      if IsFloat(num)
         ;Pause(0,num)
         ; Parse out chunk of data based on floating point number
         startstr = '<':num:'>':@LF
         startptr = StrIndex( data, startstr, 1, @Fwdscan )+StrLen(startstr)
         endstr = '</':num:'>':@LF
      
         BoxOpen("Processing",".." )
         record0=num
         record = StrSub( data, startptr, endptr-startptr )
         
         
         ;accessdata= StrReplace(data,@CRLF,"")
        rs.fields("RCM").value =  num
      record1 = StrReplace(record1,@LF,@CRLF)
      record1 = StrReplace(record1,@CRLF,sss)
      
      rs.fields("RAW Result").value = record1
      ;rs.fields("Result").value = Result


    ;sluit record af
       rs.updateBatch
          
         
      :geen
      endif
   endif   
Next
Next
exit

[pennym]

file format

<1.05>
basesystem               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST
li-bter-mcap               - 2.0.8     - 46.1 - Fri 04 May 2012 12:34:01 PM CEST
tcp-wrappers             - 7.6       - 40.7.el5 - Fri 04 May 2012 12:34:06 PM CEST
basesystem               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST
</1.05>

[stanl]

I may be confused, but it appears you are using ADO to open a text file ( s.Open ) then filling an Access table (rs.fields).

You don't make it very clear, but if - is a delimiter, are you breaking down each line in the text file to 4 fields?????

If so you may wish to open the text file with File I/O, treat each line as a list, delimited by -, then use StrTrim() on each element in the last to post to the access table.

If not, maybe post a sample of the text file and the field layout in access.

[pennym]

file format

<1.05>
basesystem               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST
li-bter-mcap               - 2.0.8     - 46.1 - Fri 04 May 2012 12:34:01 PM CEST
tcp-wrappers             - 7.6       - 40.7.el5 - Fri 04 May 2012 12:34:06 PM CEST
basesystem               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST
</1.05>

in access this is the raw_result record.

[stanl]

file format

<1.05>
basesystem               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST
li-bter-mcap               - 2.0.8     - 46.1 - Fri 04 May 2012 12:34:01 PM CEST
tcp-wrappers             - 7.6       - 40.7.el5 - Fri 04 May 2012 12:34:06 PM CEST
basesystem               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST
</1.05>

in access this is the raw_result record.

Now more confused. Your original post said 'field delimiters' - so what exactly are the access fields or does what you posted (above) equal a single access field that you call a record?

My original assumption was that "li-bter-mcap" and the other lead values were field names and the other parts of each line represented attributes of the field name. Then the data could be exported as XML.

Probably why I asked for the detail I did. Good luck.               

[pennym]

I import the file in msaccess and that is no problems but, sometimes i need to export them to excel.
105 want to export to excel, but normal there are no minus in the name.

The only thing wat i want is to filter out or replace the minus with a _.  before import it to access.

perr

[JTaylor]

Assuming I understand you could do something like:

Code (winbatch) Select Expand

txt = FileGet(file_name)
txt = StrReplace(txt," - "," _. ")
FilePut(file_name,txt)

Jim

[Deana]

I recommend converting the delimiter to a value that is not used in the field text. Based on the text file input you shared it appears the delimiter can be identified by {space}-{space}. How about reading in the text file the using StrReplace to Replace {space}-{space} with your unique delimiter.

Code (winbatch) Select Expand

data = FileGet(filename)
newdata = StrReplace(data," - ",",")
FilePut(filename,newdata)

[pennym]

How can i change only the first word of a sentence.
pm afther each word there is a space.

That is my problem.

[JTaylor]

Take a look at ItemReplace() and ItemExtract().

Jim

[Deana]

How can i change only the first word of a sentence.
pm afther each word there is a space.

That is my problem.

Here is a code sample that shows you how to obtain the first word of a sentence and modify it:

Code (winbatch) Select Expand

read_hdl = FileOpen( infilename, "Read")
wrt_hdl = FileOpen( outfilename, "Write")
While @TRUE ; Loop till break do us end
   line = FileRead(read_hdl)
   If line == "*EOF*" Then Break
   firstitem = ItemExtract( 1, line, " " )
   newfirstitem = StrReplace( firstitem, "-", "_" )
   newline = StrReplace( line, firstitem, newfirstitem )
   ;Pause('newline',newline)
   FileWrite( outfilename, newline )
EndWhile
FileClose(read_hdl)
FileClose(wrt_hdl)

[pennym]

Is this also the same for a array

[Deana]

Is this also the same for a array

Depends on the format of the data in the array. If the entire record is one array element then yes.

However if each field is in its own array element it is even easier. Simply access the element of the array and do the StrReplace.

[pennym]

part of the program.
How can i change only the first word of a sentence of record1.
I did itemextarct without any result.


arrArray = ArrayFileGet(inputfile)
For strItem = 0 to ArrInfo(arrArray,1)-1
;debug(@on)
   num = 0
   str =  arrArray[strItem]
   if StrSub( str, 1, 2 ) == "</" then Continue
   if StrSub( str, 1, 1 ) == "<"
      num = StrSub( str, 2, StrLen(str)-2 )
      if IsFloat(num)
         ;Pause(0,num)
         ; Parse out chunk of data based on floating point number
         startstr = '<':num:'>':@LF
         startptr = StrIndex( data, startstr, 1, @Fwdscan )+StrLen(startstr)
         endstr = '</':num:'>':@LF
     
         BoxOpen("Processing",".." )
         record0=num
         record = StrSub( data, startptr, endptr-startptr )
         
         
         ;accessdata= StrReplace(data,@CRLF,"")
        rs.fields("RCM").value =  num
      record1 = StrReplace(record1,@LF,@CRLF)
      record1 = StrReplace(record1,@CRLF,sss)

[Deana]

The code would look something like this:

Code (winbatch) Select Expand


inputfile = 'c:\temp\data.txt'
arrArray = ArrayFileGet(inputfile)
For strItem = 0 to ArrInfo(arrArray,1)-1
;debug(@on)
   num = 0
   str =  arrArray[strItem]
   firstitem = ItemExtract( 1, str, " " )
   newfirstitem = StrReplace( firstitem, "-", "_" )
   newstr = StrReplace( str, firstitem, newfirstitem )
   Pause(str,newstr);USED FOR DEBUGGING


[pennym]

sorry,

data (record1) contains more then one line and all the first words in record 1 be be replaced.
sorry for my English.


we are almost there
all data in record 1 is:
base-system               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST
li-bter-mcap               - 2.0.8     - 46.1 - Fri 04 May 2012 12:34:01 PM CEST
tcp-wrappers             - 7.6       - 40.7.el5 - Fri 04 May 2012 12:34:06 PM CEST
base-system               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST


part of code


arrArray = ArrayFileGet(inputfile)
For strItem = 0 to ArrInfo(arrArray,1)-1
;debug(@on)
   num = 0
   str =  arrArray[strItem]
   if StrSub( str, 1, 2 ) == "</" then Continue
   if StrSub( str, 1, 1 ) == "<"
      num = StrSub( str, 2, StrLen(str)-2 )
      if IsFloat(num)
         ;Pause(0,num)
         ; Parse out chunk of data based on floating point number
         startstr = '<':num:'>':@LF
         startptr = StrIndex( data, startstr, 1, @Fwdscan )+StrLen(startstr)
         endstr = '</':num:'>':@LF
     
         BoxOpen("Processing",".." )
         record0=num
         record = StrSub( data, startptr, endptr-startptr )
         
         
         ;accessdata= StrReplace(data,@CRLF,"")
        rs.fields("RCM").value =  num
      record1 = StrReplace(record1,@LF,@CRLF)
      record1 = StrReplace(record1,@CRLF,sss)

[Deana]

Give this a whirl:

Code (winbatch) Select Expand

record1 = `base-system               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST`:@CRLF
record1 = record1 : `basesystem               - 8.0       - 5.1.1 - Fri 04 May 2012 12:33:52 PM CEST`:@CRLF
record1 = record1 : `libtermcap               - 2.0.8     - 46.1 - Fri 04 May 2012 12:34:01 PM CEST`:@CRLF
record1 = record1 : `tcp-wrappers             - 7.6       - 40.7.el5 - Fri 04 May 2012 12:34:06 PM CEST`:@CRLF
record1 = record1 : `make                     - 3.81      - 3.el5 - Fri 04 May 2012 12:34:09 PM CEST`:@CRLF
record1 = record1 : `pcsc-lite-libs           - 1.4.4     - 4.el5_5 - Fri 04 May 2012 12:34:11 PM CEST`

;trick: first replace all {space}-{space} with a unique character
newrecord = StrReplace( record1, ' - ', ',' )
;replace remaining dashes (in first word) with underscore
newrecord = StrReplace( newrecord, '-', '_' )
;put {space}-{space} back in remaining text
newrecord = StrReplace( newrecord, ',', ' - ' )
Pause('Result',newrecord)

[pennym]

thx, that was the trick.