C# example: posting CSV or Excel files directly to an import set

[WilliamHazelrig]

There are PERL and Java code samples in the Wiki for posting CSV or Excel files directly to an import set, but at present there are not any code samples for doing this with .Net (C#, VB, etc.).   I am experimenting with HttpWebClient and with HttpWebRequest to try to find the correct method of making this work, but to date my best result has been an Error from the Import Processor reporting a java.lang.NullPointerException, which tells me my code uploaded something - but it wasn't the expected content.   Has anyone else attempted this and made it work?

Below is a simplified version of my code from that attempt:

class CSVposter

{

  private System.Net.HttpWebRequest request;

  private string FilePath;

  public CSVposter(string SNinstance, string ImpTable, string FilePath, string UID, string Pwd)

  {

      this.FilePath = FilePath;

      string ImportURL = "https://" + SNinstance + ".service-now.com/sys_import.do?sysparm_import_set_tablename="

                                        + ImpTable + "&sysparm_transform_after_load=true&uploadfile=" + FilePath;

      this.request = (HttpWebRequest) System.Net.WebRequest.Create(ImportURL);

      this.request.Credentials = new System.Net.NetworkCredential(UID, Pwd);

      this.request.PreAuthenticate = true;

      this.request.Method = "POST";

  }

  public void send()

  {

      FileStream fs = new System.IO.FileStream(this.FilePath, FileMode.Open, FileAccess.Read);

      StreamReader src = new StreamReader(fs, System.Text.Encoding.ASCII);

      byte[] Content = System.Text.Encoding.ASCII.GetBytes(src.ReadToEnd());

      Stream dest = this.request.GetRequestStream();

      dest.Write(Content, 0, Content.length);

      dest.Close();

      try

      {

          System.Net.WebResponse resp = this.request.GetResponse();

      }

      catch (WebException Ex)

      {

          Console.WriteLine(Ex.Message);

      }

  }

}

[tony_barratt]

Hi William,

The curl format below works just fine for me

curl -i -F filedata=@name.csv --user "admin:password" "https://instancename.service-now.com/sys_import.do?sysparm_import_set_tablename=u_tablename&sysparm_..." --trace-ascii -

and gives quite informative output.

Best Regards

Tony

[WilliamHazelrig]

Thank you again, Tony.   So far, I have not been able to get the upload to work with cURL for Windows; I'm going to try running it through Fiddler (if I can get it to disregard the SSL certificate mismatch) in hopes of discovering more about why it is failing.

[WilliamHazelrig]

Tony, thank you.   Since CURL is designed for a command-line interface and our solution did not have to be 100% .Net, I wound up cheating: my .Net code simply writes the file I want uploaded and then calls CURL to do the actual uploading.

This unfortunately does not solve the originally-posed problem and will not help anyone who genuinely needs a 100% .Net solution, so if anyone manages to get this working correctly with pure .Net code, I (and other .Net programmers, I'm sure) would love to see how it's done.

[jace]

I am also looking for a way to do this. Has anyone been able to find a solution?

[jace]

Figured it out:

string boundary = "---------------------------" + DateTime.Now.Ticks.ToString("x");

                      byte[] boundarybytes = System.Text.Encoding.ASCII.GetBytes("\r\n--" + boundary + "\r\n");

                      var http = (HttpWebRequest)WebRequest.Create(httpsAddress);

                      http.Method = "POST";

                      http.ContentType = "multipart/form-data; boundary=" + boundary;

                      http.KeepAlive = true;

                      var proxy = new WebProxy(ServerEnv.ProxyUri);

                      proxy.UseDefaultCredentials = true;

                      http.Proxy = proxy;

                      object username = "", password = "";

                      IConnectParms connect = new ConnectParmsClass();

                      connect.GetUserIDandPassword(SNAUTHID, ref username, ref password);

                      String encoded = Convert.ToBase64String(Encoding.UTF8.GetBytes(username + ":" + password));

                      http.Headers.Add("Authorization", "Basic " + encoded);

                      Stream rs = http.GetRequestStream();

                      rs.Write(boundarybytes, 0, boundarybytes.Length);

                      string headerTemplate = "Content-Disposition: form-data; name=\"{0}\"; filename=\"{1}\"\r\nContent-Type: {2}\r\n\r\n";

                      string header = string.Format(headerTemplate, "uploadfile", _workingFilePath, "text/plain");

                      byte[] headerbytes = Encoding.UTF8.GetBytes(header);

                      rs.Write(headerbytes, 0, headerbytes.Length);

                      FileStream fileStream = new FileStream(_workingFilePath, FileMode.Open, FileAccess.Read);

                      byte[] buffer = new byte[4096];

                      int bytesRead = 0;

                      while ((bytesRead = fileStream.Read(buffer, 0, buffer.Length)) != 0)

                      {

                              rs.Write(buffer, 0, bytesRead);

                      }

                      fileStream.Close();

                      byte[] trailer = Encoding.ASCII.GetBytes("\r\n--" + boundary + "--\r\n");

                      rs.Write(trailer, 0, trailer.Length);

                      rs.Close();