1

I have exported the csv file from SQL SERVER database which contains 24K records. I want to convert the csv into an .sql file i have tried phpmyadmin (timeout error) and online converters but all them failed :

<b>Fatal error</b>:  Allowed memory size of 67108864 bytes exhausted (tried to allocate 75 bytes) in <b>/mounted-storage/home85/sub007/sc60314-GZFW/typexpert.net/csv2sql/parsecsv.lib.php</b> on line <b>426</b><br />

What will be the best alternative ?

NOTE: None of the previous posts resolved my issue .

this a screen of result i get after import the insert script .sql from SSMS :

enter image description here

I got loading issue (timeout)

Malek Zarkouna
  • 943
  • 10
  • 21
  • Huh? What do you mean by convert a csv to .sql? What are you trying to accomplish here? And which DBMS are you using? mysql <> sql server But I am not convinced this really has anything to do with a DBMS as it seems like a PHP issue. – Sean Lange Aug 21 '17 at 13:49
  • I m using MYSQL and the file i want to convert is a csv file that i got from an sql server database – Malek Zarkouna Aug 21 '17 at 13:50
  • Do you want to SCRIPT your data as INSERT to execute then this script in MySQL? – sepupic Aug 21 '17 at 13:56
  • @sepupic Absolutely – Malek Zarkouna Aug 21 '17 at 13:57
  • 1
    absolutely yes or absolutely no? – sepupic Aug 21 '17 at 13:58
  • @sepupic Absolutely YES ! – Malek Zarkouna Aug 21 '17 at 13:58
  • 1
    You can use scripting option in SSMS and generete the INSERT script instead of save your data as csv – sepupic Aug 21 '17 at 13:59
  • @sepupic I tried it generating the insert but i got error in phpmyadmin seems to ignore the script entirely – Malek Zarkouna Aug 21 '17 at 14:02
  • If you don't show your error how can we help you? – sepupic Aug 21 '17 at 14:03
  • i will upload a screen of the phpmyadmin error – Malek Zarkouna Aug 21 '17 at 14:04
  • I would not even consider using php for something like this. I would use the data export tool from SSMS and insert your data in one single step. Or use the scripting tool in SSMS to generate insert statements for your data. – Sean Lange Aug 21 '17 at 14:04
  • 1
    I'm curious why you're using PhpMyAdmin to do this. I wouldn't bother transforming the CSV into insert statements. If you want to import a CSV into MySQL, usually you would use [`load data infile`](https://dev.mysql.com/doc/refman/5.7/en/load-data.html). Just make sure that if you need unicode that it's UTF-8 encoded since SQL Server tends to prefer UTF-16. Also, it's typically best to import the data into a staging table you've created for this purpose. You can then verify the data are correct before inserting them into the live table. – Bacon Bits Aug 21 '17 at 16:25
  • Possible duplicate of [Importing a csv into mysql via command line](https://stackoverflow.com/questions/6605765/importing-a-csv-into-mysql-via-command-line) – Bacon Bits Aug 21 '17 at 16:27
  • @BaconBits Yes load data worked fine thanks – Malek Zarkouna Aug 22 '17 at 10:17

1 Answers1

1

Your PHP script has not enough memory it can use. That depends on your server and settings. If your server has enough you can easily add more memory to phpmyadmin by changing the settings in the phpmyadmin folder. You can use different methods:

changing in htaccess (for apache):

php_value memory_limit 2G

changing in php:

ini_set('memory_limit', '2G');

changing in user.ini (for plesk users)

memory_limit = 2G
Remco K.
  • 644
  • 4
  • 19