Log Parser 2.2: data type is not compatible with SELECT clause

Hi all,

I have a problem with Log Parser 2.2 and CSV files I hope you can help me with. When run log parser against my CSV files, I get the following error:

Task aborted.
SQL table column "Originator" data type is not compatible with SELECT clause item "ORIGINATOR" (type INTEGER)

Now the SQL table column called "Originator" is set up as a varchar, because the CSV column called "ORIGINATOR" could contain either numbers or letters (though most often numbers). Log parser thinks that this column is an integer because the first log it analyses only contains numbers, but subsequent logs will contain both numbers and letters.

Therefore, if I change my SQL column data type to integer, subsequent logs will fail saying the data type is wrong again!

Surely there must be a way with Log Parser to tell it to simply take the CSV columns and ignore their data type and just insert their values into the SQL table?

Thanks

Jon
LVL 11
Jon WinterburnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ted BouskillSenior Software DeveloperCommented:
LogParser uses the Microsoft Text driver to parse the files.  You can control the behavior of the driver using a schema.ini in the same folder as the file source (the log folder)

http://msdn2.microsoft.com/en-us/library/ms709353.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jon WinterburnAuthor Commented:
Thanks very much for this link and for your help. I've tried to use the Schema.ini but it doesn't seem to work. I'm probably missing something really basic, so please tell me if I am.

I get the error "Error: SELECT clause: Syntax Error: unknown field 'ORIG'"

What I have is this:

1. The CSV file has no headers for the columns
2. My Schema.ini is in the logs folder
3. My batch file that executes log parser is in the folder above the logs folder

My Schema.ini is as follows:
Format=CSVDelimited
Col1=NOTREQ1
Col2=ORIG
Col3=DEST
Col4=DESTCONTEXT
Col5=CLID
Col6=CHANNEL
Col7=DESTCHANNEL
Col8=LASTAPP
Col9=LASTAPPDATA
Col10=STARTOFCALL
Col11=ANSWERTIME
Col12=ENDTIME
Col13=DURATION
Col14=NOTREQ2
Col15=BILLSECONDS
Col16=DISPOSITION
Col17=NOTREQ3

My batch file is in the code snippet below.
cd "C:\Program Files\log parser 2.2\"
LogParser -i:CSV -headerRow:off "SELECT 1,ORIG,DEST,DESTCONTEXT,CLID,STARTOFCALL,ANSWERTIME,ENDTIME,DURATION,BILLSECONDS,DISPOSITION INTO Call_Logs FROM D:\LogFiles\Asterisk\Call_Logs\*.csv" -o:SQL -server:mymssql -driver:"SQL Server" -database:AsteriskData -username:myuser -password:mypassword -createtable:ON

Open in new window

0
Ted BouskillSenior Software DeveloperCommented:
I think you have to be in the current folder when you run the EXE
cd "D:\LogFiles\Asterisk\Call_Logs\"
C:\Program Files\log parser 2.2\LogParser -i:CSV -headerRow:off "SELECT 1,ORIG,DEST,DESTCONTEXT,CLID,STARTOFCALL,ANSWERTIME,ENDTIME,DURATION,BILLSECONDS,DISPOSITION INTO Call_Logs FROM *.csv" -o:SQL -server:mymssql -driver:"SQL Server" -database:AsteriskData -username:myuser -password:mypassword -createtable:ON
 

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

LowfatspreadCommented:
could it be that you haven't named the first table column ?

"SELECT 1 as [id],ORIG,DEST   ...

do you  want to maje that

"SELECT "1" as [id],ORIG,DEST,...

it would be better practice to  explicitly create the permanent table call_logs prior to running the statement (or truncate it prior to each run)

and then use Insert into (column list ) select column list ....

   
0
Jon WinterburnAuthor Commented:
Tedbilly - I have amended it as you said, and even put everything (schema.ini and .bat file) into the same folder as the logs, but still I get the error "SELECT clause: Syntax Error: unknown field 'ORIG'".

I get the same if I do the "as [id]" - this is not necessary anyway, as by entering "select 1," tells the log parser to tell the unique ID column to increment by one (I have implemented this in other log parser scripts which do work).

So now my script is as below in the snippet.
cd "D:\LogFiles\Asterisk\Call_Logs\"
"C:\Program Files\log parser 2.2\LogParser" -i:CSV -headerRow:off "SELECT 1,ORIG,DEST,DESTCONTEXT,CLID,STARTOFCALL,ANSWERTIME,ENDTIME,DURATION,BILLSECONDS,DISPOSITION INTO Call_Logs FROM *.csv" -o:SQL -server:mymssql -driver:"SQL Server" -database:AsteriskData -username:myuser -password:mypass -createtable:ON

Open in new window

0
Jon WinterburnAuthor Commented:
Well, I got fed up of messing around with this and decided it was easier to simply spend 10 minutes adding the column names to all the CSV files. Once I did this, I could then run log parser and it all worked fine. Thanks for all your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.