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

[bdmeyer]

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.

[mschleich · Accepted solution]

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

[bdmeyer]

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