Importing PXRD data into Excel (alternative view with large screen shots)

PXRD Material Index  Previous Page  Next Page

Importing PXRD data into Excel

The powder X-ray diffraction (PXRD) data from the different instruments at UCL is saved in the instrument manufacturers "raw" state (as a binary file). Data conversion software written by Jeremy Karl Cockcroft with the help of Ron Ghosh enables the raw data to be provided as a simple text file (*.xye) and as an Excel formatted input file (*.xml). The latter is only intended for use with Microsoft Excel as it provides an easy method for importing data into this spreadsheet program.

Depending on your local computer setup (i.e. file program associations), the *.xml files may open directly with Excel by clicking on it with the mouse or the file may open in a browser window. If the latter happens, launch Excel first and then use the open file option within Excel to open the *.xml file. When opened, the data should appear as in the screen shot shown below:

Then choose the Save As option to save the data as an Excel workbook file (the first type listed in the list file types with the file extension *.xlsx). In the screenshot examples, I have used the data set file name. Do not save the data in any other format.

The data has been pre-formatted in the worksheet in columns labelled 2θ (in degrees), Intensity (in counts), Error (in counts), plus a small table of header information such as sample name, instrument, wavelength, etc.

Although not required for the 3rd year laboratory practicals involving PXRD analysis, simple text files are also generated by the data conversion software. These have the extension *.xye and contain the data columns in plain text format (the files may be examined with a simple editor such as Notepad). Note that these data files have no header information stored with them and so only the filename provides any information on the data (usually via the hand-written logbooks stored in the PXRD labs).

Column data stored in a plain text file may be read into Excel but a few more steps are required than for the *.xml files. Firstly, use File then Open and make sure that you can see All Files (*.*) and not just Excel files (the default option!) in the folder containing the *.xye file:

Select the file you wish to open and Excel will bring up a wizard menu for importing plain text data into Excel (as shown below). The *.xye files for the in-house instruments are usually written in fixed-width format, so it is better to choose this option:

Check that Excel has correctly identified where the column breaks should go (as it does not always get this right by default!):

Then choose Finish and the data should appear as unformatted numbers as shown in the spreadsheet below:

It is also possible to read the same text file as Delimited instead of as Fixed Width. If this option is chosen, check that the box marked Space is ticked:

Note that Excel can also read comma-separated data and in this case you would need to tick the box marked Comma as well. The data will be imported with blanks in column A, so this column should be deleted after choosing Finish.

Before saving the raw data in Excel, format the values as numbers with an appropriate number of decimal places. To do this select a column and right mouse click to get the menu with the option Format Cells... as shown below (with the 2θ values selected):

In the format cells menu, select the Number tab, choose Number and set an appropiate number of decimal places, e.g. 3 given a 2θ step for the data of 0.015°. Note that intensity in counts should have 0 decimal places (in contrast to say counts per second or minute):

Finally, choose the Save As option to save the data as an Excel workbook file as described earlier.

PXRD Material Index  Previous Page  Next Page