Loading...

How to convert a Microsoft Excel File into a Flat File for PIR Staff Reporting

For instances where you have to convert data that you have downloaded from Wisenet as an Excel file to a Flat file, follow the steps below.

1

  1. Navigate to  HEIMSHELP > Provider Information Request
  2. Click on the Reporting your data Tab and Click to expand How to create a HEPCAT data file from an Excel spreadsheet
  3. Click to download and save the PIR Microsoft Excel to Flat File Template XLSX

2

  1. Open Microsoft Excel
  2. Under Menu, Select File > Open
  3. Navigate to the System Folder where your Wisenet Data has been exported to
  4. Select the spreadsheet which contains the data being converted
  5. Click Open

3

  1. Once the spreadsheet has successfully opened, highlight all data records
  2. Right-Click the highlighted data records and Click Copy

Important Note!

When selecting the records to copy do not use the Select All or include headings that may have been exported with the data records. Only actual data records should be copied.

4

  1. Once again from Microsoft Excel, under Menu, select File > Open
  2. Navigate to the system folder where the PIR Microsoft Excel to Flat File Template has been saved, Select and Open

Note!

The PIR Microsoft Excel to Flat File Template has a separate tab for each file that is to be submitted to the department.

3. Locate and Click the relevant Tab

4. Select Cell B2 and paste, using Crtl V (for Windows keyboard) or Cmd V (for Mac Keyboard)

Important Note!

You will notice that the cells in column A have been populated.

Before you progress you must check all the cells in column A to ensure that the data records have copied successfully. If the length of any of the data elements is incorrect an error
(#VALUE) will show. Any cell showing this error will need to be corrected before progressing.

The main reason why a data record has not copied successfully is due to the format.  Sometimes when data, numbers in most cases, are copied into Microsoft Excel the change
of format removes leading or trailing zeros.

Click image to enlarge

5

  1. Once all data records have been successfully pasted, highlight all records in Column A
  2. Then Right-Click the highlighted data records and Click Copy

Important Note!

When selecting the records to copy do not use the Select All or include the headings that are highlighted in yellow. Only actual data records should be copied.

3. Open Notepad and paste the previosuly copied data records (using Crtl V or Cmd V)

4. From the Menu, Select File > Save As

5. Navigate to the system folder where the Sample HEPCAT Flat Files have been saved and select the appropriate sample

6. Update the File Name by replacing 9998 with your organiation number and removing the .txt extension

7. Click Save

Note!

It is best, once populated with data, to move the Sample HEPCAT Flat Files to C:\hepcat\Import for importing to HEPCAT. If you do not have HEPCAT installed you can save these files to another preferred location.

Click image to enlarge

Was this Resource helpful?