Large Product Import

[Guest]

Hi all,

 

I need a little bit of help as I am still a little bit new to this. I am currently making an online store and want to be able to take my database from Filemaker with all my products etc that we have and import it into OSC.

 

How do I go about doing this so then all we have to do is update our prices and etc on Filemaker and then one or twice a week we just export the filemaker document and then import it into OSC.

 

Can anyone please provide some help with this - the uickest and best option would be appreciated. Or can someone let me know if this can not be done.

 

Cheers :)

[mi_jaiten]

Hi all,

 

I need a little bit of help as I am still a little bit new to this. I am currently making an online store and want to be able to take my database from Filemaker with all my products etc that we have and import it into OSC.

 

How do I go about doing this so then all we have to do is update our prices and etc on Filemaker and then one or twice a week we just export the filemaker document and then import it into OSC.

 

Can anyone please provide some help with this - the uickest and best option would be appreciated. Or can someone let me know if this can not be done.

 

Cheers :)

Try one of these http://www.oscommerce.com/community?contri...er&category=all

[cssystems]

yes, you need to install easy populate, this is a osc contribution and can be downloaded and easilly installed, you should be fine just exporting the database as a csv file from filemaker and uploading it through easy populate.

 

Hope this helps !!

[Guest]

Hi and thanks for the reply. I have since installed Easy Populate.

 

When I click the link "Download Complete tab-delimited .txt file to edit" I get a txt file and when I open it up it looks much different to what is from a CSV export from filemaker. Here is a little sample of what came from FileMaker:

 

"27.34","AMUNIMACR63","Mac Monitor Adapter / UNIMAC-PLUS","Mac to VGA adapter - Supports 'on the fly' resolution switching.

UNIMAC VGA ->Macintosh Monitor Adpater","Monitors - Adaptor","Both","Retail",""

"179.96","IOM3225R1","Iomega Zip USB 100MB -USB powered Drive","Includes drive, USB cable, drive side stand, Fun disk Stickers, Solutions CD containing Iomegaware software, HTML users manual and additional apllications, and Quick Install Quide.","Storage Devices - Removable Storage","Both","Retail",""

"66.38","99-00696-07R2","AsanteFAST 10/100 Ethernet card for PowerPC","AsnateFAST 10/100 card for PCI PowerPC","Networking - Cards/Adapters","Mac","Retail",""

"42.75","M3020-KSR22","Kensington ADB Orbit Trackball","The ADB Orbit Trackball from Kensington is the only answer if you are need a trackball for an older Macintosh computer. Unlike a mouse, with a trackball you actually move the ball itself with your fingertips. The ADB Orbit Trackball also has a button located on each side of the trackball allowing both left and right handed people easy access to a button.

* Easy fingertip control offers natural, precise movement and orientation

* Symmetrical shape works for left- and right-handed users, all hand sizes

* Natural wrist support eases fatigue

* Customizable buttons give single-click solutions to repetitive tasks

* Available for ADB Macintosh (pictured) & USB Macintosh (Bondi Blue Buttons)

MANUFACTURER NAME: Kensington

WARRANTY: 1 yr","Accessories - Mouse/KB/Game","Both","Retail",""

"97.88","KB1010-SMR22","ADB Extended Keyboard","ADB Extended Keyboard

The ADB Extended Keyboard is the product to replace a keyboard from an older ADB equipped Macintosh computer. ADB ports on the side allow you to plug your mouse into the keyboard.

? Trickle power startup key

? Full Apple keyboard layout (including Command key)

? Beige colour to match older ADB equipped computers

? Num Lock, Caps Lock & Scroll Lock LEDs

","Accessories - Mouse/KB/Game","Mac","Retail",""

"205.95","IOM0014R1","Iomega Zip 100MB PC/Mac Disk","IOMEGA: Zip PC/MAC 100MB Ten-Pack in Sealed Paper Sleeves

Zip disks 100MB - Colour singles PC/MAC Code IOM0025 (20.50 each)

","Storage Devices - Media","Both","Retail",""

"169.95","SYM0133R1","Norton Utilities 8.0 for Mac","Optimize performance and solve problems easily with Norton Utilities for Macintosh 8.0, the world's best-selling product for Mac? disk repair, disk optimization, and data recovery. Norton Disk Doctor? finds and repairs disk problems with a click of a button. Run it from the bootable program CD when your hard drive won't restart. Speed Disk? optimizes your drive's performance. UnErase? helps you restore deleted files. And Norton FileSaver? and Volume Recover? allow you to rebuild damaged file directories. Now this comprehensive set of utilities also supports Mac OS X systems with new versions of Norton Disk Doctor, Norton FileSaver, and UnErase.

Mac OS 8.1 or higher (X)

 

Product Information

Symantec's Norton Utilities for Macintosh 8.0 maximizes your computer's performance. It finds and repairs disk problems, and can alert you to potential problems. It can also help you recover files from damaged disks.

Improve hard drive performance by using Norton Utilities to defragment and organize files for fast access.

System Requirements:

MAC OS X

* Mac system with Power PC G3 or higher proc (excluding Beige G3)

* Mac OS X version 10.1.5 or higher

* 128 MB of RAM (required for Mac OS X)

* 40 MB of available HD space

* CD-ROM Drive

* Internet Connection required for LiveUpdate

MAC OS 9

* Power PC-based Mac System

* Mac OS 9.2 or higher

* 24 MB of RAM

* 15 MB of available HD space

* Recommended 10% of total disk space available when optimising the disk

* CD-ROM Drive

* Internet Connection required for LiveUpdate

* Norton Utilities CD will not boot the original PowerBook G3

 

Can anyone please provide me with information on what I could do to get my filemaker database into OSC. I have not yet tried to upload the Filemaker export as I have a big hint that it will not work and possibly stuff other things up.

 

A quick response would be greatly appreciated :)

 

Thanks in advance.

[kirchenbauer]

Are you using Microsoft Excel? The file you exported from Filemaker is in

csv, but the default setting for Easy Populate is txt. There is a way to

change the default in Easy Populate, but it would be just as easy to just

save your exported file from Filemaker to a txt file.

 

Once you save your Filemaker csv file as a txt, then you should be able

to import it into osCommerce using Easy Populate.

 

Best of luck...

Sherry

 

You are going to need the proper column headings in Excel also. If you aren't sure what they

are, then do an export using Easy Populate to a file (if you don't have any products, add a

dummy product first).

 

Also, I noticed that you have some appostrophes in your description. You will need to replace

all of your appostrophes with this: \'

 

So, the word Don't would be Don\'t.

 

In Excel, you can do this easily by going to EDIT

and FIND, then clicking over to FIND AND REPLACE.

Have Excel find the ' and replace with \'

[cssystems]

The problem with filemaker is that when you export the information from the database it doesnt give you your headers for the columns, and everything seems to be a mess. I have found the best way is to import the data through excell and then export as a csv file. This seems to clean the data up alot. Another good practice is to first download the txt file from easypopulate and that should give you your structure on how to have the table in your filemaker. Hope this helps, let me know if you need any more info.

[Guest]

Ok well I exported from Filemaker as a ".txt" file and then opened that file in EXCEL. Everything is in colums and looks nice and tidy. I can't save the file as a .csv. What is my next step from here? I have attached an image of what some of it looks like in excel. I would appreciate help and advice on this.

 

 

Cheers,

Phill :D

[kirchenbauer]

Okay, the file looks good. Here are the next things you need to do:

 

1) Your first row needs the column headings that define what is in that

row. Here is a sample of what the headings look like:

v_products_model v_products_image v_products_name_1 v_products_description_1

 

The easiest way to get the headings is to export using Easy Populate a product

that you have already manually entered into osCommerce.

 

Once you get the headings, then you can match them to the columns.

 

2) I see that you have categories and subcategories in the same cell (I.e. Monitors - Adaptor).

Copy that column to a blank spreadsheet in Exel, then highlight the column, then go to "Data"

and "Text to Columns" in the top menu. When the box comes up, click "Next". Then, select

"other" and put a "-" in the box next to "other". You will see a preview of how it will split your

text into 2 columns. Then, click "Next" and "Finish". Now you have one column with the main

category and one column with the subcategory. You can copy them back to your .txt file now.

 

3) You need to replace your appostrophes:

In Excel, do a "Find and Replace". Have Excel find all of your ' and replace them with \'

NOTE: If you don't do this, then your import will halt as soon as it gets to the record that has

the ' in it.

 

4) Your model number (product number) cannot exceed 12 characters, unless someone has

a work-around for this. You will need to make sure that your model numbers are all 12 or

fewer characters.

 

 

5) Your description can include html, so you can add color, page breaks, etc. if you want to.

 

6) Every row that contains data must have EOREOR in the last column of data.

 

If you want to get your file ready to import, then post it here like you did for the Filemaker,

then we can take a look at it and let you know if we see anything that needs fixed.

[Guest]

Thanks for your help. I want to take this one step at a time and make sure I make no mistakes. So I am sorry if this is a lenghty process etc. But I do appreciate all the help I can get.

 

Where you say

Your first row needs the column headings that define what is in that

row. Here is a sample of what the headings look like:

v_products_model v_products_image v_products_name_1 v_products_description_1

 

The easiest way to get the headings is to export using Easy Populate a product

that you have already manually entered into osCommerce.

 

Once you get the headings, then you can match them to the columns.

Do you mean move everything down in the spreadsheet and at the top of each column put what each column contains? e.g. My first collum contains all the prices for customers would that column at the top say "v_products_price" and that is all?

 

If you go to this link you can download a copy of the whole TXT file I exported from easy populate if this would help at all. http://www.phillracer.com/download/shopdb.txt

 

Thanks and I hope that I am not annoying you with all these questions - it's my first time tyrying to import it from a FileMaker database and I kind of need all the help I can get.

[kirchenbauer]

Yes, insert one row at the top and add the headings.

 

Do you mean move everything down in the spreadsheet and at the top of each column put what each column contains? e.g. My first collum contains all the prices for customers would that column at the top say "v_products_price" and that is all?

Yes, that is exactly what you need to do.

 

I looked at your exported file, too. Looks good. Just take those headings and add them to the first row of your Filemaker file.

 

Be sure to go back and read my other points once you have the headings added.

 

I know what it is like to try Easy Populate for the first time! So don't worry about the questions.

People have helped me and I just try to give back by helping others when they have a problem

that I have encountered and solved.

 

Keep me posted, and ask questions if you need help.

[shanedj]

we us excel import to add 10000 products at a time.

[Guest]

"shanedj" But what are you importing them from and how does it work? Do you have an example or a bit more information for me?

 

kirchenbauer I have now done the headings "I think" are they right? I can't find what the heading would be for the column that is missing a heading it is about if the product is either a retail version or a education version.

 

Here is an image of it. Where to from here? And that table splitter worked a dream (nice tip thanks heaps for that - could help me with other things)

[Guest]

youshould read thru the docs for easypopulate and also the easypopulate support thread.

all contribuitons fall under contribution support, not general support

[kirchenbauer]

Sorry it took so long to reply. I am in the USA and went to bed. It

was 3am here.

 

The spreadsheet looks great.

 

Where is the model number? Just make sure you have that column

because osCommerce requires it. If you don't want to use the same number

that you had in Filemaker, then you can create a model number, even if you

just number them (1, 2, 3....).

 

As far as the retail or education version, how are you using that on your site?

 

Is it an attribute, where the shopper can choose which one they want?

Is it part of the description? Or is it more like another category?

 

If it is part of the description, then I would do a formula to merge the cells.

Let's say you wanted the main description and then a blank line and then

your retail or education version listed.

 

Insert a new column. In that column, put this formula in row 2:

=c2&"<br /><br />"&f2 (NOTE: The columns might be different if you insert the

column before column f.)

 

Then, copy that formula all the way down. Then, save your spreadsheet (to be

safe).

 

Then select the entire column of forumulas (EXCEPT don't include the first row).

Then, click "Copy" and then click row C2 and click "Paste Special". When the

box comes up, select "Values" and click "OK".

 

Your formula should now be just text in column c. You can delete the column

with the retail or education in it and also delete the column that had the formula.

 

If it is another category, then you will want to decide if they should be 3rd level

categories or if you want them to be 1st level categories. If you want them to

be 3rd level categories, then label the column "v_categories_name_3". If you

want them to be 1st level categories, then let me know and I'll explain that one.

 

If it is an attribute where the shopper selects which one they want, then let me

know and I'll explain how to set them up.

 

John (Mibble) is right that this whole topic should be under contributions support,

but I don't know if we are supposed to do anything with the topic at this point.

[kirchenbauer]

Sorry it took so long to reply. I am in the USA and went to bed. It

was 3am here.

 

The spreadsheet looks great.

 

Where is the model number? Just make sure you have that column

because osCommerce requires it. If you don't want to use the same number

that you had in Filemaker, then you can create a model number, even if you

just number them (1, 2, 3....).

 

As far as the retail or education version, how are you using that on your site?

 

Is it an attribute, where the shopper can choose which one they want?

Is it part of the description? Or is it more like another category?

 

If it is part of the description, then I would do a formula to merge the cells.

Let's say you wanted the main description and then a blank line and then

your retail or education version listed.

 

Insert a new column. In that column, put this formula in row 2:

=c2&"<br /><br />"&f2 (NOTE: The columns might be different if you insert the

column before column f.)

 

Then, copy that formula all the way down. Then, save your spreadsheet (to be

safe).

 

Then select the entire column of forumulas (EXCEPT don't include the first row).

Then, click "Copy" and then click row C2 and click "Paste Special". When the

box comes up, select "Values" and click "OK".

 

Your formula should now be just text in column c. You can delete the column

with the retail or education in it and also delete the column that had the formula.

 

If it is another category, then you will want to decide if they should be 3rd level

categories or if you want them to be 1st level categories. If you want them to

be 3rd level categories, then label the column "v_categories_name_3". If you

want them to be 1st level categories, then let me know and I'll explain that one.

 

If it is an attribute where the shopper selects which one they want, then let me

know and I'll explain how to set them up.

 

John (Mibble) is right that this whole topic should be under contributions support,

but I don't know if we are supposed to do anything with the topic at this point.

[Guest]

Hi there,

 

I took out the model number because I could not work out a way of renumbering it easily. Are you sure it will not work unless you have it?

 

And in reply to your retail/education question. I just want it on a second line that says Retail or Education. So pretty much I just want it to say Retail or Education below the description so would I just put "v_products_description_2"?

 

Thanks for your help I hope you can still help me.

[kirchenbauer]

Hi Phill,

 

I am not positive that a model is required. Try adding a product

manually without a model number and see if it works.

 

The "v_products_description_2" is for the second language, so

you can't use it as an extension of the main product description.

 

If what you want to do is have your main description, then a

blank line, and then either "retail" or "education", then what you

need to do is:

 

1) Insert a new column between Column F and Column G. In the new

column, put this formula in Row 2:

 

=c2&"<br /><br />"&f2

 

2) Then, copy that formula all the way down. Then, save your spreadsheet (to be

safe).

 

3) Then, select the entire Column G, which should have your formula that you

just entered and copied down the column (NOTE: don't include the first row).

Then, click "Copy" and then click Cell C2 and click "Paste Special". When the

box comes up, select "Values" and click "OK".

 

4) Your formula should now be just text in Column C. You can delete Column F

(with the retail or education in it) and also delete Column G (with the formula).

 

 

What my instructions are doing is merging the two cells, with a blank line

in between them.

 

It will save you the trouble of manually typing "<br /><br />Retail" or

"<br /><br />Education" at the end of the description for each product.

 

If you follow the instructions, it will make sense as you go along.

[Guest]

Ok so I have done all of the above although I modified it so that it was adding the infomration from H2 as that is now what column the Retail and Education is in. By the way I put the product models back but I haven't had to add them ever before from what I know of so should I just delete?

 

What is the next step from here? What heading do I give that column now?

 

I you want to see screenshots or files let me know.

[kirchenbauer]

1) The column heading should be:

 

v_products_description_1

 

2) I just did a test product and it doesn't require the model number.

I had read something in this forum that said the model number is

a required field. I guess you can leave it out if that is easier.

 

3) If you haven't already done this, you need to replace your appostrophes:

In Excel, do a "Find and Replace". Have Excel find all of your ' and replace them with \'

NOTE: If you don't do this, then your import will halt as soon as it gets to the record that has

the ' in it.

 

4) And if you haven't already done this, every row that contains data must have EOREOR in the last column of data.

 

5) You are ready to upload the file. Make sure you have a subdirectory "temp" under "catalog"

(catalog/temp). That is where Easy Populate will put your excel file when it does the import. Also,

make sure that the "temp" directory has the proper permissions, so that Easy Populate can write

the file to the directory.

[Guest]

When you say put the heading as "v_products_description_1" do yuo mean for both those columns?

 

Here is a screenshot - http://www.phillracer.com/gif/excelview3.gif[/gif]

 

And I will replace my ' with \'

 

Thanks for your help so far kirchenbauer

:)

[kirchenbauer]

You no longer need the column with "Retail" in it because you

merged it with the description column, so Column G can be

deleted.

 

Column F should have the heading "v_products_description_1"

[Guest]

When I delete column G is deletes weither it is Retail or Education off the end.

 

By the way I try to replace ' with "\'" and I get an error that says "Formula is too long"

 

I also have other characters in the description like - * and lots of spaces and that but when I try to replce them it does not work - is their a way to delete things?

[kirchenbauer]

Can you take another look at my instructions?

I think you missed a couple of steps.

 

If you try to import it as a formula, it will probably

import the formula and not the text.

 

So, you still need to:

 

1) Select the column with the description/formula

2) "Copy"

3) Stay in the same column and "Paste Special"

When the window comes up, select "Values"

and click okay.

 

Then, you can delete the Column G.

 

Once you do the above, see if it resolves your error

that the "Formula is too long". If it doesn't, then you

will need to manually change those.

 

If you want to use the Find and Replace function to

delete something, put what you want to delete in

the Find and leave the Replace completely blank

(including no spaces).

 

By the way, if you want to use Find and Relace for

just a section of your spreadsheet for some reason,

you can just highlight the areas you want to work

with before you do the Find and Replace.

[Guest]

Ah I see. I had to do the copy and paste special a second time to get that to work :D

 

I still get the formula error now however. It has replaced the first 43 I think but then I get that error. There are 1468 products I think so I do not really want to edit them all as that would mean each time I want to do an export from filemaker I would have to do it.

 

The other symbols I have are "*ե" And when I try to replace them with just a space I get the error below:

 

[kirchenbauer]

I'm looking in Excel. Try this:

 

Display or hide control characters

On the Tools menu, click Options , and then click the International tab.

Select or clear the Show control characters check box.