How could i make modifications on .sql file offline ?

mr.riziq posted 5 years ago in General

I'm wondering if there is any way to make modifications on .sql file offline without online connection to my online database ?

i can use the phpmyadmin, but the problem that i have more than 100,000 entry in one table! i cant entry them all using the regular way through my website or even using the "insert" toll in the phpmyadmin !!

I'm really in need of any software that i can transfer my data from the excel sheet to .sql file offline!

I used to download HeidiSQL software but it asked me some configurations that i cant provide them easily! (:

Any help please?

Thanks for your time...
ansgar posted 5 years ago
I'm confused. You have an Excel file which shall go into some table on your server or what?
mr.riziq posted 5 years ago
This is it simply:
1- I've export one table from my database as .xls sheet using PhpMyAdmin.
2- I need to add more than 10,000 entry to this table manually.
3- Then i need to import this file using the PhpMyAdmin to my database instead of my old one.

That's it..

The problem is that i cant import the .xls sheet simply using PhpMyAdmin! it wont work!

ansgar posted 5 years ago
Then you're looking for help on phpMyAdmin or?

Just save the file as .csv in Excel and import it with pma, or HeidiSQL. If you have pma configured you should be able to use HeidiSQL as well, passing the same host/user/password. Both pma and HeidiSQL can import a text file, and Excel can export such files.

I see you have exported data only in order to be able to use Excel as editor for it. Be aware that the above export/import procedure can result in data loss, for columns with NULLs for example, which are mostly not handled correctly in csv files. For the future I highly recommend editing rows in HeidiSQL (or pma of course). HeidiSQL has a data tab, which is comparable to some Excel sheet:

mr.riziq posted 5 years ago
It sounds good..
But i don't think that I have the accurate PMA configurations to get in HeidiSQL as well!

Would you guide me where i can get the information to start using HeidiSQL immediately ?


ansgar posted 5 years ago
pma is configured in some config.php or so, not sure, I don't use it since several years. Did you set pma up?
mr.riziq posted 5 years ago
I didn't set a thing!
And even i didn't find the config.php in my files!
mr.riziq posted 5 years ago
what I'm suppose to fill in here?


Hostname /ip: is it "localhost" or my website url "www.mydomain.com" ?

User: Is it my DB username ?
Password: Is it my DB password?
ansgar posted 5 years ago
Hostname could be both, I don't know on what server you are working. Try localhost if you installed MySQL on your local computer.

User/password - of course the db credentials.
mr.riziq posted 4 years ago
No I'm sorry, i didn't install Mysql on my computer, it just a .sql file that what i want to modify! i'm using justhost.com server online.

ansgar posted 4 years ago
In that case justhost.com should have mailed you host/user/password, didn't they?

Please login to leave a reply, or register at first.