import

Started by pennym, November 03, 2013, 01:50:22 AM

Previous topic - Next topic

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

Quote from: perrym on November 03, 2013, 09:44:39 AM
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
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
data = FileGet(filename)
newdata = StrReplace(data," - ",",")
FilePut(filename,newdata)
Deana F.
Technical Support
Wilson WindowWare Inc.

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

Quote from: perrym on November 05, 2013, 12:47:14 PM
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
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)
Deana F.
Technical Support
Wilson WindowWare Inc.

pennym

Is this also the same for a array

Deana

Quote from: perrym on November 06, 2013, 05:13:35 AM
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.
Deana F.
Technical Support
Wilson WindowWare Inc.

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

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
Deana F.
Technical Support
Wilson WindowWare Inc.

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
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)

Deana F.
Technical Support
Wilson WindowWare Inc.

pennym

thx, that was the trick.