[seanb]

I have a lot of data in an Excel worksheet, and I want to use it as the data source for an NSTableView. I've been trying to export the worksheet as XML for the last hour or so, and I'm getting frustrated. I figured I should make sure I'm going down the right path before I waste any more time on it...

If you can't tell, I've never tried to do anything like this before. I'm trying to display the data in an NSTableView, and each row will have an entry for a file name, and clicking on the row will display an the image with that file name in an NSImageView.

I'm about ready to pull my hair out. I can't figure out how to get the data from excel into the NSTableView. Any suggestions? What file format should I try to use as my data source?

Thanks guys (and girls!)

Sean

[DaGuy]

The approach seems sound. Do you know for a fact that there's a way to get XML out of Excel? I fired it up and didn't see an XML option anywhere. Perhaps my version is too old.

Anyway, If not XML, then I would chose CSV (comma delimeted). It's the simplest.

[seanb]

Hey, thanks for the reply. I've found a VB macro to export a table as XML, and I'm having a hell of a time getting it to work:

http://www.freevbcode.com/ShowCode.a...amp;NoBox=True

I've been looking for a quick way to convert a comma or tab delimited file to a xml file, but I'm getting nowhere fast.

Can I use -initWithContentsOfFile to pull a comma or tab delimited file directly into an NSArray? That would be perfect. I'm considering trying, but I'm not going to let myself. It's almost 3AM, and work starts in about 6 hours... it's time for bed.

[Angus_D]

Yes, tab or comma delimited is probably the easiest solution. Then you could just split it into lines with componentsSeparatedByString:yourlinebreakchar and then into columns using componentsSeparatedByString:tab or comma. Note that this might become expensive for really huge data files.

[DaGuy]

I'm a Cocoa beginner but one thing I quickly realized is that there's more than one way to anything in Cocoa.

For this kind of task, I would read the CSV file into an NSString using its "initWithContentsOfFile" method, and then use NSScanner to get the stuff in between the commas.

Another approach is to remember that Cocoa is extended C, so you could look around for some C code that handles CSV and try to use that.

[DaGuy]

Angus_D's solution is pretty straightforward. NSScanner is very flexible but it's a bit of a bear to use.

[ambush]

overall, NSScanner is a bit trickier but componentsSeparatedByString: is easy but slow when it involves working with large strings.

[Angus_D]

Originally posted by ambush:
overall, NSScanner is a bit trickier but componentsSeparatedByString: is easy but slow when it involves working with large strings.

componentsSeparatedByString: only becomes a problem if you're calling it repeatedly on large strings, since it involved the allocation of a lot of individual strings and an array to hold them in (so basically you'll be using a big chunk of memory). If you keep those around it might be faster than repeatedly scanning the string. Of course, if you're working with large amounts of data, you'd probably be better off with some database library.

There are lots of ways of approaching this sort of thing, it all depends what you're trying to do.

[seanb]

Thanks for the help so far everyone. I finally got a chance to spend some more time on it, and I've made some progress. I'm just going to read the tab delimited file into the table view once and leave it there, so I decided to take the easier componentsSeperatedByString route. Here's the code I'm using (I found some of it on the cocoa.mamasam.com archives):

Code:
- (void)awakeFromNib
{
	// which file?
	NSString *pathToFile = @"/Users/sean/Desktop/database.txt";
	NSString *fileContents = [NSString stringWithContentsOfFile:pathToFile];

	// convert the string
	int i;
	NSArray *tempArray = [fileContents componentsSeparatedByString:@"\r"];
	dataArray = [NSMutableArray array];
	for(i=0; i<[tempArray count]; i++)
	{
		NSString *tempString = [tempArray objectAtIndex:i];
		[dataArray addObject:[tempString componentsSeparatedByString:@"\t"]];
	}

	NSLog(@"%d records",[dataArray count]);
}

// that gives me an array of arrays (right?), each
// secondary array has 8 items, one for each
// column

// then for my data source methods:

- (int)numberOfRowsInTableView:(NSTableView *)aTableView
{
	return [dataArray count];
}
- (id)tableView:(NSTableView *)aTableView
    objectValueForTableColumn:(NSTableColumn *)aTableColumn
    row:(int)rowIndex
{
	int columnID;
	// the column identifiers are 0 through 7
	columnID = [[aTableColumn identifier] intValue];
	//NSLog(@"the column ID is: %d",columnID);
	return [[dataArray objectAtIndex:rowIndex] objectAtIndex:columnID];
}

It's almost working. dataArray (the main array of arrays) has 285 records, just like it should. When the app launches, the tableView loads the data initially, but it logs the error "-[NSCFString objectAtIndex:]: selector not recognized." Maybe one or more of the objects in the dataArray is an NSString(?). Can anyone see anything else wrong with the code?

[seanb]

OK, I caught my error. The line:

dataArray = [NSMutableArray array];

should be:

dataArray = [[NSMutableArray alloc] init];

the array was autoreleasing, then when I resized the window, it would be referenced again by the table view, and that was causing my app to crash. It works now, thanks again everyone.

edit: removed accidental redundancy

[seanb]

For anyone who's been following this, I replaced the lazy

NSString *pathToFile = @"/Users/sean/Desktop/database.txt";
NSString *fileContents = [NSString stringWithContentsOfFile:pathToFile];

with this:

Code:
	NSString *pathToFile;
	NSString *fileContents;
	NSBundle *aBundle = [NSBundle bundleForClass:[self class]];

	if (pathToFile = [aBundle pathForResource:@"database" ofType:@"txt"]) {
		fileContents = [NSString stringWithContentsOfFile:pathToFile];
	}

So, if there's a file called database.txt in the Resources folder in the xcode project, it's read into the NSString fileContents.