Commodore
Commodore
809 views

How do I handle ArcSight delimiters for a csv import to MS-SQL?

Jump to solution

I have spent the last full two days on this.

All I am trying to do is import a csv into a new table.

The csv is a simple report with no sorting or anything.

I have 7 fields out of about 60, where my source csv data looks similar to this:

"<Resource URI=""/All Zones/ArcSight System/Public Address Space Zones/APNIC/153.0.0.0-153.255.255.255 (APNIC)"" ID=""M-srb4ycBABCEzJ5Updd27g==""/>"

I have tried using: double quote ("),and a pair of the double quotes (""), and also nothing for the delimiter. I always get a truncation error because MS-SQL isn't handling the series of double quote pairs inside a single field.

I was wondering if anyone else has already run into this and figured out how to deal with it.

Any help guidance, whatever that will help me import this data sanely will be quite appreciated.

Thank-You.

-= Bruce
Labels (1)
Tags (1)
0 Likes
1 Solution

Accepted Solutions
Fleet Admiral
Fleet Admiral

Hi Bruce,

You have to remove the begin and end quote and you have to replace dual quotes by single quote.

The issue is that, for ArcSight ESM, this is considered as a resource reference type and not a string.

You have to change the type of this field in your ActiveList.

I have verified it and it is working properly.

Thanks

Kind regards

Michael

View solution in original post

0 Likes
2 Replies
Fleet Admiral
Fleet Admiral

Hi Bruce,

You have to remove the begin and end quote and you have to replace dual quotes by single quote.

The issue is that, for ArcSight ESM, this is considered as a resource reference type and not a string.

You have to change the type of this field in your ActiveList.

I have verified it and it is working properly.

Thanks

Kind regards

Michael

View solution in original post

0 Likes
Commodore
Commodore

Thank-You Michael.

Sorry for the delay, I was out of town.

Your response allowed me to ultimately figure this out.

I am posting this next bit so if someone else searches this in the future, they'll find it here.

I was trying to import this into an MS-SQL via MS SQL Management Studio using varchar for the field type.

It kept failing with a truncate error.

On an MS-SQL forum, once I posted the nature of the data in this field, I received a reply in this forum thread about halfway down from Nr5952 (username)

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0a08542a-5047-4664-abbd-6d2d6dc55f83/importing-csv-errors?forum=sqlgetstarted

The bottom line is, instead of using varchar, use TextStream (DT_Text) for eachc olumn that contains this type of data, and it should work quite well.

-= Bruce D. Meyer

-= Bruce
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.