How to migrate Flat Text File into MySQL?

#1

Post by zeroxsys » Sat, Jul 23, 2011, 1:55 PM UTC

I am running a VB.NET loop that imports a flat text file of 122677 towns/cities of Japan into an online MySQL table. MySQL Workbench crashes when you upload table this big, so export/import won't work. It took less than 2 hours when I first run it for local migration, now it's only at 55600 after 3 hours.

Is there a better way to do this?

Re: How to migrate Flat Text File into MySQL?

#2

Post by smoove » Sat, Jul 23, 2011, 2:00 PM UTC

Do it on the server.

Re: How to migrate Flat Text File into MySQL?

#3

Post by there » Sat, Jul 23, 2011, 2:01 PM UTC

are you doing bulk inserts or one-per-transaction?

Re: How to migrate Flat Text File into MySQL?

#4

Post by zeroxsys » Sat, Jul 23, 2011, 2:07 PM UTC

there wrote: post #3

are you doing bulk inserts or one-per-transaction?

Not using transaction, auto-commit.

Re: How to migrate Flat Text File into MySQL?

#5

Post by zeroxsys » Sat, Jul 23, 2011, 2:09 PM UTC

smoove wrote: post #2

Do it on the server.

I don't have a shell access to my client's shared server, so I can't do it using perl/bash. Yes, I'll try this with PHP instead.

Re: How to migrate Flat Text File into MySQL?

#6

Post by rick888 » Sat, Jul 23, 2011, 4:46 PM UTC

zeroxsys wrote: post #5

smoove wrote: post #2

Do it on the server.

I don't have a shell access to my client's shared server, so I can't do it using perl/bash. Yes, I'll try this with PHP instead.

you could upload the file to your server and do a mysql_import (I assume it's tab/comma delimited).

PHP can call system commands, so you could do system("Mysql import ...(file)...").

more info about the command here: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

Re: How to migrate Flat Text File into MySQL?

#7

Post by zeroxsys » Sat, Jul 23, 2011, 5:26 PM UTC

rick888 wrote: post #6

zeroxsys wrote: post #5

Earlier quoted context omitted.

I don't have a shell access to my client's shared server, so I can't do it using perl/bash. Yes, I'll try this with PHP instead.

you could upload the file to your server and do a mysql_import (I assume it's tab/comma delimited). PHP can call system commands, so you could do system("Mysql import ...(file)..."). more info about the command here: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

Yes, I am familiar with that, thank you @rick888.

Re: How to migrate Flat Text File into MySQL?

#8

Post by zeroxsys » Sat, Jul 23, 2011, 5:28 PM UTC

smoove wrote: post #2

Do it on the server.

Thank you so much @smoove, I totally forgot to do this on the server for my own sake! Well the reason is, I am really looking at a long-term solution, not just for this Japan address job.

This desktop app technique I am using now is more geared towards my clients who cannot do that command/code (you are recommending) themselves. I am looking at how to do this kind of migration and synchronization of local and online (MySQL) databases automatically (or at least with a single click by my clients). My clients may not even know how to do FTP; even if I provide them with manuals to the technical steps, they might find it difficult. That’s why I am building these VB.NET migration/synchronization utilities that do flat-file migration and local-to-online (Intranet-to-Internet) table synchronization so that they can accomplish the task on their own.

I can setup MySQL Replication but they will have problem with primary IDs. Aside from that, I don’t know other tools/technique out there that can do these migration and synchronization.