Convert Excel document (xls) to a plist

Question

I have a pretty straightforward Excel spreadsheet, and I need to use the data in an iPhone app. The xls document has 6 columns, and > 200 rows.

I would like to create a plist from the xls document. How can I convert one to the other, programmatically?

Cyler

Answer 1

I'm late to the party but I built a desktop utility that will convert CSV to a plist. You can download the binary or use this code, which requires cCSVParse. It uses whatever is in row 0 to create key names, then generates dictionaries for each successive row.

    CSVParser *parser = [CSVParser new];
[parser openFileWithPath:pathAsString];
NSMutableArray *csvContent = [parser parseFile];
[parser closeFile];

if (pathAsString != nil)
{

    NSArray *keyArray = [csvContent objectAtIndex:0];

    NSMutableArray *plistOutputArray = [NSMutableArray array];

    NSInteger i = 0;

    for (NSArray *array in csvContent)
    {



        NSMutableDictionary *dictionary = [NSMutableDictionary dictionary];

        NSInteger keyNumber = 0;

        for (NSString *string in array)
        {

            [dictionary setObject:string forKey:[keyArray objectAtIndex:keyNumber]];

            keyNumber++;

        }

        if (i > 0)
        {
            [plistOutputArray addObject:dictionary];
        }

        i++;

    }

    NSMutableString *mutableString = [NSMutableString stringWithString:pathAsString];
    [mutableString replaceOccurrencesOfString:@".csv" withString:@".plist" options:nil range:NSMakeRange([mutableString length]-4, 4)];

    NSURL *url = [NSURL fileURLWithPath:mutableString];


    [plistOutputArray writeToURL:url atomically:YES];

Sclerenchyma

Thanks for this. How is the binary supposed to work? Paste in a path? Paste in the actual csv? I've not had success with either. – Deviation

I found your blog post noting that you paste in a path. Alas, no conversion for me. – Deviation

Love love LOVE that conversion tool. Just drag-and-drop the file in from the Finder. DONE! – Gherardo

It works nice, but has anyone noticed it outputs a small subset of the data if the csv is too large? Perhaps a special charachter would cause early termination? – Agamemnon

if i convert it converts in to the alphabetical order ABCD........., is not it possible to convert same order the file is in?? – Enthronement

@MJesse I think the max number of records it supports is around 200, at least in my case, it seems like that. – Calvincalvina

drag and drop from the finder and it uses the path of the file. Alternatively I guess just type in the absolute path. – Rachealrachel

Answer 2

You could do this using a simple formula that you copy and pasted down a column beside each of your 200+ rows.

For example, assuming colum A contains a list of names, and column B contains a matching set of ages you could use a formula such as the following to end up with most of the XML for a plist based dictionary.

=CONCATENATE("<key>Name</key><string>", A1,"</string><key>Age</key><integer>",B1,"</integer>")

You then select all the cells within this new column you can copy and paste into notepad or another text editor to save it as a plist file (you may want to put some hardcoded text into a cell above and below your 200+ rows, in order to get the required tags etc as well...

Whittington

Without a full-fledged script, this approach will definitely save some time. Thanks. – Cyler

this was faster than writing a script for me too. "Excel: the poor man's scripting language" :D – Alehouse

Very nice, quick and easy :-) – Pigtail

Cool idea, although I was looking for something else. :) – Jarry

This was a lot faster than writing a script to do this. Also, the actual 'Answer' didn't work for me but this did. Thank you. – Pentup

Answer 3

Ladies and gentlemen,

I tried any other recommended solutions above but because of Unicode characters in my language (Turkish) none of them worked out for me... All unicode characters were all broken. Then I decided to make a tool for this.

I proudly present the simplest way to convert any XLS or XLSX or CVS file to a plist:

http://exceltoplist.herokuapp.com/

Just upload your XLS, XLSX or CSV and download your Apple Plist!

Enjoy!

Note: Because of Heroku's free dyno policy it might take a few moments to browse the page. Just keep waiting for 5-10 seconds to open page.

Towelling

Nice work. This seems like a great replacement for the mindsizzlers converter which seems to no longer be working. – Worked

Nice to hear you liked it :) Thanks – Towelling

Answer 4

For OpenOffice, use this formula

=CONCATENATE("<key>number</key><integer>"; A2;"</integer><key>MyString</key><string>";B2;"</string>")

Spooky

Answer 5

I found the CONCATENATE to work the best for this.

Thiosinamine

Can you give a short example? Your answer is not that helpful as it stands. – Macaulay

this should be a comment on the previous answer – Faucher

Answer 6

For my purpose I just need to convert CSV with two columns to plist file. First column is keys and second are values. So, I slightly change Danilo Campos code as following:

CSVParser *parser = [CSVParser new];
[parser openFileWithPath:pathAsString];
NSMutableArray *csvContent = [parser parseFile];
[parser closeFile];

if (pathAsString != nil)
{
    NSMutableDictionary *plistOutputArray = [NSMutableDictionary dictionary];

    for (NSArray *array in csvContent)
    {
        NSString *key = (NSString *)([array objectAtIndex:0]);
        NSString *value = (NSString *)([array objectAtIndex:1]);

        [plistOutputArray setObject:value forKey:key];
    }

    NSMutableString *mutableString = [NSMutableString stringWithString:pathAsString];
    [mutableString replaceOccurrencesOfString:@".csv" withString:@".plist" options:nil range:NSMakeRange([mutableString length]-4, 4)];

    NSURL *url = [NSURL fileURLWithPath:mutableString];

    [plistOutputArray writeToURL:url atomically:YES];
}

P.S. You can find his initial source code here - http://code.google.com/p/danilobits/source/checkout Please note that to get his code work now you need to change "Base SDK" to "Latest OS X"

Miocene

This is exactly what I need to do. First column keys, second column values. Is there an online converter that will do this? Or is there maybe a binary I can just download and use? – Worked

Answer 7

Use http://shancarter.github.io/mr-data-converter/ to convert xls to a Json(just copy & paste)(can re format it by remove white space in http://jsonviewer.stack.hu/). save json to text file named: in.json.

Use plutil command to format json to plist

plutil -convert xml1 in.json -o out.plist

Herrick