ConnectToMicrosoftExcel - Revision history

IanMcGowan: Add options for creating XML spreadsheet as well as "true" XLSX, and links to code in [BasicSource]

2017-03-03T06:30:59Z

Add options for creating XML spreadsheet as well as "true" XLSX, and links to code in [BasicSource]

← Older revision		Revision as of 06:30, 3 March 2017
Line 3:		Line 3:
	* Use the [[AccuTerm]] FTD utility to import and export with .xls files. [[AccuTerm]] also has [http://www.asent.com/code.html samples] to use Excel formulas to access MV. See		* Use the [[AccuTerm]] FTD utility to import and export with .xls files. [[AccuTerm]] also has [http://www.asent.com/code.html samples] to use Excel formulas to access MV. See
	* [http://nebula-rnd.com/products/xlite.htm <nowiki>NebulaXLite</nowiki>] allows creation of rich documents from BASIC, compared to plain data in cells.		* [http://nebula-rnd.com/products/xlite.htm <nowiki>NebulaXLite</nowiki>] allows creation of rich documents from BASIC, compared to plain data in cells.
			* Since Office 2003, Excel has supported so-called XML Spreadsheets, using a schema known as [https://msdn.microsoft.com/en-us/library/bb226687.aspx SpreadsheetML] - this is a simple approach that has the advantage of creating a single file, in a manageable format. There are a couple of downsides: 1) If you name the file .xml it opens in IE, but if you name it .xls it opens in Excel with a warning that may be alarming. 2) If you are exporting thousands of rows with dozens of columns, the resulting files can get quite large. Still, for modest needs this is an easy approach. [[SCI.XLS.RECALL]] has an example of a program that takes a delimited text file and converts it to an XML document suitable for loading into Excel.
			* Since Office 2010, the internal format used for Office documents has switched to a zipped collection of XML files. Since these are "just" text files, it's reasonable to attempt to produce them directly from a PICK Basic program, and this is certainly feasible. Trying to reverse-engineer the xlsx XML schema can be challenging - Excel throws everything and the kitchen sink into the zip file, and small errors lead to Excel rejecting the entire file. [http://polymathprogrammer.com/tag/openxml/ Polymath Programmer] has some excellent resources to explain what's going on at just the right level of detail. Well worth buying the [http://polymathprogrammer.com/about/ book], and the free [http://spreadsheetlight.com/ code] if you're using DotNet. [[SCI.XLSX.RECALL]] has an example of a more complicated program that takes a delimited file and turns it into a zipped collection of XML documents that can be open natively in Excel.

	== Importing data from Excel to MV ==		== Importing data from Excel to MV ==

Conversion script: link fix

2015-02-26T23:48:54Z

link fix

← Older revision		Revision as of 23:48, 26 February 2015
Line 1:		Line 1:
	== Exporting data from MV to Excel ==		== Exporting data from MV to Excel ==
	* Create a CSV or other Tab-Delimited file. When a .csv file is opened with Excel you'll get a basic set of columns and rows.		* Create a CSV or other Tab-Delimited file. When a .csv file is opened with Excel you'll get a basic set of columns and rows.
	* Use the AccuTerm FTD utility to import and export with .xls files. AccuTerm also has [http://www.asent.com/code.html samples] to use Excel formulas to access MV. See		* Use the [[AccuTerm]] FTD utility to import and export with .xls files. [[AccuTerm]] also has [http://www.asent.com/code.html samples] to use Excel formulas to access MV. See
	* [http://nebula-rnd.com/products/xlite.htm <nowiki>NebulaXLite</nowiki>] allows creation of rich documents from BASIC, compared to plain data in cells.		* [http://nebula-rnd.com/products/xlite.htm <nowiki>NebulaXLite</nowiki>] allows creation of rich documents from BASIC, compared to plain data in cells.

	== Importing data from Excel to MV ==		== Importing data from Excel to MV ==
	* As above, see AccuTerm.		* As above, see [[AccuTerm]].
	* See [http://nebula-rnd.com/blog/tech/2007/11/excel-to-mv1.html this blog entry] for discussion of a possible future product as well as code to integrate Excel with MV via a web service: Search the blog for notes about integration with Microsoft Office applications like Outlook.		* See [http://nebula-rnd.com/blog/tech/2007/11/excel-to-mv1.html this blog entry] for discussion of a possible future product as well as code to integrate Excel with MV via a web service: Search the blog for notes about integration with Microsoft Office applications like Outlook.

TonyG: Added tags and See Also

2009-12-19T19:25:30Z

Added tags and See Also

New page

== Exporting data from MV to Excel ==
* Create a CSV or other Tab-Delimited file. When a .csv file is opened with Excel you'll get a basic set of columns and rows.
* Use the AccuTerm FTD utility to import and export with .xls files. AccuTerm also has [http://www.asent.com/code.html samples] to use Excel formulas to access MV. See
* [http://nebula-rnd.com/products/xlite.htm <nowiki>NebulaXLite</nowiki>] allows creation of rich documents from BASIC, compared to plain data in cells.

== Importing data from Excel to MV ==
* As above, see AccuTerm.
* See [http://nebula-rnd.com/blog/tech/2007/11/excel-to-mv1.html this blog entry] for discussion of a possible future product as well as code to integrate Excel with MV via a web service: Search the blog for notes about integration with Microsoft Office applications like Outlook.

== Direct Connectivity vs import/export ==
This can be accomplished with any connectivity code and Microsoft Office add-ins. Look into Microsoft VSTO (Visual Studio Tools for Office) to create the Excel integration piece. Other methods can be used such as XLAs or VBA macros. Excel development is a field unto itself. Use tools like [[UniObjects|UniObjects.NET]], [[mv.NET]], or <nowiki>ON.NET</nowiki> to do the actual connectivity directly into MV. For clients outside of the intranet, create the add-in with a web service interface and connect into any MV platform using standard web service methods. (TG)

== See Also ==
There are various solutions on this site for converting Excel to CSV and parsing CSV to dynamic or dimensioned arrays.

== Misc discussions ==
http://groups.google.com/groups?hl=en&q=group:comp.databases.pick+excel

----

[[Tags]]: excel csv spreadsheets