importing price data

[Queenswood]

Happy new year I hope everyone had a good one. I im doing a big update of products from filemaker pro into actinic. I'll be doing this using a standard tab file.

Problems I've had previous that I know will happen but im hoping that someone might know a way I can get around.

1. importing data I cant import using CSV as in our long description we have used commas. (this is why I am exporting to a tab seperated file.)

2. Prices all our prices are in a caclulated field which for example will export as £99.999999 for £99 but actinic requires this to be 9999?? I had issues on previous imports with 0s being cut off the end of the price. Not really too bad when having to check aprox 200 products. I will be importing over 1000 products & need an easy way verify the data.

3. Import of carriage returns. Importing as squares. If I export then import directly into actinic I think this doesnt happen its only If I open with a text editor or excel. Again anyone have an easy way to avoid this. As I will need to verify the data....????

I think thats it. Any help would be great.

Thanks

David Shorthouse

[TraceyG]

Hi David,

I hope I understand your issues correctly (please correct me if I'm not getting the point) but here goes:

I cant import using CSV as in our long description we have used commas. (this is why I am exporting to a tab seperated file.)

CSV files can be tab delimeted, although I am not sure how Filemaker Pro allows you to export the data. I tried the exporting a catalog db to a comma-delimited file (test.csv) and then imported it into Excel by going to 'Data | Get External Data | Import Text File'. I set it to 'delimeted' and then selected 'comma' and that imported ok with any commas in my full descriptions remaining in place.

Import of carriage returns. Importing as squares

I guess this must be dependent on how they are exported from Filemaker. Importing the file as I did above, any carriage returns appeared as '/n' rather than squares. Maybe you could replace your returns with '/n' within Filemaker (not ideal I know but possibly try it for one or two products to see how it goes).

Prices all our prices are in a caclulated field which for example will export as £99.999999 for £99 but actinic requires this to be 9999?? I had issues on previous imports with 0s being cut off the end of the price.

Actinic requires the prices to be set in the database as pence, so 9999 would equate to be £99.99. I'm not sure why imports would cut off 0's, I've never seen that before. To be honest, I don't know of an easy way to validate your prices so that they will import into Actinic correctly. Maybe an Excel expert could comment to this.

I'm not sure that I've been of much help to you, but hopefully someone else using Filemaker will comment on how they use it with Actinic.

[Jan]

To get the prices into pennies, just create a SQL query that multiples the price by 100 (and possibly round it up) and then export that rather than the table. You could probably fix up the carriage return issues as well and possiblty having to use tabs as separators (although this is not a problem unless you use tabs in your fields) - in this last case use '"' + field + '"' in the sql statement for each string field.

Alternatively, you can use external linking with Actinic Business (or above) to link directly to filemaker pro through ODBC.

Regards,

[Queenswood]

Hi Tracey,
Basically what I am trying to achieve is a streamline to getting the data out of filemaker & into actinic with as little human intervention as possible

I have the task of importing the data & when done in small amounts its easy to check. But we are basically moving over 1000 product into our sale which is impossible for me to check all of them. So the less data correction I have to do the better that is why I need to get the squares fixed as well.

Hi Jan many thanks for your reply we use filemaker4 & don’t have access to ODBC drivers. I export the data to csv, I try not to open the file in excel as we still use office 95 & opening the CSV file in excel removes data.

Please except my apologies for any confusion in the original post.

My Problem was mostly prices which I will try Jans query.
I should be able to set my text editor to change squares to /n many thanks for that Tracey.

Many thanks,

Dave.

[Queenswood]

Hi Tracey,

Tried changing the carriage returns to /n now in my description I am getting the the /n appearing instead of a square. I wasnt sure if i had to add the !!< >!! at the beginning and end of the record so tried. It still imported /n any ideas..

The Data has already gone live as the catalogue we do was released early. That is why I have been on panic stations any other thoughts would be great.

Thanks,

Dave..

[Jan]

I've seen carriage returns printed as squares in catalog after import, they seem to display correctly on the website, so I have always left them alone. If you want to change them still try \n or \r\n.

Regards