How Could I Import To Different Tables With Sja ?

[MuphoOx]

Hello,

I'm using sja, for importing data from an access database (generated from an excel file).

when i import to a unique mysql table it work great … but, what i have and want to do, is to import from the access database to 5 different mysql tables (on the same database).

The original excel file contain 180 columns, extracted to CSV format, afterward imported into access database.

[peterlaursen]

Why do you use Excel and CSV as an intermediary format? You can import directly from Access with SJA/Migration tool.

Do you use Migration Tool with the text-ODBC-driver or is it is SJA for Linux and a Notification Service job? Or … ?

Please explain more in detail!

With direct migration from Access you will need to run 5 different jobs then and use the parameter with each of them. You can create one batch-file with the 5 jobs, as described here:

http://webyog.com/faq/27_35_en.html

read about the option here

http://www.databasejournal.com/features/my…p/10897_3550146

[MuphoOx]

thank you peter for the fast reply.

The data source is an excel file, i've imported them to an access database using CSV cause when i wanted to migrate directly from excel to access (both in office 2003 format) i had many error messages.

Realy i don't know how i could use the SQL Where to put the data of 3 or 4 access DB colums into adequate collums in the mysql tables.

[peterlaursen]

Quote:

Where to put the data of 3 or 4 access DB colums into adequate collums in the mysql tables.

That is another problem then. it is columns but not rows that you want to 'split'.

Well you just select what columns should be imported with each job? See attached.

Do I completely misunderstand you?

[MuphoOx]

You're right sir !

this what i want to do … i did this … but, more of it, i would put some of the selected column on TABLE_1, and other columns on TABLE_2 of the same database.

i made a batch file with many sja jobs. the problem that i've encountered is that the tables used are related

TABLE_1 (ID_tb1, fk_id_tb3, article_name, …)

TABLE_2 (ID_tb2, fk_id_tb1, article_price, …)

TABLE_3 (ID_tb3, …)

I can't insert data on a table randomly, so how coul i insert data (concerning prices for exemple) on the TABLE_2 as i can keep the relation between the two tables ?

[peterlaursen]

It is a little bit tricky with this setup. But try to learn the tool

You could do like

1) run import jobs creating empty tables (using a where that is never fullfilled (like WHERE 'a' = 'b')

2) run ALTER TABLE statements (form a Notifications Service Job) creating FK's

3) run import job again without the 'silly where' and choose the option not to delete existing tables.

[MuphoOx]

OOOkey ! thx peter; i'll try this tricky method ! answer you once i'm done 😉

[MuphoOx]

i'm back again …

i've tried your solution, but i think that will not work with MySQL version 4.1.12. Unfortunately, i can't upgrade to mysql 5, and doing this will lead to many changes on our ERP software. The error message that i had during the creation of the FK constraint is :

The ALTER TABLE :

Quote:

ALTER TABLE migration_data_client_boutique.produits24_article ADD CONSTRAINT

FOREIGN KEY (code_utilisateur_article) REFERENCES bw.article(code_utilisateur_article) ON DELETE CASCADE;

FOREIGN KEY (CODE_UNITE_STOCKAGE) REFERENCES bw.article(CODE_UNITE_STOCKAGE) ON DELETE CASCADE;

[…]

The error message is : SEE THE ATTACHMENT

[peterlaursen]

I simply believe this is a MySQL bug. Or a privilege issue on the server.

Could you upgrade to 4.1.21 (latest 4.1.x build)?

Can you do this ALTER TABLE from any other client?

[MuphoOx]

This alter table was done first on MySQL 5 and that was working.

i'm trying to do a migration to a temp table on the database i'm using and execute an SQLScript as this one :

Quote:

insert into article

select

code_utilisateur_article, descriptif_langue_1_article, nom_article, […], code_type_gestion_stock, stock_negatif_autorise, […]

from temp_table;

insert into ligne_de_tarif

select prix_vente

from temp_table, article, remise

where remise.code_article = article.code_article and article.code_utilisateur_article = temp_table.code_utilisateur_article ;

insert into ligne_prix_achat

select date_debut, date_fin

from temp_table, article, ligne_prix_achat

where ligne_prix_achat.code_article and article.code_utilisateur_artcile = temp_table.code_utilisateur_article ;

what do you think of this solution ?

[TomBez]

MuphoOx wrote on Oct 4 2006, 02:12 PM:

Code:

where ligne_prix_achat.code_article and article.code_utilisateur_artcile = temp_table.code_utilisateur_article;

wont work, as first where clause has nothing to be proofed, i suppose you want to connect article with ligne_prix_achat, so something like

where ligne_prox_achat.code_article = article.??????? is needed.

[MuphoOx]

ohh !! yeah !! i forgot this !!

==> ligne_prix_achat.code_article = article.code_article and article.code_utilisateur_article = temp_table.code_utilisateur_article;

that was just a miss ! (i've not executed the request yet)

[MuphoOx]

Hi all !

I'm back to the forum, was in holidays 😆

To solve my probleme i've used an sql script with many Select and Where conditions in aan SJA notification Job, and now everything is ok 🙄

[Author]

Posts