New to API- CSV DataSource Question

[Chuck Lafean]

I am just starting to learn both C# and the WS APIs. I am modifying the hot folder sample and while everything is making sense, I get this error when I upload a CSV file.

Error
Recipients Table: Query execution failed.
Please check query "SELECT * FROM Test_WelcomeLetterMailFileFeb222016  ".
(OLEDB Message: The Microsoft Jet database engine could not find the object 'Test_WelcomeLetterMailFileFeb222016.txt'.  Make sure the object exists and that you spell its name and the path name correctly.)

I know this has to be something simple, like my connection string or something, but I am at a loss...

Here is the modified code (the hot folder has been changed to deal with the CSV file and this is uploading the file correctly):

string path1 = inFileName;
string tablename = Path.GetFileNameWithoutExtension(path1);
string Filename = Path.GetFileName(path1);
string DataSourceName = Filename;
string DataSourceType = "TXT";
string CampaignID = "1261";
string FolderPath = @"\\NAS3\nasshare\Common Downloads 3\HotFolder";
string FullUploadPath = System.IO.Path.Combine(FolderPath, Filename);
string FullUploadFilePath = System.IO.Path.Combine(FolderPath, Filename);
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\\\nas3\\nasshare\\Common Downloads 3\\HotFolder;Extended Properties=\"text;HDR=YES;FMT=DelimitedCSV\";" + FullUploadFilePath;
string AdditionalParams = ",";

XMPieAPIWS_DataSource.DataSource_SSP DataSourceWS = new XMPieAPIWS_DataSource.DataSource_SSP();

// Upload a list

string dataSourceID = DataSourceWS.CreateNew(uName, Password, CampaignID, DataSourceType, DataSourceName, ConnectionString, AdditionalParams, FolderPath, false, null);

// Create the job ticket web service object

XMPieAPIWS_JobTicket.JobTicket_SSP jobTicketWS = new XMPieAPIWS_JobTicket.JobTicket_SSP();
           
// Create a new job ticket
string jobTicketID = jobTicketWS.CreateNewTicketForDocument(uName, Password, DocumentID, "", false);

// Set the job output type
jobTicketWS.SetOutputInfo(uName, Password, jobTicketID, "PDFO", 1, "", "", null);

// Set the job type
jobTicketWS.SetJobType(uName, Password, jobTicketID, "PRINT");

// handle recipient list
XMPieAPIWS_JobTicket.RecipientsInfo riInfo = new XMPieAPIWS_JobTicket.RecipientsInfo();
//riInfo.m_Filter = "Customers";

//riInfo.m_Filter = tablename;

// the table name
riInfo.m_FilterType = 3;
XMPieAPIWS_JobTicket.Connection conn = new XMPieAPIWS_JobTicket.Connection();
conn.m_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\\\nas3\\nasshare\\Common Downloads 3\\HotFolder;Extended Properties=\"text;HDR=YES;FMT=DelimitedCSV\";" + FullUploadFilePath;
riInfo.m_Filter = tablename;

//conn.m_Type = "CSV";
jobTicketWS.SetRI(uName, Password, jobTicketID, riInfo, conn);

// Create the production web service object
XMPieAPIWS_Production.Production_SSP productionWS = new XMPieAPIWS_Production.Production_SSP();

// Submit the job
string jobID = productionWS.SubmitJob(uName, Password, jobTicketID, "0", "", null);
label1.Text = "File " + inFileName + " - Job " + jobID + " is being submitted.";

[couch]

I vaguely remember seeing this too. From memory, it was either:

1) the type of path to the file that I was using - ie try using file:////server//folder//file.txt or c:\\folder\\file.txt type of paths; or

2) maybe that the file needed to be on the server file system, not on a NAS/shared folder.

That said, since yours is on a shared NAS folder, you should also check that the windows user that XMPie is using can access the share without needing credentials. 

You can check that by looking in the services control panel to see which user is running the XMPie services.

[Chuck Lafean]

I will give that a try- development time is always trumped by production work: which I have a lot of at the moment... Thanks for the suggestion.

C

[Raju Bhupathi]

Hi Chuck,

Could you able to resolve this issue, I am getting same error.  I tried both including UNC  and local paths using couch example. Still I could not resolve..

Appriciate you help on this.

Thank you

Bhupathi.

[couch]

Here is some sample code behind from a web page with a simple .net file upload control to receive the file, and process it.

//set some default variables

string xmp_user = "test";

string xmp_pass = "pwd";

string xmp_docID = "19188";

string xmp_account = "test";

string xmp_campaign = "upload";

string savePath = @"c:\temp\uploads\";

string fileName = "";

string extension = "";

string jobid = "";

returnMessage.Text = "";

//1.

//handle the file upload

if (CSVfileUpload.HasFile)

{

fileName = Server.HtmlEncode(CSVfileUpload.FileName);

extension = System.IO.Path.GetExtension(fileName);

if (extension == ".csv")

{

CSVfileUpload.SaveAs(savePath + fileName);

}

else

{

returnMessage.Text = "The file you selected was not a CSV...";

return;

}

}

else

{

returnMessage.Text = "You didn't select a file to upload...";

return;

}

//2.

//do the print

//create recipient info

xmp.RecipientsInfo ri = new xmp.RecipientsInfo();

ri.m_Filter = fileName;

ri.m_FilterType = 3;

//create the connection

xmp.Connection conn = new xmp.Connection();

conn.m_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";Extended Properties=text;";

conn.m_AdditionalInfo = fileName + "@,@,";

conn.m_Type = "TXT";

// create job ticket

xmp.JobTicket_SSP jt = new xmp.JobTicket_SSP();

string jtid = jt.CreateNewTicketForDocument(xmp_user, xmp_pass, xmp_docID, "", false);

jt.SetOutputInfo(xmp_user, xmp_pass, jtid, "PDFO", 1, null, null, null);

jt.SetJobType(xmp_user, xmp_pass, jtid, "PRINT");

jt.SetRI(xmp_user, xmp_pass, jtid, ri, conn);

//submit the job

xmp.Production_SSP prod = new xmp.Production_SSP();

jobid = prod.SubmitJob(xmp_user,xmp_pass,jtid,"0",null);

//return job id to user...

returnMessage.Text = "Job submitted. Check the XMPie dashboard's job centre for job id " + jobid;

[Dwight Kelly]

What should ConnectionString and AdditionalInfo be set to if the datasource was uploaded to a TempStorage folder?

[couch]

AFAIK the temp storage AddFileToFolder() method returns a token which is the file name/path. 

You can use the normal System.IO.Path methods to get the path and filenames to use in the connectionString and additionalInfo

[couch]

Here is a code sample using temp storage together with jobticket and production classes:

//set some variables

string username = "xxx";

string password = "yyy";

string remotedatafile = @"c:\temp\contacts2.csv";

int bufferSizeInBytes = 128;

string docID = "222";

//do the file upload to temp storage

xmp_ts.TempStorage_SSP tempStorage = new xmp_ts.TempStorage_SSP();

string tempFolder = tempStorage.CreateFolder(username, password);

string fileToken = tempStorage.AddFileToFolder(username, password, tempFolder, System.IO.Path.GetFileName(remotedatafile));

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

int numToRead = (int)fs.Length;

int bytesRead = 0;

byte[] buffer = new byte[bufferSizeInBytes];

while (numToRead > 0)

{

bytesRead = fs.Read(buffer, 0, bufferSizeInBytes);

if (bytesRead > 0)

{

bool ret = tempStorage.AppendFileBinaryStream(username, password, fileToken, buffer);

numToRead -= bytesRead;

}

}

//create the recipient info

xmp_jt.RecipientsInfo ri = new xmp_jt.RecipientsInfo();

ri.m_Filter = System.IO.Path.GetFileName(fileToken);

ri.m_FilterType = 3;

//create the connection string

xmp_jt.Connection conn = new xmp_jt.Connection();

conn.m_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(fileToken) + ";Extended Properties=text;";

conn.m_AdditionalInfo = System.IO.Path.GetFileName(fileToken) + "@,@,";

conn.m_Type = "TXT";

//create job ticket

xmp_jt.JobTicket_SSP jt = new xmp_jt.JobTicket_SSP();

string jtid = jt.CreateNewTicketForDocument(username, password, docID, "", false);

jt.SetOutputInfo(username, password, jtid, "PDFO", 1, null, null, null);

jt.SetJobType(username, password, jtid, "PRINT");

jt.SetRI(username, password, jtid, ri, conn);

//submit the job

xmp_p.Production_SSP prod = new xmp_p.Production_SSP();

string jobid = prod.SubmitJob(username, password, jtid, "0", null, null);

//return the jobid to the user or do something else with it

Label1.Text = jobid;

//remember to delete the temp storage folder and contents after production is completed

[Dwight Kelly]

Hello,

I'm not a .NET developer. Will these functions work as expected as the TempStorage tokens are not regular Windows paths.

System.IO.Path.GetDirectoryName(fileToken)
System.IO.Path.GetFileName(fileToken)

A file token looks like this: Token{b0b08665-e46c-4fe2-bf82-7760328921d7}\test123.csv
and a folder token: Token{b0b08665-e46c-4fe2-bf82-7760328921d7}

[couch]

Yes, because they are actually windows paths...

My code is a working example created specifically to demonstrate this functionality.