Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Paradox->MSAccess->ASCII->import to MySQL problem

Quote Reply
Paradox->MSAccess->ASCII->import to MySQL problem
Hello all,

Well i am stuck for 2 days now on this conversion
database from Paradox->MSAccess->ASCII->import to MySQL problem.

I did:
- We have a paradox database (from Corel).
- Because i needed some query's i used Ms Access (yeah i know query's can also be done in mysql :)).
Well after i overwon a language table problem (paradox to msaccess i translated it to ascii instead of table language paradox, but never mind this) i had a 'good' MS Access 2000 database with also some
MEMO fields (TEXT/BLOB in MySQL) in it....

Ok so after the query's, i used the export function from MS access to get a ASCII file to import ....

When i tried to export MS Access 2000 database to a ASCII file, i selected all the option like | is the delimiter and so on.

After that the ASCII file was created... Well let's look in file with wordpad (with no wrap on) ....

Eh.. only a few records (10%) is in that file hmm.. where is the rest of my data ? damm...
Also a record is sometimes not 1 line, well maybe that's because in the memo field there are [enters],
but where is the rest of my data?

Can any give my a hint how to solve this ? I am really stuck on this problem ....

Possible:
- I have some memo fields (alias TEXT/BLOB in MySQL) in the MS Access database maybe that is a problem ?
- Export in MS Access 2000 don't work good ?
- Use MyOCDB (well i can't use that yet, because i am using a server from a i.s.p. and can only access in telnet, but i will soon have my own server) ?
- Select a other export in MS Access then ASCII .txt file ?

Hope anyone can help me.

Allready thanks.

Regards Startpoint.

Quote Reply
Re: Paradox->MSAccess->ASCII->import to MySQL problem In reply to
I know that Excel handles exports to tab delimited ASCII files a lot better. When you tried using Access, did you select tab delimited?

I think that the field you've defined (MEMO field) may be causing a porblem during the export process.

Of course, the easier solution is to move your database to MS SQL, which allows you to import DSN files (mbd).

Regards,

Eliot Lee

Quote Reply
Re: Paradox->MSAccess->ASCII->import to MySQL problem In reply to
Thanks Eliot for the reply

Tab delimited i did try but same BAD result.

MS SQL <-- LOL well i don't have it and don't want it .....

Eh.. any other ideas ?

Regards Startpoint.

Quote Reply
Re: Paradox->MSAccess->ASCII->import to MySQL problem In reply to
Startpoint,

Here's how I did it:

Open your table in Access
Open a new sheet in excel.
Highlight the entire first Column in access (by clicking in the column name) and click on Edit > Copy.
Switch over to excel and highlight the first column (A), then click on Edit > Paste.

Do this for each column in your Access table and then save the Excel file in excel format. You may have to edit some of the data 'types' in excel to get the exact format for dates, etc.

From here you can experiment with exporting and you'll probably find that excel produces better results - it worked for me!

All the best
Shaun

Quote Reply
Re: Paradox->MSAccess->ASCII->import to MySQL problem In reply to
Thanks Shaun,

Well it looks like the .tab file is better then the other, great (YEAH!! this must be my day...) ....

Now i want to import it to linksSQL....


I tried it with import function of editor.cgi but no succes.

Eh.. tab delimiter is \t eh?

Well then i tried it with mysqlman (import) but if i select only the fields i have to import, some 'strange' mysql-error appear ..... Maybe it's a bug in mysqlman don't know yet...

How did you import the exported (excel).tab file into LinksSQL ?

Allready thanks.

Regards Startpoint.

Quote Reply
Re: Paradox->MSAccess->ASCII->import to MySQL problem In reply to
Again, this is how I work it, it may not be suitable for your MO but I'll give you the details anyway:

I created a text file categories.txt and opened it with a standard text editor.

I then added the field names to the top line of the file to create a header, i.e.;

ID|Name|Description|Meta_Description|Meta_Keywords|Header|Footer|Number_of_Links|Has_New_Links|Has_Changed_Links|Newest_Link|isSub

(these may be different for your category database :-)

Directly underneath I added the category list, i.e.;

3|Animals and Nature/Animal Behavior/Animal Science|Animal Science|||||0|No|No|1999-01-01|Yes
4|Animals and Nature/Animal Behavior/Bioacoustics|Bioacoustics|||||0|No|No|1999-01-01|Yes
5|Animals and Nature/Animal Behavior/Chat and Forums|Chat and Forums|||||0|No|No|1999-01-01|Yes
6|Animals and Nature/Animal Behavior/Courses|Courses|||||0|No|No|1999-01-01|Yes
7|Animals and Nature/Animal Behavior/Institutes|Institutes|||||0|No|No|1999-01-01|Yes
8|Animals and Nature/Animal Behavior/Organizations|Organizations|||||0|No|No|1999-01-01|Yes
9|Animals and Nature/Animal Behavior/Publications|Publications|||||0|No|No|1999-01-01|Yes
10|Animals and Nature/Animal Behavior/Web Directories|Web Directories|||||0|No|No|1999-01-01|Yes

(NOTE: The number doesn't really matter as LSQL adds a number from the database itself, so long as you have a number here it should be OK.)

From the LSQL admin panel click on Category in Table Maintenance, then click on the option at the top for Import data.

Click in the main part of the window (not the filename box) and add the 'header' field ID's. You'll find that you cannot add more than around 600 categories at a time this way, so select the first 600 cats and paste them under the header, then click the 'Import Data' button.

Repeat this step for each batch of 600 or so categories until they're all in. Long winded method I know, but it worked for me, and I often add 'groups' of new cats this way.

Hope it helps.

All the best
Shaun

Quote Reply
Re: Paradox->MSAccess->ASCII->import to MySQL problem In reply to
Thanks qango,

Eh i only have to import to table links :)...

But in a .tab file you don't have delimiter | eh...? So there you lost me...

Didn't you say you used tab as a delimiter ?


Also i wanna use file import but that not much different as the methode you used ....

Hmm... i go try and look for other solution...... or maybe the 'bot' man is arround ?:)...

Regards Startpoint

Quote Reply
Re: Paradox->MSAccess->ASCII->import to MySQL problem In reply to
You use \t in the delimiter field for tab delimited files.

| is used for pipe delimited files.

Regards,

Eliot Lee

Quote Reply
Re: Paradox->MSAccess->ASCII->import to MySQL problem In reply to
Sorry if I muddied the waters a little there, I've only ever used the | pipe myself.

Anyway, looks like the 'bot' has already beaten me to it Smile - any further on with it now?

All the best
Shaun

Quote Reply
Re: Paradox->MSAccess->ASCII->import to MySQL problem In reply to
Well qango not yet somehow i can't import the file... not with editor.cgi or mysqlman...

I go check it out and hope to tell or ask you all more:)....

Laterz..

Regards Startpoint.