search

agstudy / rsqlserver

Sql Server driver database interface (DBI) driver for R. This is a DBI-compliant Sql Server driver based on the System.Data.SqlClient.
82 stars 30 forks source link

Interpret quotes #18

Closed wibeasley closed 7 years ago

wibeasley commented 9 years ago

Is there a way to have the bulk copy mechanism interpret the quotes surrounding character fields, and not upload them to the database. (For my current situation, I could just unquote the temp csv, but I'm hoping there's a more general solution in case the quotes are necessary to handle commas inside the actual values.)

In other words, instead of the default bulk copy resulting in something like this

image it would look like this: image

I see that clrCallStatic("rsqlserver.net.misc","SqlBulkCopy",con.string ,value,name) is called underneath, and you've mentioned the bcp utility in other posts, but I didn't understand how they fit together. Is this a simple configuration change?

agstudy commented 9 years ago

Using Quote=FALSE fix the problem?

wibeasley commented 9 years ago

That option worked for my specific scenario (which prompted PR #17), but I think always including the quotes is an overall undesirable behavior because

  1. It's almost never desired to have quotes around text fields after it's written to the database. Those quotes are mostly so the CSV columns don't get confused if a text field contains a comma. SQL WHERE clauses are much more natural as WHERE FindingCategory='Bad' than WHERE FindingCategory='"Bad"'.
  2. Sometimes there's a legitimate need to use quotes for that intermediate file saved (on the local machine before the bulk upload occurs). Like when a text field contains a comma (eg an address).

I don't understand the details of the underlying chain using bcp and clCallStatic. I was hoping those developers had encountered this problem before, and had some option like "IgnoreQuotesDuringUpload".

wibeasley commented 7 years ago

Thanks @agstudy for all your work on this package. It saves us so much time (over RODBC) with our warehouse-ish projects.

agstudy commented 7 years ago

Thank you. Since the end of this year , I will come back to this package to work on it actively and use it as a component from our consulting services. So I will be happy to hear from you and others how do you use this package and what features do you want to include.

wibeasley commented 7 years ago

Great. I just sent you an invitation on LinkedIn. Contact me when it's best for you, and I'm happy to help.