Search results matching tag 'CSV and TSV input formats'

Apostrophe Excape Need in Folder Path - Using Log Parser as an object in vbscript

Ed Grossheim — Sun, 01 Feb 2009 02:09:40 GMT

Hi,

I am running logparser, in a vbs script using the logparser object.

The vbs file that runs logparser is below. It places all the files of a folder (recursing) and places them in a text file.
=============================================
Dim objLogParser, objInputFormat, objOutputFormat, strQuery
Set objLogParser = CreateObject("MSUtil.LogQuery")
Set objInputFormat = CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
Set objOutputFormat = CreateObject("MSUtil.LogQuery.TSVOutputFormat")

strQuery = "SELECT Name, Size INTO 'C:\Ed's Scripts\Output2.txt' FROM 'C:\Tom's Scripts\Before*.*' ORDER BY Name ASC"
objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat

Apparently Log Parser cannot handle the apostrophe.

=============================================
How can I escape any apostrophes in my strQuery?

(Unfortunately my computer is loaded with folder names with apostrophes I am using XP, and this is the first brick wall I have hit on this subject.)

In the help file, it says you should be able to escape the apostrophe.

Like (?):
strQuery = "SELECT Name, Size INTO 'C:\Ed\'s Scripts\Output2.txt' FROM 'C:\Tom\'s Scripts\Before*.*' ORDER BY Name ASC"

But LogParser does not understand that the backslash is "escaping the apostrophe".

Am I escaping incorrectly?

I have tried escaping with unicode (U0027) also.

I have tried everything I can think of. Any help would be greatly appreciated!!!!!!!

Other than this big obstacle, I am thrilled with the potential of log parser for me !!!!!

Thanks, Ed

Query issues with Null field value in TSV format...

yesubabu — Thu, 25 Sep 2008 07:48:39 GMT

Hello:

I have an issue in querying TSV format data files with some fields having nulls (no-data). The issue here is that logparser is considering the subsequent field value for the current field and messing up the data format.

Please verify the query, sampledata and output pasted below to see the problem. Can anyone help with the workaround or fix to this issue?

Appreciate your help!

Regards,
Yesu

Query: logparser -i:tsv -o:tsv -q:on "select a,b,c,d from c:\sampledata.txt"

SampleData:

A	B	C	D
1	2	3	4
11		13	14
21	22	23	24
31			34
41	42	43	44

Output:

A	B	C	D
1	2	3	4
11	13	14
21	22	23	24
31	34
41	42	43	44

Re: input format help needed

yellowdog.dave — Mon, 21 Jul 2008 07:10:59 GMT

Hi There,

I am not familiar with HL7 logs, but if they are just a text file with | delimited data, then you can use logparser to pump it straight into your Access database. You can also let logparser determine your data types from the data, so that you will not need to do a data import into Access. Look at the"TSV input Format Parameters" in the logparser.chm help file to get the specifics for the dtLines (data type lines). There are also numerous other parameters which I find useful when parsing non-standard type data. The header line is also very useful to give your elements meaningful names when dealing with each field. The default values of these parameters are generally good to go.

The following example will pump | delimited data straight into and Access database table.

LogParser -i:tsv -iseparator:"|" "select * INTO MyTable FROM sample.hl7" -o:SQL -oConnString:"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\MyDB\MyDB.mdb;Uid=MyUsername;Pwd=MyPassword"

A quick observation for you; why not use logparser to directly analyse the HL7 data? When you get more familiar and start seeing the speed of the processing and start looking into the advanced features in the product, you will find the file checkpointing, multiplexing files, charting of output, creation of web pages for reporting etc etc very useful. This literally obviates the intermediate step of having to transform data from one format to another.

Good luck and enjoy.

Cheers, Dave

zipped csv files as input

juergmaier — Thu, 19 Jun 2008 10:53:33 GMT

Any plugin available that will allow to parse zipped files?

Jürg

Cisco Pix logs

altair4 — Wed, 04 Jun 2008 19:43:16 GMT

The goal here is to craft a query whereby an IP address is suppled, and the result returned is a list of all urls accessed. The syslog file is a text file, tab-delimited, in the following format:

2008-05-21 08:49:58    Local6.Notice    10.13.30.2    %PIX-5-304001: 10.17.90.10 Accessed URL 206.190.50.59:/serv?s=150550015&dm=&t=0.9166543242483984&t_e=1
2008-05-21 08:49:59    Local6.Notice    10.13.30.2    %PIX-5-304001: 10.17.40.58 Accessed URL 65.175.87.137:/a/hBINA-xAdIUrHB7REIJBNCvLK.AdIUrHSd/spacer-1.gif
2008-05-21 08:49:59    Local6.Info    10.13.30.2    %PIX-6-302014: Teardown TCP connection 122281117 for outside:65.175.87.137/80 to inside:10.17.40.58/2385 duration 0:00:01 bytes 804 TCP FINs
2008-05-21 08:50:00    Local6.Info    10.13.30.2    %PIX-6-305011: Built dynamic TCP translation from inside:10.17.50.70/1614 to outside:216.185.22.100/47811
2008-05-21 08:50:00    Local6.Info    10.13.30.2    %PIX-6-302013: Built outbound TCP connection 122281118 for outside:206.16.21.33/80 (206.16.21.33/80) to inside:10.17.50.70/1614 (216.185.22.100/47811)
2008-05-21 08:50:00    Local6.Info    10.13.30.2    %PIX-6-305011: Built dynamic TCP translation from inside:10.17.50.70/1615 to outside:216.185.22.100/47812

The query is something along the lines of :

Logparser -i:tsv "Select * FROM file\path WHERE Field4 Like "inside:10.17.90.78" -iSeparator:tab -headerRow:off

Field4 returns the "%" sign and everything to the right of it. I guess what I need is to tokenize the string and search for IP's but I am not sure how to go about that.

Any ideas greatly appreciated.

comma as decimal point in number

JosefVr — Thu, 08 May 2008 09:57:17 GMT

Does logparser support localized numbers (decimal point is comma) in csv file? If so, is there anything special needs to be done to get this going?

Thanks!

RE: textfile to textfile - need help

LogParser User : Jeff — Fri, 15 Jun 2007 15:45:00 GMT

Graham is correct, out of the box Log Parser won't do what you want. That said, the format looks pretty simple. If you can write a little bit of VBScript (or JScript), you could make a custom input parser (which would allow you to take advantage of the rest of Log Parser's features.

RE: textfile to textfile - need help

LogParser User : gpl — Fri, 15 Jun 2007 07:45:00 GMT

Unfortunately, Logparser cannot really deal with multine type records; your best bet is to use a short vbscript to reformat it

Graham

textfile to textfile - need help

LogParser User : clavis — Fri, 15 Jun 2007 06:00:00 GMT

I am beginner - hope anyone can advise me. Have following textfile

!Subject,!From,!Date,!Lines,!Body
Subject:aaa
From:bbb
Date:ccc
Lines:ddd
Body:eee
......body
lines.....
!Subject,!From,!Date,!Lines,!Body
Subject:fff
From:ggg
Date:hhh
Lines:iii
Body:jjj
......body
lines.....

and want new textfile (ready to import into MS-Access):

"Subject","From","Date","Lines","Body"
"aaa","bbb","ccc","ddd","eee","......bodylines....."CR/LF
"fff","ggg","hhh","iii","jjj","......bodylines....."CR/LF

Possible in LogParser ?

In advance thank you

Time between TimeStamp & System_Time

LogParser User : Glassdrum — Wed, 06 Jun 2007 01:05:00 GMT

Hello All,
I have a question about how to calculate the time elapsed between the log file timestamp and the current system time. I have a log file in the format shown below and the StartTime is the time in which a software license was checked out.

BXW01 McWhorter-B01 McWhorter-B01 (v1.000) (CDCLM1/27001 2701), start Fri 6/1 10:52
JMR01 ROSETTI-J01 ROSETTI-J01 (v1.000) (CDCLM1/27001 1301), start Fri 6/1 10:52
JRS01 Sikes-JR01 Sikes-JR01 (v1.000) (CDCLM1/27001 3404), start Fri 6/1 10:56
SAE01 Prech-m204 Prech-m204 (v1.000) (CDCLM1/27001 4409), start Fri 6/1 16:22
TLB03 BAUGH-TIM-01 BAUGH-TIM-01 (v1.000) (CDCLM1/27001 3801), start Fri 6/1 11:04
TXC05 Chea-Tony Chea-Tony (v1.000) (CDCLM1/27001 2608), start Fri 6/1 16:34
axb03 BALDWIN-A01 BALDWIN-A01 (v1.000) (CDCLM1/27001 5303), start Fri 6/1 16:34
bws02 40SMITH-BRIANW 40SMITH-BRIANW (v1.000) (CDCLM1/27001 5201), start Fri 6/1 11:32
cgv01 VIER-C01 VIER-C01 (v1.000) (CDCLM1/27001 1402), start Fri 6/1 16:02
cocotp COCOTP023 COCOTP023 (v1.000) (CDCLM1/27001 5710), start Fri 6/1 12:19

I would like to know how long each user has used a particular license. Therefore, I would like to have LP return the below format but also ADD a column for the calculated time elapsed between the StartTime and the System Time in xx(days) xx(hours) xx(min). I can get the results shown below. Now I am working to calculate and output the elapsed time only and am stuck.

User Machinename Version StartTime
BXW01 McWhorter-B01 (v1.000) 6/5 10:52
JMR01 ROSETTI-J01 (v1.000) 6/5 10:52
JRS01 Sikes-JR01 (v1.000) 6/5 10:56
SAE01 Prech-m204 (v1.000) 6/5 16:22
TLB03 BAUGH-TIM-01 (v1.000) 6/5 11:04
TXC05 Chea-Tony (v1.000) 6/5 16:34
axb03 BALDWIN-A01 (v1.000) 6/5 16:34
bws02 40SMITH-BRIANW (v1.000) 6/5 11:32
cgv01 VIER-C01 (v1.000) 6/5 16:02
cocotp COCOTP023 (v1.000) 6/5 12:19

I have tried the command:

LogParser -i:TSV -iSeparator:space -iTsFormat:"M/d h:mm" "SELECT SUB(TIMESTAMP('StartTime', 'M/d h:mm'), SYSTEM_TIMESTAMP()) INTO users.tmp FROM users.log"

and I get in return Error: Syntax Error: (value): Invalid month in timestamp "StartTime"

After scouring the KB and Forums I haven't found anything like what I'm trying to do. Do I need to convert the Timestamp to an Integer then calculate the difference?

Any help is much appreciated.