How to load data source csv files that have commas inside double quotes causing an error?

Todd O · ‎02-09-2017

I'm exporting data from SQL Server Management Studio to csv files and then importing them to ServiceNow by way of "load data", create a new import set row and data source. Everything is working great except for certain files that contain additional commas. I'm getting an error when creating a new data source indicating that it expected x number columns but found y number of columns. I need to figure out how to resolve this. Here are more details.

1. I know it's being caused by the additional commas that are found inside some of the fields. These fields that contain these, however, are enclosed inside double quotes which is valid csv. I'm baffled why SN does not allow this.

2. I can temporarily open in Excel, search for commas and replace with spaces. The error goes away so I know the root cause is the extra commas. I cannot use this Excel find/replace as a permanent solution as I have many tables in which to do this for.

Here is a simple example showing what my valid csv file looks like.

header1, header2, header3, header4

data1, "data2 plus, comma in double quotes", data3, data4 <-- ServiceNow doesn't like this.

data5, data6, data7, "data8 plus, comma in double quotes" <-- ServiceNow doesn't like this.

p.s., the above is a valid format according to the Standard RFC 4180

Todd O · ‎02-09-2017

Hi Joel,

With your prodding, I was able to figure out the root cause of my problem. Here are the details. Hopefully this may help someone else in the future. Thanks for spending time on this.

I did some deep analysis of each record and looked for any inconsistencies in the records. I used Notepad++ and turned on show all symbols. I found there are about 3 records where the content of the data field actually contains CR/LF (carriage return and line feeds) embedded in the data. Therefore, these records actually exist on more than one line in the file. This causes SN to say "Hey!, I found records that don't have the correct column count dummy!"

This was an elusive problem since I have thousands of rows. Maybe there's a tool out there than can find and notify you have csv inconsistencies easier than how I did it. Anyway, at least I know my problem. Now, I have to figure out how to hand incoming garbage data from a SQL server database.

Have a good night.

Todd

View solution in original post

Joel Dias · ‎02-09-2017

Service Now doesn't like multi-characters separators.

It is actually the space after the "," that are messing things up.

header1, header2, header3, header4

data1,"data2 plus, comma in double quotes",data3,data4 <-- ServiceNow is OK with this.

data1, "data2 plus, comma in double quotes", data3, data4 <-- ServiceNow doesn't like this.

Todd O · ‎02-09-2017

Hi Joel,

That was my mistake. My file does NOT have the spaces after the commas as I showed above. It's actually like this. Other ideas? Thanks and sorry for leading you down the wrong path.

header1,header2,header3,header4

data1,"data2 plus, comma in double quotes",data3,data4 <-- ServiceNow still doesn't like this.

data1,"data2 plus, comma in double quotes",data3,data4 <-- ServiceNow doesn't like this.

Joel Dias · ‎02-09-2017

The example you are providing is actually working fine in my test instance.

Can you provide more information (instance version, data source setting, ...) and maybe some example CSV that is rejected on your instance?

Todd O · ‎02-09-2017

You bet Joel. I'll scrub the data a bit and then make sure I'm posting a version that is causing the problem. I'll then post it. Thanks for spending you time on this Thursday night.