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?

score 47 · Accepted Answer · answered Dec 05 '09 at 06:49

47

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];

answered Dec 05 '09 at 06:49

Danilo Campos

6,430
2
25
19

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. – Matthew Frederick Nov 22 '10 at 10:08
I found your blog post noting that you paste in a path. Alas, no conversion for me. – Matthew Frederick Nov 22 '10 at 10:24
1

Thanks for this - that's a pretty useful tool! – lxt Jan 22 '11 at 17:37
Love love LOVE that conversion tool. Just drag-and-drop the file in from the Finder. DONE! – Joe D'Andrea Jan 06 '12 at 20:05
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? – M Jesse May 27 '12 at 07:40
if i convert it converts in to the alphabetical order ABCD........., is not it possible to convert same order the file is in?? – SameSung Vs Iphone Dec 08 '12 at 08:03
@MJesse I think the max number of records it supports is around 200, at least in my case, it seems like that. – sonoshin Dec 14 '12 at 11:21
drag and drop from the finder and it uses the path of the file. Alternatively I guess just type in the absolute path. – Max MacLeod Sep 19 '13 at 10:57

score 39 · Answer 2 · answered Oct 23 '09 at 03:02

39

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...

answered Oct 23 '09 at 03:02

Christopher Fairbairn

1,401
10
10

1

Without a full-fledged script, this approach will definitely save some time. Thanks. – John Muchow Oct 23 '09 at 13:58
1

this was faster than writing a script for me too. "Excel: the poor man's scripting language" :D – unsorted Jul 02 '10 at 19:47
Very nice, quick and easy :-) – Chris Jan 29 '11 at 23:11
1

Cool idea, although I was looking for something else. :) – Dev Kanchen Mar 06 '12 at 22:02
2

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. – Johnny Sep 18 '12 at 18:14

score 26 · Answer 3 · edited Jun 20 '20 at 09:12

26

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 11 '14 at 10:34

scaryguy

7,720
3
36
52

1

Nice work. This seems like a great replacement for the mindsizzlers converter which seems to no longer be working. – zeeple Jan 22 '14 at 18:18
Nice to hear you liked it :) Thanks – scaryguy Jan 23 '14 at 09:31

score 4 · Answer 4 · answered May 04 '13 at 10:46

4

For OpenOffice, use this formula

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

answered May 04 '13 at 10:46

Swaroop S

490
5
11

score 1 · Answer 5 · answered Dec 26 '13 at 19:42

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"

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? — zeeple, Apr 16 '15 at 04:56

score 1 · Answer 6 · answered Apr 20 '15 at 02:31

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

score 1 · Answer 7 · answered Oct 26 '11 at 01:31

1

I found the CONCATENATE to work the best for this.

answered Oct 26 '11 at 01:31

Steve B

21
1

Can you give a short example? Your answer is not that helpful as it stands. – Matt Wilko Oct 26 '11 at 10:40
1

this should be a comment on the previous answer – bkbeachlabs Apr 25 '12 at 01:48

Convert Excel document (xls) to a plist

7 Answers7

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

http://exceltoplist.herokuapp.com/

Linked