Author Topic: import  (Read 7255 times)

pennym

  • Newbie
  • *
  • Posts: 26
import
« on: November 03, 2013, 01:50:22 am »
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

  • Newbie
  • *
  • Posts: 26
Re: import
« Reply #1 on: November 03, 2013, 02:46:44 am »
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

  • Newbie
  • *
  • Posts: 26
Re: import
« Reply #2 on: November 03, 2013, 03:25:45 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>


stanl

  • Pundit
  • *****
  • Posts: 1171
Re: import
« Reply #3 on: November 03, 2013, 07:26:34 am »
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

  • Newbie
  • *
  • Posts: 26
Re: import
« Reply #4 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.

stanl

  • Pundit
  • *****
  • Posts: 1171
Re: import
« Reply #5 on: November 03, 2013, 11:19:27 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

  • Newbie
  • *
  • Posts: 26
Re: import
« Reply #6 on: November 03, 2013, 12:28:33 pm »
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

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: import
« Reply #7 on: November 04, 2013, 06:50:58 am »
Assuming I understand you could do something like:

Code: Winbatch
txt = FileGet(file_name)
txt = StrReplace(txt," - "," _. ")
FilePut(file_name,txt)
 
Jim

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: import
« Reply #8 on: November 04, 2013, 08:42:29 am »
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
data = FileGet(filename)
newdata = StrReplace(data," - ",",")
FilePut(filename,newdata)
Deana F.
Technical Support
Wilson WindowWare Inc.

pennym

  • Newbie
  • *
  • Posts: 26
Re: import
« Reply #9 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.

JTaylor

  • Pundit
  • *****
  • Posts: 1362
    • Data & Stuff Inc.
Re: import
« Reply #10 on: November 05, 2013, 01:03:10 pm »
Take a look at ItemReplace() and ItemExtract().

Jim

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: import
« Reply #11 on: November 05, 2013, 01:06:37 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
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

  • Newbie
  • *
  • Posts: 26
Re: import
« Reply #12 on: November 06, 2013, 05:13:35 am »
Is this also the same for a array

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: import
« Reply #13 on: November 06, 2013, 08:24: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

  • Newbie
  • *
  • Posts: 26
Re: import
« Reply #14 on: November 06, 2013, 08:27:40 am »
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

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: import
« Reply #15 on: November 06, 2013, 08:51:45 am »
The code would look something like this:

Code: Winbatch
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

  • Newbie
  • *
  • Posts: 26
Re: import
« Reply #16 on: November 06, 2013, 11:49:30 am »
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

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: import
« Reply #17 on: November 06, 2013, 01:31:42 pm »
Give this a whirl:

Code: Winbatch
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

  • Newbie
  • *
  • Posts: 26
Re: import
« Reply #18 on: November 07, 2013, 12:16:19 am »
thx, that was the trick.