How do you upload data from .dat file on the server database?

[Ivan Ivković]

We bought a database containing all the countries/states/cities in the world.

 

These are the instructions for the upload:

Step 1. Create following tables in your database with the command:
create table country(country_code varchar(2), country_name varchar(255));
create table states(country_code varchar(2),state_code varchar(20),state_name varchar(255));
create table weblocations (country_code varchar(2), state_code varchar(20),city_name varchar(255));

 

Step 2: Add unicode support to the table

ALTER TABLE country CONVERT TO CHARACTER SET utf8 COLLATE utf8_persian_ci;
ALTER TABLE states CONVERT TO CHARACTER SET utf8 COLLATE utf8_persian_ci;
ALTER TABLE weblocations CONVERT TO CHARACTER SET utf8 COLLATE utf8_persian_ci;

Step 3. Load the data using the following command. Please substitute correct path for the extracted files from the zip that we provided

LOAD DATA LOCAL INFILE 'C:/country.dat' INTO TABLE country CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n'; 
LOAD DATA LOCAL INFILE 'C:/states.dat' INTO TABLE states CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n'; 
LOAD DATA LOCAL INFILE 'C:/weblocations.dat' INTO TABLE weblocations CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n'; 

Step 4. Verify that the data has been loaded.

SELECT * FROM country;
SELECT * FROM states;
SELECT * FROM weblocations;

 

I've tried uploading to my local database server and it works like a charm, but I can't export more than 40 mbs of data (there's around 100) since php.ini has maximum_executing_time to maximum of 300 seconds, and that's still not enough.

 

The problem is. What do I type in instead of 'C:/country.dat' and for other files? I can't find any mysql folder in my cPanel to move my files to that folder...

And what is the format of my link to the file? Is it http://www.mysite.com/country.dat or is it standard stuff like C:/something/something/country.dat?

 

This is my folder structure:

 

 

(/home/social)

access-logs

bin

etc

 

mail

perl

php

 

public_ftp

 

public_html

tmp

 

www

application

application_blog

cgi-bin

cometchat

externals

install

public

temporary

 

[trq]

You need shell access to be able to do this. Either that, or create the database locally, then make a dump of that. You should have some method of importing a database from a dump available through your control panel.

[Ivan Ivković]

I've tried to do it locally but it won't dump everything, and I can't do it in parts since I can't tons of pages, just blows my PC away lol... Errors everywhere (using new phpmyadmin, no bugs while using normal sizes of data).

[Ivan Ivković]

I tried to export whole table weblocations (105mb) and it exports only 40 mb, saying maximum execution time of 300 seconds exceeded... And it writes out the error IN THE SQL FILE lol, not on my screen.

[gizmola]

This is a question for your webhost.  Do you have ssh access to your site?  If so you can run the commands as listed, with only slight alterations to the path (assuming it's linux).  Just use the command line mysql client.

[Ivan Ivković]

I have Shell Access button and Restart Services -> SSH Server (OpenSSH)

 

It is Linux.

 

BTW, HUGE thanks for help.

[trq]

Shell access button? You generally access the shell via an ssh client. You may however have some web based shell emulator that will suffice (this sounds like what your looking at).

 

Login via that and execute the appropriate commands.

[Ivan Ivković]

By the way, if you help me, as a gift I will put the whole cities - countries - states database in SQL format and put it on this website in a torrent file / upload link.

[Ivan Ivković]

I don't see any shell client option. :/

[trq]

By the way, if you help me, as a gift I will put the whole cities - countries - states database in SQL format and put it on this website in a torrent file / upload link.

 

That will get you banned.

 

I don't see any shell client option. :/

 

I suggest you contact your host and ask them if you have shell access.

[dzelenika]

there are two possible solutions:

  1. copy (ftp) local mysql datafiles to remote server mysql directory

  2. export is ASCII file wiith INSERT queries, cut it to smaller parts, and then import them

 

[QuickOldCar]

You need shell access to be able to do this. Either that, or create the database locally, then make a dump of that. You should have some method of importing a database from a dump available through your control panel.

 

That's most likely your best solution.

Load the data into mysql using a local version of wamp/xampp or whatever package you use.

Increase the limits for timeout and size on your local install to accomplish your goal.

Once you have the data loaded into database, you can export, then import one table at a time into a new database at your server.

 

I've even just copied the MySQL/data/database_folder_name files from the folder, then do a repair on the tables.

[Ivan Ivković]

Well I managed to convert the .dat files to sql now. It's all good now... Thanks for your help.