Tips and Tricks

Problems with data export and import? Check here

Why is my data export showing all on one line in Excel?

It's because of your "regional settings". You've got Excel set up with 'European-style' separators so it thinks that the , is a decimal separator and not a list separator. Depending on your operating system or version of Excel, you can change this setting in something like 'Regional and Language Settings'. Just make sure your decimal separator is set to . and the list separator set to ,.

Why is CSV import/export so slow?

Interesting question. Well, part of the architecture that makes Pakk so fast in other areas is actually a weakness when it comes to data export/import. Because we don't use a traditional "relational database" (don't worry if that means nothing to you), it unfortunately makes computing all the relations between different records quite a slow process depending on how many of these "relations" your data has. Given that data import/export is quite infrequent, we think it's a tradeoff worth making.

Is there a limit to the number of records that can be exported or imported in one go?

It's not the number that is limited, but the run time of the operation. Currently both imports and exports are limited to a 60 second run time and will time out if they take longer.

Why is there a limit to export/import run time?

It's related to the high resource consumption of these processes - have a read of the question regarding 'slowness' of import and export - it's essentially the same explanation!

My import/export crashes after a long period with an obscure message. What might be wrong?

Again, this is related to the answer above about 'slowness'. If you have a very heavy import in terms of "relational data" (i.e. lots of fields that reference other entities in the system), this will reduce the number of records you can successfully import in one go. Try breaking the import up into separate chunks, or if you are exporting, narrow your filters to export only a subset of the list.

Can you give me any general tips for importing data?

Yes, importing data by CSV is part science, part art. If you've been doing it long enough, you learn a few tricks:

• Break up your import. Don't try to import all your data at once. Decide on some axis by which to divide up your data which makes sense to you and try importing it chunk by chunk. For example, for products, you might order your product list alphabetically by SKU and split it in half.

• Use formulae in Excel to "massage" your data before importing it. If you're getting lots of validation errors, flex some Excel muscle and get your data cleaned up before trying to import it. For example, you could use the excel LEFT function to shorten product meta descriptions that were too long.

• Don't import all fields at once. If there are some particularly hairy "relational" fields, like 'Categories' for products. You can leave those until after the main import and do a secondary import with just those fields. Your best bet in in circumstances like this is to use VLOOKUP in Excel to relate the newly created records via their primaryIdentifier to the export from your old system. Remember to leave in the _id field in the import so that it does and update and not creation of new records

• Make use of "import simulations" to test your imports before committing the changes. View the import report and fix any errors that come up. Do this multiple times until your import is error free - then commit it.

• Copy and paste any 'primaryIdentifier' fields to related records - don't type by hand. For example, if you are importing Products and need to set a Category field to 'Pastries and Confectionary', do an export of Categories and copy-paste the Category primary identifier - don't write it manually because you are likely to type it wrongly and the import will fail - it has to be exact.