Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[I_want_an_Omega]

Hi,

I've been given some data in an Excel spreadsheet to play with - and on closer examination it's in rows - each having the form:

Date;Time;Field1;Field2;Fieldn;
I'm assuming this is in a CSV form, with the ";" being the field separator?

My question is - how do I access an individual Field - or even place each field into a separate column?

I'm sure there is a simple answer to this but a look at the inbuilt help has only confused me further.

Thanks - Rob

[aaronjb]

Is the file really an Excel file, or is it actually a plain text CSV file that Excel isn't automagically recognising as such? (Open it in Notepad and have a look - if it looks like normal text then it's the former, any gibberish and it's the latter).

If it's the former then assuming you're double clicking it to open in Excel right now - stop doing that Open Excel then open the file from within Excel and it should (assuming anything other than a .xls/.xlsx extension) ask you what to do with it. Choose Delimited, click Next, select Semicolon and uncheck Tab, select next, set column types if necessary then click Finish.

Et viola. Now save as a real Excel file.

[tunnie]

Import that CSV, specifying ";" as the space

Info here: http://www.brighthub.com/computing/windows-platform/articles/10782.aspx

To import this back into Microsoft Excel 2007, click on Data, then click the From Text button in the Get External Data menu. This will bring up the Text Import Wizard.
In the first step, make sure you have Delimited set as the first option. You can also select which row you would like to start from. In this example, I’m doing the whole thing starting with the first row.

In the second step, make sure you check the box next to Comma. (See screenshot for an example.) This will then show your data in columns at the bottom. If you click the check next to Space, it will divide the address column into two lines, in this example. Sometimes this can be helpful when you are trying to break down data during the import, as some programs tend to lump data together.

The third step simply lets you choose the format of the data, such as numbers, text, or dates. After you’ve done that, just click Finish and the data will be imported into your spreadsheet. Depending on how big the .CSV file is, the import process could be instantaneous or take several minutes. I’ve seen giant database exports pulled into Excel that took a while to load, but they still worked just fine.

[I_want_an_Omega]

Thanks guys - will try when I get a few spare mins ...............

[TheBoy]

Excel is a tool by OW's for OW's.

I avoid at all times

[I_want_an_Omega]

Go on then, OW = ?

[TheBoy]

Go on then, OW = ?

Not sure the word censor will allow the W bit, but here goes...  ...Office self-abuser

[TheBoy]

Oh, word censor did allow it 

[TheBoy]

Oh, word censor did allow it 

Word censor fixed...

[CaptainZok]

Merchant Banker surely.

[TheBoy]

Merchant Banker surely.

5 points to the Honourable Gentleman from Bolton.

[CaptainZok]

It's rude to point, I'll take 5 blunts instead.

[I_want_an_Omega]

Dont you like any Office components then?

[TheBoy]

Dont you like any Office components then?

They have a place. Except Excel and Powerpoint, both, as said, designed by OWs for OWs