Excel extracting data from report

pernes

Hi I am trying to extract some data that I can export into Excel, I can export the data in a number of formats, CSV, text, chr. From the following data all I want to put in columns is the Record Name, Date of Treatment, Location, Type and time allocated. But as this is in the row and not columns I can't seem to separate it. They will be multiple records that I want to put in the spreadsheet. can anyone advise? Thanks


Record Name : John Dingle Date Checklist Assigned : 22/02/2014 Assigned By User : System Date Last Updated : 04/03/2014 Current Step : 5. STEP NAME VALUE POSTED DATE POSTED BY USER 1. Date of treatment 20/01/2014 22/02/2014 System 2. Location 22/02/2014 System Home 3. Type of Treatment Counselling 04/03/2014 System 4. Time Allocated 4 04/03/2014 System

peaceandfreedom

pernes wrote: »

Hi I am trying to extract some data that I can export into Excel, I can export the data in a number of formats, CSV, text, chr. From the following data all I want to put in columns is the Record Name, Date of Treatment, Location, Type and time allocated. But as this is in the row and not columns I can't seem to separate it. They will be multiple records that I want to put in the spreadsheet. can anyone advise? Thanks


Record Name : John Dingle Date Checklist Assigned : 22/02/2014 Assigned By User : System Date Last Updated : 04/03/2014 Current Step : 5. STEP NAME VALUE POSTED DATE POSTED BY USER 1. Date of treatment 20/01/2014 22/02/2014 System 2. Location 22/02/2014 SystemHome 3. Type of Treatment Counselling 04/03/2014 System 4. Time Allocated 4 04/03/2014 System

You don't say where you are extracting the data from. From the look of the data above, you have exported it as Text. Try exporting it as CSV (which stands for comma-separated-values). That way each piece of data should be separated by a comma and when you open it in Excel, it should put each field into separate columns.

I'm not sure that will work with your data though - it depends what it looks when output as a CSV. If it is still output with the field label followed by the field data (as it has above) it will still be messy to import into Excel.

Heedtheadvice

I agree with P&F above, but with a few additional comments.

Unless you can directly import or link the data from the two programs together (you really should specify what they are and versions if possible) then csv files do give a lot of flexibility and Excel is great at importing them). The comma is usually inserted in between records and fields within records ad the import process in Excel allows some record selection etc. so works well.

You would however lose any formating from the original prog but records should be there (up to maximum that can be handled which is a lot for most users).

Once you have the records in Excel it is an easy matter to transpose the rows and columns. Simply highlight all the data, copy (example)
a 1 2 3
b 4 5 6
c 7 8 9
and then select a new destination start cell (maybe a new sheet) & edit paste special selecting all and transpose options. result:
a b c
1 4 7
2 5 8
3 6 9
HOWEVER!
You so say it's data from a report so I assume it is from a database. The layout of the report seems to be a header giving basic data, and then a number of records listed
i.e. header for individual report record "Record Name : John Dingle Date Checklist Assigned : 22/02/2014 Assigned By User : System Date Last Updated : 04/03/2014 Current Step : 5."

and then the data for that section " STEP NAME VALUE POSTED DATE POSTED BY USER 1. Date of treatment 20/01/2014 22/02/2014 System 2. Location 22/02/2014 SystemHome 3. Type of Treatment Counselling 04/03/2014 System 4. Time Allocated 4 04/03/2014 System"

are there multiple data sections under the one header as is typical for a Report style output or is the 'header' repeated for every data item?
If the latter (when exported) it may be no problem i.e. just one big csv table but if the former its a bit more complicated and would probably be best with a dedicated output report or another database method to correctly layout as a simple table.

More info needed please.......

agrinnall

Just be careful when using CSV if your data contains any commas before you extract it. If it does then you can either change commas to another character before doing the extract (if wherever it is allows it) or use a different character as the separator (but again, check your data first to make sure that character isn't used).

Mirno

If the data is in a specific format - either delimited by a specific character (comma, tab, pipe, spaces etc.) or fixed width then use Excel's "Text to Columns" functionality - data->text to columns on 2007 or less (no idea where it is on the ribbon on Office 2010).

Highlight the rows you need to split up, then select the function. Fill in the appropriate bits. If the text is fixed width (with spaces at the end to keep fields where they should be) Excel will make a guess based on the data there, but you can add new columns or delete others at the appropriate step in the process.

Mirno

peaceandfreedom

Mirno wrote: »

If the data is in a specific format - either delimited by a specific character (comma, tab, pipe, spaces etc.) or fixed width then use Excel's "Text to Columns" functionality - data->text to columns on 2007 or less (no idea where it is on the ribbon on Office 2010).

Highlight the rows you need to split up, then select the function. Fill in the appropriate bits. If the text is fixed width (with spaces at the end to keep fields where they should be) Excel will make a guess based on the data there, but you can add new columns or delete others at the appropriate step in the process.

Mirno

Good point, the text-to-columns functionality is extremely useful. I think it's still in the Data tab in newer versions of Excel.

Agrajag

peaceandfreedom wrote: »

Good point, the text-to-columns functionality is extremely useful. I think it's still in the Data tab in newer versions of Excel.

It is. Just to the right of middle