PDA

View Full Version : Newbie question...importing Excel data



Unregistered
09-27-2005, 03:44 PM
Hello,

Has anyone figured out an easy way to import Excel data into PV-Wave?

Cheers for any help.

ed
09-28-2005, 06:37 AM
You can highlight the cells in Excel and either drag them onto the PV-WAVE Home Window, or copy the cells (Ctrl-C) and then press the paste button in the Home Window (or Ctrl-V).

Either of these will pop up an Import Excel Data dialog box and ask for a Variable Name (excel_data is the default). If you just press OK and then do a info, excel_data you should see your array of DOUBLE values.

Unregistered
09-28-2005, 09:57 AM
The recommendations worked but the only problem is i am working with a lot of data and it is not very practical to copy and paste the excel cells into PV-Wave.
Is this the only way to get my excel data into PV-Wave? Is there any way to read the excel spreadsheet into PV-Wave? I am hoping there is another way...please help!

Thanks.

stef
09-28-2005, 10:27 AM
Alternatively:

I. DC_READ_FREE

1) Open .xls file in Excel
2) Save As... .csv file (comma separated values)

If your regional settings are those of a comma country (http://en.wikipedia.org/wiki/Decimal_separator), you will have to replace ',' by '.':

- search and replace function of your editor, or
- invoke external tool (e.g. sed) from PV-WAVE via SPAWN command, or
- apply string processing functions in PV-WAVE to your .csv file

3) Use the DC_READ_FREE function in PV-WAVE

II. ODBC Connection

http://www.vni.com/cgi-bin/tipsengine.pl?id=140&formType=GET&kw=Excel&type=AND&int=FALSE&inh=FALSE

This requires a bit more practice and testing, but also allows you to update an Excel sheet.

DGP
10-19-2005, 08:22 AM
I've written PV-Wave/Visual Basic routines to export out to Excel using the DC_WRITE_FREE to create the data file. There is a 65536 limit I ran into on the Excel side. The reverse solution would work just as well, I'm sure. Using a Visual Basic application along with a PV-Wave SPAWN allowed me to simplify the user interface. Error checking can be included in the VB application. I can include details if you decide the go with this approach. I use a good amount of VB to work with the operating system in various areas of program. The combination of VB and PV-Wave works well for me, being that each has different strong points and weak points.

Unregistered
02-01-2006, 12:25 PM
Is there a to import column labels when doing a DC_READ_FREE?

Seems like there should be some work-around to read the first line, parse the strings into an executable statement with the DC_READ_FREE and execute it.

Bricey
02-10-2006, 03:32 PM
Try this:

1) Use READF to read the first line of the file (i.e. variable names).
2) Parse the variable names.
3) Use the EXECUTE function to dynamically build the DC_READ_FREE command to read the data into the correct variables.

Here is example code:

filename = 'datafile.dat'
varnames = ' '
;
; Open the file and read the first line; first
; line has the list of variable names.
;
OPENR, 1,filename
READF, 1,varnames
CLOSE, 1
;
; The file is organized so that variable names are
; separated with a space. Use STRSPLIT to create a
; string array of the variable names.
;
vars = STRSPLIT(varnames, ' ')
;
; Build the DC_READ_FREE command to process the file.
; First, define the start of the command which includes
; the name of the file.
;
cmd = 'status = DC_READ_FREE("' $
+ filename + '",'

FOR i = 0, N_ELEMENTS(vars)-1 DO BEGIN
cmd = cmd + vars(i) + ','
ENDFOR

numvars = STRING(N_ELEMENTS(vars))
cmd = cmd + 'Nskip = 2, /Column, ' + $
'Resize = INDGEN(' + numvars + ')+1)'

PRINT, 'Executing ' + cmd + '...'

..LOCALS 50

exec_stat = EXECUTE(cmd)
INFO

Hope this helps.
-Bricey

Bricey
02-16-2006, 02:22 PM
You may want to also check out the WzPreview tool.

WAVE> WzPreview

WzPreview is a point-and-click tool for previewing and reading in ASCII data. There is an "Autodefine" button you can select to have PV-WAVE automatically figure out the variable names based on the column labels OR you can define the header, record, and fields manually.

I prefer reading in the file programmatically with the DC_READ_FREE and EXECUTE functions but this is an alternative for someone who is more comfortable with a point-and-click interface.

Sincerely,
-Bricey


Is there a to import column labels when doing a DC_READ_FREE?

Seems like there should be some work-around to read the first line, parse the strings into an executable statement with the DC_READ_FREE and execute it.