How to migrate Flat Text File into MySQL?

https://news.ycombinator.com/item?id=2796917

by zeroxsys • 15 years ago

1 7 15 years ago

news.ycombinator.com

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?

smoove• 15 years ago

Do it on the server.

zeroxsys4• 15 years ago

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.

rick888• 15 years ago

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

zeroxsys4• 15 years ago

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.

there• 15 years ago

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

zeroxsys4• 15 years ago

Not using transaction, auto-commit.

How to migrate Flat Text File into MySQL?

Related Stories