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
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
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>
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.
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.
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.
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
Assuming I understand you could do something like:
txt = FileGet(file_name)
txt = StrReplace(txt," - "," _. ")
FilePut(file_name,txt)
Jim
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.
data = FileGet(filename)
newdata = StrReplace(data," - ",",")
FilePut(filename,newdata)
How can i change only the first word of a sentence.
pm afther each word there is a space.
That is my problem.
Take a look at ItemReplace() and ItemExtract().
Jim
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:
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)
Is this also the same for a array
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.
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)
The code would look something like this:
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
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)
Give this a whirl:
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)
thx, that was the trick.