How could i make modifications on .sql file offline ?

[expired user #5726]'s profile image [expired user #5726] posted 13 years ago in General Permalink
Hi,

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's profile image ansgar posted 13 years ago Permalink
I'm confused. You have an Excel file which shall go into some table on your server or what?
[expired user #5726]'s profile image [expired user #5726] posted 13 years ago Permalink
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!

Thanks
ansgar's profile image ansgar posted 13 years ago Permalink
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:

[expired user #5726]'s profile image [expired user #5726] posted 13 years ago Permalink
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 ?

Thanks
ansgar's profile image ansgar posted 13 years ago Permalink
pma is configured in some config.php or so, not sure, I don't use it since several years. Did you set pma up?
[expired user #5726]'s profile image [expired user #5726] posted 13 years ago Permalink
I didn't set a thing!
And even i didn't find the config.php in my files!
[expired user #5726]'s profile image [expired user #5726] posted 13 years ago Permalink
what I'm suppose to fill in here?

http://www.heidisql.com/images/screenshots/connection.png?2009-10-02

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's profile image ansgar posted 13 years ago Permalink
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.
[expired user #5726]'s profile image [expired user #5726] posted 13 years ago Permalink
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's profile image ansgar posted 13 years ago Permalink
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.