| kyro |
| Newbie |
|
|
|
|
| None Specified |
|
| Wednesday, October 21, 2009 |
| Tuesday, February 2, 2010 12:54:41 AM |
4
[0.02% of all post / 0.00 posts per day] |
|
I have a MapForce project which is mapping an EDI file to a database, for each EDI Message it does a table lookup using a SQL-Where.
It is meant to take two of the EDI message values and use them to lookup the correct value in a database table.
If I map either the first or the second value from the EDI file to the SQL-Where it works. However if I map BOTH values to the SQL-Where it fails and will not even execute (clicking generate output returns almost instantly with nothing as the result, compared to when if I map only one of the values it takes a lot longer and returns the correct value).
Its almost like it doesn't even execute the SQL-Where and just automatically returns nothing.
I can run the query against my database and it works fine with both values provided.
The SQL-Where is:
(EndDate = :EndDate) AND (StartDate = :StartDate)
Both StartDate and EndDate are datetimes and I am pulling the values out of the EDI message as datetimes (I tested by mapping both values to an excel output).
|
Hi, is it possible to execute a SQL Server 2005 function or stored procedure from within MapForce and pass parameters to it?
What I have is 4 values (2 locations and 2 times) that are coming from an EDI file, I need to then pass these values to a SQL Server 2005 stored procedure or function (I can change it to either one) and then it will return an integer (0, 1 or 2).
|
island your idea of opening the file in Excel and then resaving it has already crossed my mind. However this is not possible because this is a one step process that is done by the application. The servers do not have Office installed.
Opening a quickly mapped XLSX file from MapForce in WinRAR and looking at the Sheet0.xml file I can see why it my not be working:
Code:
<?xml version="1.0" standalone="yes" ?>
- <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
- <sheetData>
- <row r="1">
+ <c r="A1" t="inlineStr">
- <is>
<t>VALUE A</t>
</is>
</c>
</row>
- <row r="2">
- <c r="A2" t="inlineStr">
- <is>
<t>VALUE A</t>
</is>
</c>
</row>
</sheetData>
</worksheet>
While Excel's output is:
Code:<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> - <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> <dimension ref="A1:A2" /> - <sheetViews> <sheetView tabSelected="1" workbookViewId="0" /> </sheetViews> <sheetFormatPr defaultRowHeight="15" /> - <sheetData> - <row r="1" spans="1:1" ht="12.75"> - <c r="A1" t="s"> <v>0</v> </c> </row> - <row r="2" spans="1:1" ht="12.75"> - <c r="A2" t="s"> <v>0</v> </c> </row> </sheetData> <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" /> </worksheet>
notice that cell values in the mapforce output are:
Code:<c><is><t>value</t></is></c>
While in Excel its:
|
Hello,
We are mapping an EDIFACT message to an Excel 2007 File.
Which is then processed by our already existing code that can read XLS and XLSX documents (saved by Microsoft Excel 2003/2007) using OleDBConnections.
However the output file from MapForce 2009 is not in a valid Excel format.
We can open the file in Excel 2007, but using OleDBConnections we keep getting:
"external table is not in the expected format" errors.
If we open the file with Excel 2007 and then save it (no data change, no file name change) then our OleDBConnection code can read it fine.
This is a direct save of the output from Altova MapForce 2009:
Download Sample Output Here
Here is some sample code to demonstrate (put it into a Windows form application, on a button click event):
Code:System.Data.OleDb.OleDbConnection oConn = null;
try
{
OpenFileDialog fileDiag = new OpenFileDialog();
fileDiag.ShowDialog();
string sFileName = fileDiag.FileName;
string sConnection;
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFileName + ";Extended Properties=Excel 12.0 Xml;";
oConn = new System.Data.OleDb.OleDbConnection(sConnection);
System.Data.OleDb.OleDbDataAdapter oDA = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Template$]", oConn);
DataSet oDS = new DataSet();
try
{
oDA.Fill(oDS);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
MessageBox.Show(oDS.Tables.Count.ToString());
}
catch (Exception ex)
{
}
Is there something I am doing wrong? or is this an error with MapForce ?
|
|
