Exporting PS Query results to Excel and Importing into Brio
Run the People Soft query and export the results to Excel. Because People Soft creates a web page type file, the file will have the first row as a title and the second row will be blank. Save the resulting file to your desired destination in the original htm or html format. Once saved, its time to modify the file so it will import into Brio properly. Delete the first two rows of the spreadsheet (title and blank row). Save the file as a prn (Formatted Text- Space delimited). Close the file and reopen it in excel. Parse the columns as necessary using the fixed width option. Be sure that the Column Data Format is ‘Text’. Save the file as a ‘Microsoft Excel 5.0/95 Workbook’. The file is now ready to be imported into Brio!
Importing data into Brio.
The 3 most common files imported into Brio are test files (txt), common delimited (csv), and excel spreadsheets (xls). The basics for importing such files are simple. Click on “File” – Import Data File – Data File…
Choose the file that you want to import from where it is located either on your pc or on a network drive. Be sure that the columns on your file are the same data types as the files that you want to join to. Mismatched data types are a very common problem when importing files. Brio will only read Excel version 95, so be sure to save the file as this type. Also make sure that with all the file types, that the first row is actually your column titles. If not, you may end up with column titles that are actually your data or even gibberish.
Once you import the file into Brio, the file is accessible in the local results section. Prepare your query and then choose the local results. Pick the file you imported and join common fields with any RDS tables you are using.
Submitted by Joe McCullough, BFS
October, 2003