Importing text file into mysql

software development

#1

Can anyone shed any light on the best way to get a text file of data into a mysql database.

phpMyAdmin wont do it because the file is too big (29mb).

I tried to use cocoaMysql but it just choked.

Thanks


#2

You can log-in via SSH and issue shell commands to import your database, the wiki article linked below has the details…

http://www.wiki.dreamhost.com/index.php/Migrate_MySQL

Note: You will need to enable shell access for your user and have some kind of SSH client available, the following wiki articles have more information…

http://www.wiki.dreamhost.com/index.php/Enabling_Shell_Access

http://www.wiki.dreamhost.com/index.php/SSH

Mark


Save [color=#CC0000]$50[/color] on DreamHost plans using [color=#CC0000]PRICESLASH[/color] promo code (Click for DreamHost promo code details)


#3

Thanks for the info.

The pages you linked only have info about importing sql files, which i can already do.

I need to be able to import a text file with the following format.

CC_FIPS FULL_NAME_ND

AA Angochi

AA Arasji

AA Babijn

AA Balashi

AA Barcelona

AA Blumond

AA Boton

AA Brasil

AA Bringamosa

AA Bubali

AA Bushiribana


#4

Sorry, I misunderstood what you were trying to do. I thought you had a text file containing a previously ‘dumped’ MySQL database that you wanted to import.

Since you mention CocoaMySQL, I assume you are running a Mac. I don’t use a Mac here, so unfortunately I am unable to suggest any applications that may do what you require.

Hopefully, one of the Mac users here will chime in with a suggestion or two.

Good luck.

Mark


Save [color=#CC0000]$50[/color] on DreamHost plans using [color=#CC0000]PRICESLASH[/color] promo code (Click for DreamHost promo code details)


#5

Thanks for the help anyhow.

I’ve seen examples of people using php to do this.

But i’m unsure how to indicate that the first 2 lines should be treated as field names and the rest of the lines are data.


#6

That’s one way of skinning the cat - provided you’re a talented-enough PHP hack.

Here’s an alternative:
Download this: http://www.heidisql.com/ and use it to create the table w/fieldnames based on your data. Then use a spreadsheet program to import your text data into a spreadsheet format and export it as a CSV (comma-separated values) file (each field seperated by a comma), deleting the first row with the field names because you won’t need it because you already made those w/Heidi. Then use Heidi’s built-in CSV import to suck the data into the table you previously entered.

I just had to do pretty much the same thing with a database full of airport codes. The number of airports in the world - 2048 that’re listed but I bet some of 'em are barely-paved goat paths - presented a similar data import problem for me just a coupla days ago.


#7

Thanks. This sounds like a good work around. I’m on a mac but i can install heidisql on parallels and give it a go.