How to import this data set into excel? (column headings on each row delimited by a colon)
- by Anonymous
I'm trying to import the following data set into Excel. I've had no luck with the text import wizard. I'd like Excel to make id, name, street, etc the column names and insert each record onto a new row.
,
id: sdfg:435-345,
name: Some Name,
type: ,
street: Address Line 1, Some Place,
postalcode: DN2 5FF,
city: Cityhere,
telephoneNumber: 01234 567890,
mobileNumber: 01234 567890,
faxNumber: /,
url: http://www.website.co.uk,
email: [email protected],
remark: ,
geocode: 526.2456;-0.8520,
category: some, more, info
,
id: sdfg:435-345f,
name: Some Name,
type: ,
street: Address Line 1, Some Place,
postalcode: DN2 5FF,
city: Cityhere,
telephoneNumber: 01234 567890,
mobileNumber: 01234 567890,
faxNumber: /,
url: http://www.website.co.uk,
email: [email protected],
remark: ,
geocode: 526.2456;-0.8520,
category: some, more, info
Is there any easy way to do this with Excel? I'm struggling to think of a way to convert this to a conventional CSV easily. As far as I can think, I'd have to remove the labels from each line, enclose each line in quotes, then delimit them with commas. Obviously that's made a little more difficult to script though seeing as some fields (address, for instance) contain comma-delimited data. I'm not good with regex at all.
What's the best way to tackle this?
