I am trying to import from an excel data file using openrowset using the below.
declare @t table
select * from @t
from openrowset('Microsoft.ace.oledb.12.0','Excel12.0;HDR=YES;Database=C:\Projects\Test.xlsx','select * from [Sheet1$')
I keep getting the error ' Name or Alias Can Not Be A Reserved Word: openrowset.
Does Vistadb5 support the openrowset function??
The SQL parser in VistaDB does not recognize OPENROWSET as a token, so there is no implementation for it in VistaDB. It shows up in the list of SQL reserved words which helps catch unquoted names which would fail in SQL Server, which is why it generates the odd error.
You might be able to implement your own table-valued CLR function to reference an Excel library and read rows from the Excel spreadsheet and provide them as a table result. Note that I am not sure whether a third-party library dependency can be referenced from a CLR function, but it may be worth trying. You'd also probably have to change the name of the function or possibly call it with quoting brackets around it to avoid the "can't be a keyword" error.
VistaDB does have methods in the DDA API for importing and exporting table data as XML: http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.html#VistaDB.5.NET40~VistaDB.DDA.IVistaDBDatabase_methods.html (don't miss AddToXmlTransferList or else nothing will happen) There might be a path to do this from .NET (or from Data Builder) if you can get the data in XML form (might require minor editing, such as to give it the correct table name, or more extensive editing if the format is too different). Experimenting with exporting to XML would help show the format it expects.
Are you trying to migrate a specific database as a one-time or occasional thing which you can direct manually, or will your app need to migrate databases from another source programmatically? Have you looked at our Data Migration Wizard? What database provider is the source database originally under?