February 22, 2012 at 12:07 pm
I have a flat file that was pulled from the wevtutil.exe utility. The flat file looks like this (sorry for the long post):
Event[0]:
Log Name: Application
Source: RPSyncService
Date: 2012-02-22T10:21:52.000
Event ID: 2
Task: N/A
Level: Information
Opcode: Info
Keyword: Classic
User: N/A
User Name: N/A
Computer: 01856SC02.rr.server.us
Description:
For table HostSuccessfulAcks: number of rows received are 6000
For table RPSYNDataHost: number of rows received are 6000
For table RPACKDataHost: number of rows received are 0
Event[1]:
Log Name: Application
Source: MSSQL$SQLEXPRESS
Date: 2012-02-22T10:21:05.000
Event ID: 18454
Task: Logon
Level: Information
Opcode: N/A
Keyword: Audit Success,Classic
User: N/A
User Name: N/A
Computer: 01856SC02.rr.server.us
Description:
Login succeeded for user 'POS_usr'. Connection made using SQL Server authentication. [CLIENT: 10.244.78.127]
Event[2]:
Log Name: Application
Source: MSSQL$SQLEXPRESS
Date: 2012-02-22T10:20:58.000
Event ID: 18454
Task: Logon
Level: Information
Opcode: N/A
Keyword: Audit Success,Classic
User: N/A
User Name: N/A
Computer: 01856SC02.rr.server.us
Description:
Login succeeded for user 'POS_usr'. Connection made using SQL Server authentication.
I'm trying to use Bulk insert to insert each event into its own row. The destination table is only 1 column to hold each event. I either get everything in one row or 1 row for every line. Any ideas?
February 22, 2012 at 12:53 pm
I don't see any way to easily distinguish between the end of a field and the end of a record using this file format. That's going to cause problems with your bulk load.
I see that wevtutil has an option to export in XML format and that BULK INSERT can read from XML files. I've never tried either, so I can't offer any advice, but it seems that this would be a much easier approach.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 22, 2012 at 1:06 pm
Create a format file for the file to load. You can set the field length for each field.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 22, 2012 at 1:40 pm
I have created the .fmt file:
9.0
1
1 SQLCHAR 0 0 "Event" 1 event SQL_Latin1_General_CP1_CI_AS
When i use the file to load the data i get the same result. Its loaded line by line. Where am i supposed to set the length?
February 22, 2012 at 2:25 pm
I'm not sure that you can do this without some more manipulation beforehand or "possibly" in SSIS. BULK INSERT is meant to insert large amounts of data that are in some type of table structure, not to parse out a text file that is not structured data.
Jared
CE - Microsoft
February 22, 2012 at 2:29 pm
Thanks for the replies. Is there no way to use BCP and Bulk Insert to perform this task?
February 22, 2012 at 2:31 pm
dnolan5323 (2/22/2012)
Thanks for the replies. Is there no way to use BCP and Bulk Insert to perform this task?
Not until you create a file that fits a row/column format.
Jared
CE - Microsoft
February 22, 2012 at 6:27 pm
In your FMT file, you need to set up for each field the field type, length. I believe (it's been awhile) that you can tell it which position the start of the field is and the length.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 22, 2012 at 6:34 pm
sjimmo (2/22/2012)
In your FMT file, you need to set up for each field the field type, length. I believe (it's been awhile) that you can tell it which position the start of the field is and the length.
Yes, but a line has to have a terminator, like a CR. In this case each field is separated by a CR and nothing to say when the next "line" is. A format file, as far as I know, cannot handle this.
Jared
CE - Microsoft
February 23, 2012 at 5:46 am
Now I understand. I thought that the line was one long string containing all of the fields.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 23, 2012 at 5:55 am
If there are certain "fixed" things (number of lines in each set that you would consider a "row") you can have someone write something to parse out this file. My company, unfortunately, uses PERL for example. Once you have a properly delimited file, then you can import it. May I ask, where does this file come from and is there a way to generate it in a delimited fashion?
Jared
CE - Microsoft
February 23, 2012 at 6:59 am
Create a table with an IDENTITY column and a single varchar column set to max length of input line
bulk load the data
then select the relevent detail associated to each 'Event' ID
you can then pivot the data or process in any way you wish
If high volume beware of any possible triangular joins which may cause performance issues
Far away is close at hand in the images of elsewhere.
Anon.
February 23, 2012 at 7:57 am
David Burrows (2/23/2012)
Create a table with an IDENTITY column and a single varchar column set to max length of input linebulk load the data
then select the relevent detail associated to each 'Event' ID
you can then pivot the data or process in any way you wish
If high volume beware of any possible triangular joins which may cause performance issues
It doesn't look like that will do any good since the identifiers for the event are on a separate line. Plus, I'm not sure that you can "guarantee" that the data will be loaded in the order of the file. This file really has to be parsed before being loaded.
From http://msdn.microsoft.com/en-us/library/ms190421.aspx:
By default, a bulk-import operation assumes that a data file is unordered. If the table has a clustered index, the bcp utility, BULK INSERT statement, and OPENROWSET(BULK…) function (Transact-SQL) enable you to specify how data in the data file is sorted during a bulk-import operation. It is optional for data in the data file to be sorted in the same order as the table. However, you can improve performance of the bulk-import operation if you specify the same ordering for the data file as the table.
And in this case, what would you make your clustered index?
I would do this the right way... Either export the file in a delimited format if you can, or get a programmer to write something to parse the file.
Jared
CE - Microsoft
February 23, 2012 at 8:34 am
Thanks for all you help guys. I know its not the prettiest way of doing things but i got things to work. I am using 'Event[' as my rowterminator and i am able to load each event in a single row. Of course I'm sure there is a better way to go about it but this will work for now.
February 23, 2012 at 8:35 am
dnolan5323 (2/23/2012)
Thanks for all you help guys. I know its not the prettiest way of doing things but i got things to work. I am using 'Event[' as my rowterminator and i am able to load each event in a single row. Of course I'm sure there is a better way to go about it but this will work for now.
Nice hack! Hey, if it gets you the results you need, I think the solution is great.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply