Import from Excel to a datawindow

1
0
-1

Is there a way to import data from an Excel file into a datawindow through the powerscript code in a window object?

I want to have the user click on a command button, then in the powerscript code the data would be imported into the datawindow.

My application is set up meaning that I already have a datawindow that the columns match the columns in the excel file I want to import from. The excel file will be an xlsx or an xls type file. I can import the data successfully if I go through the Import-Export wizard. However, I don't want the user to have to use the wizard.

Preferably I would like the user to click on the command button and the data will automatically load into the datawindow. If anybody can give me an idea of what to do I would appreciate it.

If I could get an example of what to do, that would be great. Or, if there is a place in the documentation where I can see an example of how to do this, that would be good too.

Thank you in advance

Question Tags: 

Answers

Michael Kramer answered Import from Excel to a datawindow

1
0
-1

Hi, Use dw.ImportFile to import from Excel file into a DataWindow. Same for a DataStore.

dw_MyData.ImportFile(XLSX!, "myData.XLSX")

 

Roland's picture

The ImportFile method does not have XLSX! as a filetype. The valid datatypes are: Text!, CSV!, XML!, DBase2!, DBase3.

The easiest method is to setup an ODBC profile to the Excel file and then in PB connect to it as if it were a database and retrieve the datawindow.

Michael Kramer's picture

Roland is right, my mistake -- and -- I should have tested my code snippet.

I've been doing complex Excel import/export for years using OLE Automation.

ODBC vs. OLE Automation: ODBC requires less code for importing/exporting data. OLE Automation exposes the complete Excel API in case you need more than the ODBC interface provides.

HTH​  /Michael

jon_15171's picture

I will try

Thanks

Ashutosh Varshney's picture

You can also use ole automation to open the xlsx file, save it as csv and then import the csv using ImportFile function. That way you would not have to worry about the ODBC being setup.

HTH.

Heiko Bergner's picture

If you work with ole you could also open the excel-file, mark the data which should be imported, copy it to the clipboard
and then with

dw_1.importclipboard()

you can import the data into your datawindow.

Heiko