Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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@address.com,
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@address.com,
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?

share|improve this question
    
is the structure always the same? Are there always 14 fields in the same order? –  nixda Jun 28 '13 at 10:11
    
Yep, always 14 fields in the same order. –  Anonymous Jun 28 '13 at 10:47
    
why not open it from the text file, then use Copy> PasteSpecial - Transpose to transpose the data –  Philip Jul 1 '13 at 8:58

2 Answers 2

up vote 1 down vote accepted

This macro is working with your example.
(I assume you know how to deal with VBA macros)

Sub ImportDataset()
    strPath = Application.GetOpenFilename()
    Open strPath For Input As #1
    While Not EOF(1)
        Line Input #1, strLine
        If strLine = "," Or strLine = "," Then
            intRow = intRow + 1
            intCol = 0
        End If
        If InStr(strLine, ":") <> 0 Then
            intCol = intCol + 1
            intStart = InStr(strLine, ":")
            strLine = Mid(strLine, intStart + 2)
            strLine = Left(strLine, Len(strLine) - 1)
            ActiveSheet.Cells(intRow, intCol) = strLine
        End If
    Wend
    Close #1
End Sub
share|improve this answer
    
I was too lazy to implement some lines for the header fields, so you have to insert them once on your own. Thats not a big deal. –  nixda Jun 28 '13 at 11:01
    
Thanks nixda, but when I run that I get 'Run-time error'5': Invalid procedure call or argument. If I click debug it highlights the line with strLine = Left(strLine, Len(strLine) -1). Any ideas? –  Anonymous Jun 28 '13 at 12:35
    
Well, with a text file and your example lines above its working for me. Could you upload your input file somewhere? –  nixda Jun 29 '13 at 7:36
    
Was my fault, accidentally forgot to remove some header-text from the full file. Worked perfectly, much appreciated, thanks. :) –  Anonymous Jul 1 '13 at 9:20

Assuming this is a one-time operation:

Use a text editor that has keyboard macros.

  1. Write a macro that moves 'one block' down, from the single comma to the single comma

  2. Go through the document with this macro to make sure all your blocks contain all fields; if not, insert missing fields (lines)

  3. Do a global replace: " to "" in case there's double quotes in your data

  4. Write a new macro that surrounds all lines in a block with double quotes, then concatenates them by removing the newline character at the end

  5. Do the CSV import in Excel

Remarks:

  • Not all steps may be necessary depending on the actual data throughout your file

  • There's variations possible on this (like replacing \n with "\n" to quickly surround all lines with double quotes), depending on your actual data and the capabilities of your editor.

  • You may have to try variations to get the actual CSV import to work in Excel (like maybe try surrounding single quotes)

  • Keep intermediate files for the different edit steps so that you don't have to redo all steps when trying

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.