Help parsing data sql code needed

[itsmythg]

I am trying to read some vpn logs, I import the date but only get 3 columns because of the limitation of importing into access. I don't want to use excel to parse because I would like to automate the process for reports. I need to parse the information in the 3rd column. I think what i need is a sql query to parse them but not sure and cannot write sql.

IN Comming data (column3) that I need to parse after first import;
[ORIGIN IP] domain\user(VPN Realm)[VPN ROLE] - Login succeeded for xxxxxxxxxx

Fields;
Origin IP, USER&DOMAIN"domain\user", VPN REALM, VPN ROLE, OTHER INFORMATION

 

[SkipVought]

Hi,

"...only get 3 columns because of the limitation of importing into access."

or is it that the DATA is not really tabular?

Some more DETAILED information might be helpful, to demonstrate WHY you can "...only get 3 columns because of the limitation of importing into access"

Skip,

_[red][/red]

 

[itsmythg]

because the data is not tablular so I am importing fixed with, I have tried spaces but breaks data I don't want separated.

 

[SkipVought]

What is the point of importing this kind of data?

What is the business case?

Skip,

_[red][/red]

 

[JoeAtWork]

The only thing I can think of is first import the third column, then open it as recordset and iterate through the rows, doing the parsing as you go along.

If the rows are consistent with what you've sketched out, IP could be identified as what is in between the first set of square brackets, domain/user is before the first set of round brackets, VPN Realm is between the first set of round brackets, etc.

This method highly relies on all rows being consistent and not using the same characters in the data as is used to delimit the fields (example: having round or square brackets as part of the data).

BTW, I agree with Skip, you can't blame Access for not being able to import this automatically. The source file does not follow any normal conventions for CSV files.

 

[SkipVought]

are there sets of patterns?

for instance, sometimes you find a file where certain records are header data and other records are detail data. These usually have a record type identifier at the beginning of the record.

Skip,

_[red][/red]

 

[itsmythg]

I am going to parse in perl, thanks for the help.