Convert Excel doc to FileMaker

[Rangoon]

I have an Excel document of music that I want to import into Filemaker 8.5 Advanced. The Excel document was created from a Word 11 document where each word or number is in a separate cell.  I have successfully imported the Excel document into Filemaker.

 

I now need to figure out how to convert all these individual fields into a manageable database and I can't do it manually because there are thousands of them.  What I have right now is 

 

5th Dimension Wedding Bell Blues1

Abba Chiquitta4

Rolling Stones Can't always get what you want4

Spanky and the Gange Sunday Will Never Be The Same2

 

 

As you can see some Artists have one name like “Abba”, some have two names like “Rolling Stones” while others have more like “Spanky and the Gange”.

 

The same is true of the song titles, some have one name like “Chiquitta”, while others have multiple names like “Sunday Will Never Be The Same”.

 

You will notice that each of the song titles have a number after them and these are part numbers that run from 1 to 25 and ARE NOT separated by a space, they are currently just as they are shown above.

 

What I want to do in Filemaker is get all of the “Artists” name/names into one field and all of the “Song Titles” names into one field and each part number into one field.  So i'll need 3 fields Artists, Song Titles and Parts.  If stripping the part number from the end of the song and put into a separate field is a problem, just leave it as it is.

 

Can you tell me how to do this in Filemaker 8.5 advanced for a Mac?  I would also like to know if you know of a free or for purchase music solution.  It would be great to find something that is already made that I can make changes to as time goes on.

 

Thank you

[Lee Smith]

There a couple of ways to convert your excel file. You should read about the import and export file options. If you use the exports in excel, you can choose Merge or Tab Delimited options.

 

Sometimes, the quickest way is to just dropping the file on the icon of FileMaker 8.5 when it is opened. When the file is opening up, it asked you if the first row is field names or data.

 

Anyway, read about imports and exports and file formats.

[Rangoon]

I read about importing and accomplished that without a hitch but have been unable to figure out how to convert 12 fields into 2 fields. What I have is as shown below.

 

5th Dimension Wedding Bell Blues1

Abba Chiquitta4

Rolling Stones Can't always get what you want4

Spanky and the Gange Sunday Will Never Be The Same2

 

 

As you can see some Artists have one name like “Abba”, some have two names like “Rolling Stones” while others have more like “Spanky and the Gang” and each word is in its own separate field.

 

The same is true of the song titles, some have one name like “Chiquitta”, while others have multiple names like “Sunday Will Never Be The Same” and again each word is in its own separate field.

 

I want to get all of the “Artists/Group” name/names into one field and all of the “Song Titles” names into one field.  I've not been able to figure out how to accomplish this.

 

I could sure use your help, thanks.

[Aussie John]

I think you are in trouble.

It might be possible if you could get a list of bands and compare it to your lists. This could clean up a high proportion of your data but by the time you had done that a manual approach might be easiest.

 

Go through your list and place a unique character in the gap between your band and song then use a a calculation to extract the data.

 

I think it would take way less than a second to add an asterisk to the string. = 10 mins per 1000 records.

 

You wouldn't even need to be overly accurate in relation to the space as that could be sorted in a calculation.

[Lee Smith]

I could sure use your help, thanks.

I was expecting the names to be in separate cells? Can you attach an example of the excel spreadsheet?

[Rangoon]

 

Hi Lee,

 

Here’s what I did.  

 

•  created the initial information with Word 2011

•  went to Save As in Word and created a “Plain Text (.txt) doc.

•  took the plain text doc and dropped it on the Excel icon which created 12 cells.

•  took the Excel doc and dropped it on a FMP 12 Advanced icon which created 12 fields

 

What I now have is a FMP 12 doc with fields labeled 1 through 12.  The first fields are the artist name and/or group and the other fields are the  name of the song. 

 

Let me know and I can send you any of the documents listed above.

 

Thanks Lee

 

[Lee Smith]

You can attach the file here if you are still needing help.

 

Just make a Reply using the "More Options" and then click on the "Choose File" and then select the file on your HD (It needs to be zipped) and then "Attach this File" and then add the post.

[Aussie John]

Unfortunately from your description you still can't separate the song from the artist.

[Rangoon]

Thanks Lee and here is my zipped file,

 

I sure hope there is a way to handle this situation because it is something I'll be adding to on almost a weekly basis. I don't know if you're familiar with GarageBand but that is where I generate the actual song loops.

 

Thank you so very much for your help.

Loops .fmp12.zip

[Lee Smith]

I meant the original data in it's original format.

[Rangoon]

Sorry I got it wrong, here is the original Word 11 for Mac doc.

Loops.zip

[Lee Smith]

This isn't working.

 

I meant the excel file so I can see the cell data, not the output to word.

 

I'm on my way to an appointment and will not be back until late today. If you post it while I'm out maybe some else will jump in.

[steigrafx]

You might want to consider taking Aussie John's advice.  If you open the Word doc, you can do a find and replace for each artist.

 

I downloaded your file and very quickly got through seven pages.  For example, there are 89 instances of Bee Gees songs.  I replaced Bee Gees with Bee Gees** in no time at all.  The ** acts as a separator that you can use in Filemaker to break the contents of a single field into two fields, artist and song.

[Rangoon]

Here's the Excel Doc.

 

I need to think a bit more on the;

 

"I replaced Bee Gees with Bee Gees** in no time at all.  The ** acts as a separator that you can use in Filemaker to break the contents of a single field into two fields, artist and song."

 

If I understand this correctly, perform a find for each artist like Bee Gees and place ** into each of the artist fields (1 to 5).  Then in the song fields do the same thing using something like !!.  

 

Then write a calculation to join the artist into one new field and then all of the Song fields into another new field,  Is that right?  

 

If I'm reading things correctly there is not a more seamless way of doing this.  Unfortunately BarageBand does not have an export protocol and once a "Loop" is made you cannot send it to iTunes that does allow more import/export options.

 

If you guys have any other thoughts after seeing the Excel doc please let me know.  Thanks a lot

 

 

Loops Excel doc.zip

[Lee Smith]

Hi

 

Many of us here have been parsing data for years. The reason I kept asking for the original data sample was there is often keys not seen by which you can separate the data.

 

I looked at all of your data samples, including today's excel spreadsheet, and I'm puzzled by how and why these names became separated?

 

Yes, I know, this is what you have been saying all along, however, somewhere this data resides as you desire, i.e. without the spaces.

 

Can you explain how the excel spreadsheet come to be?

 

Are you scraping a site or something?

[Aussie John]

Firstly DONT convert to Excel until you have made the changes.

As Stelgrafx suggested find and replace will help clean up 75%

 

You only need the unique character(s) in one position

eg

Bee Gees*Staying Alive4

When you convert to excel you need only ONE cell. Don't split into a cell for each word as per your example. (no idea how you managed cells for each word)

 

Filemaker can do the rest.

Probably better to repost once you have got that far but basically you create three new calculation fields for Artists Song and Number.

 

Using the Position function determine where the unique character and use the result in Left, Middle and Right functions to extract the data.

[Rangoon]

All of this data comes from GarageBand and as I said earlier, GarageBand has no export routine.  I got it  from the Mac Library folder (Library>Audio>Apple Loops>User Loops>SingleFiles).  I highlighted all of the Loops and copied them and pasted them into the Word doc and Word formatted them just as they are in the folder.

5th Dimension Aquarius1.aif.  

 

I don’t know of anyplace that the text is available “without the spaces”  

Can you explain how the excel spreadsheet come to be?

 

 

•  created the initial information with Word 2011

•  went to Save As in Word and created a “Plain Text (.txt) doc.

•  took the plain text doc and dropped it on the Excel icon which created 12 cells.

•  took the Excel doc and dropped it on a FMP 12 Advanced icon which created 12 fields

 

I hope this helps

[Rangoon]

Hi Aussie John,

 

I’m confused by exactly what it is you’re telling me to do with the *.  Where do I put them and then how do I get the properly separated words into 2 fields with proper spaces?  (I don’t need the third field.) 

 

I think maybe I’m confused by the different number of words for each song like I show below.

 

First words are always Artists as shown below;

Abba 1 word

Bee Gees 2 words

Brooks & Dunn 3 words 

 

After the “Artist” is Song Title and they can range from 1 word as in “Chiquitta” to 9 words as in “Did You Ever Have To Make Up Your Mind3.  Can be as many as 10 words in the song title.

 

Are you saying I should put an * after each word?  If the answer is yes, then what do I do once that is completed, getting all this into Filemaker in two fields?

 

I really appreciate you guy’s willingness to help me with this, thank you!

[Aussie John]

All we want is one field with a "unique" identifier between the band and the song title. I am suggesting use "*".

 

You need to add the "*" between the band name and the song name, Only ONE "*"

e.g.

Band name with as many words as you like*Song name with as many words as needed

 

Abba*Fernando4

Abba*Dancing Queen4

Bee Gees*Staying Alive6

etc

 

Once you have the text document set up like this then import/turn into Filemaker (you can import direct from TXT without going through excel)

You should then have one field and many records. Change the field name to "ImportString".

 

Create a Calculation field "Band name" with following

 

Let([

Pos=Position(ImportString;"*";1;1)];

Left(ImportString;pos-1)

)

 

Create a Calculation field "Song name" with Following;

Let([

Len=Length(ImportString);

Pos=Position(ImportString;"*";1;1)];

Middle ( ImportString ; pos+1 ; len-pos-1 )

)

 

Create a Calculation field "Song number"

Right(ImportString;1)

 

Basically the calculation uses the "*" to determines  the number characters until the "*" is reached - POSITION and LENGTH Functions.

LEFT, MIDDLE and RIGHT then extract the rest.

 

Your example has multiple versions of the same song ( 1,2,3,4 etc) so if you dont need them, search the SONG NUMBER field for numbers >1 and delete ALL to remove the duplicates.

 

 

 

Bands.fmp12.zip

[Rangoon]

Hi Aussie John,

 

Wow I never expected the information and a solution to boot!  This works perfectly and its great to know I can now make all the changes to my song document and import them into FileMaker.

 

Thanks again Aussie John and to all of you who were kind enough to lend me assistance!

[Rangoon]

Hello again Aussie John,

 

Maybe you would not mind helping me with one more thing and that is to eliminate the "Song Number" field and allow the song numbers to appear after the song just as if it were part of the name of the song.  Right now on my TXT doc it is immediately behind the Song Name and is not separated by a space.

 

BTW I know I can perform a find but can I change a Band Name or Song Name? 

 

Thank you

[Rangoon]

I can keep the Song Number separated as the file currently shows but I need your help. 

 

The calculation for the field “Song Number” is  Right(ImportString;1)

 

What happens with this calculation is it moves 1 through 9 just fine.  But 10 through 19, (as shown below) leaves the “1”.

 

Can you tell me how to change the calculation so it will move both numbers?

 

BAND NAME    SONG NAME           SONG#

Bobby Pickett   The Monster Mash      1

Bobby Pickett   The Monster Mash      2

Bobby Pickett   The Monster Mash      3

Bobby Pickett   The Monster Mash      4

Bobby Pickett   The Monster Mash      5

Bobby Pickett   The Monster Mash      6

Bobby Pickett   The Monster Mash      7

Bobby Pickett   The Monster Mash      8

Bobby Pickett   The Monster Mash      9

Bobby Pickett   The Monster Mash1    0

Bobby Pickett   The Monster Mash1    1

Bobby Pickett   The Monster Mash1    2

Bobby Pickett   The Monster Mash1    3

Bobby Pickett   The Monster Mash1    4

Bobby Pickett   The Monster Mash1    5

Bobby Pickett   The Monster Mash1    6

Bobby Pickett   The Monster Mash1    7

Bobby Pickett   The Monster Mash1    8

Bobby Pickett   The Monster Mash1    9

[Lee Smith]

Numbers in text do NOT sort as you might expect. That was why it was better that you had a second field with the numbers.

 

Then sorting on the two fields would have provided what you want.

 

If you didn't delete that field, you can still include it in a sort.

[Rangoon]

Hi Lee,

 

Using the solution I downloaded from Aussie John, if I delete the Song Number calculation I lose the song numbers completely.  

 

I really don’t care if the song numbers are in there own field or immediately after the Song Name,  I just need them listed correctly, as in my last post 1 through 19. 

 

Right now the way Aussie John’s solution is set up I end up with any Song Number from 1 to 9 displaying correctly, but any Song Number that is greater than 9 (10 and above) displays incorrectly.

 

Here’s what I mean,

Bobby Pickett   The Monster Mash     9 (correct)

Bobby Pickett   The Monster Mash1   0 (incorrect)

 

As you can see with the (10) number #1 stays with the word “Mash” while  “0” is shown in Song Number.  I either need both number in the Song Number field or the Song Number field deleted and both numbers are at the end of the Song Name. 

 

Thanks Lee

[Aussie John]

When I originally flicked through the word song list I missed that there may be titles with >9 at the end.

 

To fix this, I am re uploading a solution which covers up to 99 at then end. You will need to create a new calculation which determines the length of the number (or if there isn't one).

 

I also added trim functions to the band and song names just in case spaces lurk at the ends.

 

Hope this works. I should point out that this solution cant deal with a song which does feature a number at the end such as the Beatles Revolution 9. You might need to add a space after the actual song name and before the tail number.

cheers John

 

 

Bandsv2.fmp12.zip

[Rangoon]

Hi Aussie John,

 

Thank you so much, that is perfect and how great it is when something comes all together!  Would you answer one more question for me.  The field "Band Name, Song Name and Song Number" are calculation fields and I am unable to modify them. 

 

Is there a way to also show these three fields as Text fields so they can be modified? Or should I make any changes through the "ImportString" field which is a Text field?  I don't want to add a lot of unnecessary bulk to my database so if making changes through the "ImportString" is how you would do it, thats fine with me. 

 

I don't see there being many changes, I'd just like to know what you think about handling the changes through the "ImportString"?

 

Again, thank you - thank you- thank you!

[Aussie John]

You can do this.

First go into define fields

 

Select the field song name - enter options and Change calculated result from Number to text ( that was an error from earlier).

Open Storage options and ensure storage is set to "do not store"

Do the same for Band name.

Close the whole dialog ( not sure if you need to do that).

 

Reopen define fields

For both fields change the type to Text and enter options - In Auto calc entries check "calculation" and check "do not replace value.."

 

Your calculation should automatically cross over.

 

Close back to database . You current fields should look correct. - if not rebake.

 

If for any reason band and song title have become empty just turn back into a calculation and the formula will return. Means you probably need to change the storage.

 

Since 'do not replace' value was checked you can only enter data into the importstring field once or you will need to delete the results and try again.

 

 

 

Now each time you create a new record and add the string (or import) the field will populate with editable data.

 

 

 

 

 

cheers