ImportCSV is used for load positions from CSV file to database.
Import occurs in three steps:
- Upload file;
- Select delimiter and table;
- Select mode and columns in table.
Module has 3 modes:
- Insert all - Add all rows;
- Insert new - Add new rows. Old rows remain unchanged;
- Insert new and replace old - Add new rows. Old rows replace.
All parameters from the previous imports will be saved in a special .php file in upload folder.
Requirements ¶
Yii 1.1
Usage ¶
1) Copy all the 'importcsv' folder under /protected/modules;
2) Register this module in /protected/config/main.php
'modules'=>array(
.........
'importcsv'=>array(
'path'=>'upload/importCsv/', // path to folder for saving csv file and file with import params
),
......
),
3) Create a directory which you use in 'path'. Do not forget to set access permissions for directory 'path';
4) The module is available here:
http://yourproject/importcsv.
Or here:
http://yourproject/index.php?r=importcsv.
Or somewhere else:-) It depends from path settings in your project;
5) ATTENTION! The first row of your csv-file must will be a row with column names.
Our repo in github https://github.com/Ardem/yii-importcsv-extension
transactions
Does it use transactions for faster inserts? If not how can we set?
Re: transactions
pentium10, thank you for advice. I added support CDbTransaction in module.
Problem installing ImportCSV
Hi Ardem,
Thank you for developing this extention. Just the one i was looking for!!!
Unfortunately I cann't get ImportCSV to work. I have followed the install requirements.
I have created the folder as below and set the permissions to 777
'modules'=>array( ......... 'importcsv'=>array( 'path'=>'upload/importCsv/', // path to folder for saving csv file and file with import params ), ...... ),
When i try to import a CSV file i get a:
Not Found The requested URL /importcsv/default/upload was not found on this server. Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request. ```~ When i take a look in "assets/download.js" there is a line: ```php action : '/importcsv/default/upload',
When i created this map in my wwwroot i don't get the 404 error, i can start select a CSV en submit it. But the respons is first "Loading..." and then "aa" and the file isn't uploaded.
Can you help me out please?
Thanx!!
I have tried all kinds of combinations
Re: Problem installing ImportCSV
Hi, Gijs.
"/importcsv/default/upload" - it's a module/controller/action
May be you use another URL rules in your project? In this case you need to change address "/importcsv/default/upload" in "assets/download.js"
Re: Problem installing ImportCSV
Thanx for your quick reply!! I am new at Yii and your extention is my first. I have followed the provided install details. In "proteced" i've created a folder "upload/importCsv" with 777.
Then i use the URL: "index.php?r=importcsv/default/" and after that is fails. Also "index.php?r=importcsv/default/upload" (it gives an error: "Error: Download file is not a .csv")
I have no other rules in this project. Mayby you can tell some other things i can check?
And do you have a working example online which i can see?
I want to thank you in advance for your time!!
Re: Problem installing ImportCSV
In your case you need to use "/index.php?r=/importcsv/default/upload" in "assets/download.js".
What is your import file name?
Re: Problem installing ImportCSV
Hi Ardem.
I've been playing around and followed your remarks.
Name of the CSV-file is "testImport.csv" (a link is provided on my testsite).
I have setup my testsite for you to see it yourself.
My testproject
When i upload a file a "index.html" from the upload-dir is openend in an i-frame.
Can you see what goes wrong on my testsite?
If you need more information please let me know...
Thx!!
Re: Problem installing ImportCSV
Hi Ardem,
Super app!! It works like a charme. I finally found what i did wrong. The "action" variable in downlaod.js was wrong.
Keep up the good work!!!!!
Thx!!
Re: Problem installing ImportCSV
Thanks, gijs.
I wrote you personal message, but it is not needed, because you understand all yourself.))
Good luck.
force *.csv
Hi Ardem,
Do you know how to force (in javascript file i guess) that the only file-extention the user sees in de "Open File"-dialog is "*.csv".
Now it shows "all files (.)", pictures (.jpg,.gif), HTML (*.html).
mayby you have a solution...
Thx,
Gijs
RE: force *.csv
Hi, Gigs
<input type="file" accept="MIME-types" />
But it will be work only in Opera > 10.50 and Google Chrome > 6.0
Artem
Error at third step
At third step I am getting this error if I choose the insert & replace old option.
Undefined variable: strCounter (E:\wamp\www\realestate\protected\modules\importcsv\controllers\DefaultController.php:206)
Thanks,
Utkarsh
delimiter error
hi i get a delimiter error when installed the module. i have made the folder 777...
Undefined variable: delimiter
protected/modules/importcsv/controllers/DefaultController.php(268)
267 $this->render('index', array(
268 'delimiter'=>$delimiter,
269 'tablesArray'=>$tablesArray,
270 ));
how can i fix this problem?
(i am using yii 1.1.7)
Re: delimiter error
Hi, ortenheim.
I corrected this error. Download new version please.
thank you
it works now :)
Suggestions
Hi,
this is a nice interface
I've got some suggestions to improve your model ImportCsv.
Better MVC
Thanks for your contribution to Yii!
I recommend removing the logic from secondResult.php and the other views and move it to a model.
Problem with keys?
Hi!
I have an CVS file without a primary key and would like to import the data sets to a table with auto-incremented primary keys (Insert all from cvs into a blank table).
This I leave table pole "id" unassigned. There is no error message after import, but no rows are imported at all.
Is that possible or do I have to introduce an id column to the cvs file?
Sebastian
Handling of relations
Hi
The idea looks very nice but i haven't tried it yet (Sorry!!) but i'm wondering how does it handle data from related tables ?
Thanks in advance
documentation and examples please
Thanks
Exclude table
Hello,
In my database I have a table users where I keep usernames and passwords.Is there a way to exclude that table from the list that gets loaded?Thnx
Re: Exclude table
Hi, giunz.
This list is formed in method "tableColumns" (/protected/modules/importcsv/models/ImportCsv.php). You can exclude this table from array, for example, in "/protected/modules/importcsv/views/default/secondResult.php" str. 35.
Problem installing this extension
Hi
I am interested in using this extension in my application but getting some errors. I have downloaded the folder in protected/models as stated. In main.php, what should be the path?
Thanks
RE: Problem installing this extension
Hi, Tad.
You must download this module into protected/modules. No protected/models.
RE: Problem installing this extension
Thanks Artem, I could resolve my problem. Had not set the url in path variable properly. Its a great extension.
Thanks
RE2: Problem installing this extension
Hi, Tad.
I wanted to answer to you, but you already solved your problem yourself.)
Problem making this extension work
Hello,
I also want to use this extension but actually I can't make it work. I am new to yii so I am still quite confused with the codes. Well can someone help me to make it work, I followed the steps but I am not quite sure with step 2 what does the path do or what must be the right path I think I am doing it wrong? http://localhost/gxi_sales/gxi_sales/index.php/importcsv/default is the url I use is it wrong? thanks sorry for asking these questions hopefully some one could help. God bless!
Kevin
Re:Problem to make this extension work
Hi Kevin,
The path should be the url of the folder where the uploaded file should be placed. I had created a upload/importCsv folder inside the Module/ImportCsv folder and had mentioned this path there. Hope this would solve your problem.
Tad
Re:Problem to make this extension work
Hello tad,
Thank you for helping me with this extension. If you have time can you please help me with it because I really can't make it work. I downloaded the file then created a modules folder in the protected folder. After that I put the importcsv folder inside the modules then I followed what you told me to create for the path but still I can't access it what is the right code or url for it? If you have time can you please guide me with the steps in making this extension work? Thanks. I am a student right now and don't have much exp with this thanks a lot but we need it for our system. Sorry for the trouble. Sorry if I am asking too much questions God bless :)
Kevin
Not Found
When i try to upload a csv file, i gotten a msg which says:
The requested URL /importcsv/default/upload was not found on this server.
this error seems to be coming from the download.js
any solution to this?
call module ...
Hi for me module is available on http://127.0.0.1/myproject/index.php?r=importcsv
This guide wrote :
4) The module is available at http://yourproject/importcsv (is incorrect?)
thanks
Re
Hi, paskuale
No, it's correct. It's entirely up to your path settings in protected/confing/main.php
Code quality
ImportcsvModule.php says
"error_reporting(E_ALL & ~E_NOTICE);"
Not nice..
file not imported
Hi, so I solved a little bug in download.js on this line
[javascript] action : '/importcsv/default/upload',
to
~~~
[javascript]
action : 'importcsv/default/upload',
~~~
so now it's ok but at the end it says that everything is ok but actuallt nothing is imported... Someone has this problem too?
many many issues with this module!
Hello ardem,
While you have obviously put a lot of work into this module, it has taken me a lot of work to get it function properly in my project. I suspect many others are having these kinds of issues too. I hope you are listening to all of the recommendations people have been making to improve your code and your documentation.
Like edoardo849, I had to remove the leading slash from the action in download.js
I had to put upload/importCsv directly under the project directory instead of under protected/modules/importcsv
I had to change the link in views/default/index.php for the 'Start over' breadcrumb from "/importcsv" to array("/importcsv")
I had a lot of problems because you don't handle the case where cell values are quoted. OpenOffice always encloses cell values in single or double quotes, and simply doing explode($delimiter, $filecontent[$i]) results in broken output from secondResult.php. You need to do str_getcsv($filecontent[$i], $delimiter, $quotecharacter) for users who have csv files with quoted values. You need to use file() instead of file_get_contents() addslashes() explode()
I also had a lot of problems because there were SQL errors being reported in application.log even though your module was saying "Import was carried out". The SQL errors were a result of your failure to quote table and column names with backticks in InsertAll().
You should explicitly say that the first row of the csv file must have column names.
You should explain what you mean by "pole" and how to configure the key comparison (or how to skip key comparison) and column matching.
This stuff might seem really obvious to you since you've been working on it, but anything you can do to help people get up and running quickly will earn you good karma.
Issues confirmed
Unfortunately many issues in this module. It is interesting module, but still many bugs in it - can't make it through second step, it doesn't show anything, just loading some css code as a result.
To fix uploading issue in my case I had to change in download.js
from:
/importcsv/default/upload
to:
index.php?r=importcsv/default/upload
Hope You are still working on it and reading reported issues to solve these bugs.
PS: more flexible way to handle different types of urls would be to pass urls from php to js script as parameter, becouse in php (Yii) You can generate proper url for the project.
Edit: Thx to lxvi I was able to solve my issue in second step by changing in DefaultController->actionIndex from:
$filecontent = explode("\n", file_get_contents($uploadfile)); $csvFirstLine = explode($delimiter, $filecontent[0]);
to:
$file = fopen($uploadfile, "r"); $csvFirstLine = fgetcsv($file, 0, $delimiter, '"'); fclose($file);
New release
Thanks for all reviews. I made a new release of this extension. And we have a repository in Github now. You could fork it if you want.
Really promising, but too many bugs
Ardem, this is really close to being a great module, but there are some serious issues that need to be resolved ....
As pointed out previously, you need to quote ALL field names in your SQL statements. Modifications are required in importCsv.php to complete this. Without this fix SQL statements can sometimes fail.
You are not catching errors in the SQL statement execution properly. Whenever an error occurs, Yii's exception handling writes a message in the application log and returns the user to a "Success" message. Hence, there is no user indication that the import has failed.
Your module does not seem to handle cases where a table uses an AutoIncrement primary key that is not included in the CSV file. (or at least I could not get it to work)
Re:
Ivanda, thank you for your review, I'll work on it. Could you please give me a little bit more information about this:
"Your module does not seem to handle cases where a table uses an AutoIncrement primary key that is not included in the CSV file."
Re:
My Bad. Importing to a table with an Auto Increment key is working.
Help with extension
I am very new to YII and this has been the easiest extension of all to incorporate - so thanks for a great module. In my specific application the CSV file will always have the exact same layout in terms of delimiters/field mapping - only the length of the CSV will vary (number of rows). Could you guide me in the right direction so I could use this extension however have things such as table name, delimiters, CSV Key etc as constants so the user only has to select the file and does not have to complete the intermediate steps. I see that a importCsvxxx.php file is created containing the parameters from the previous import - I would like to be able to use these parameters for the import so the user does not have to enter these parameters or even see these parameters - Ideally I would like to go from import CSV to insert into database skipping all the various parameter setups. I hope this makes sense.
Re: Help with extension
Good day Anthony,
Yes sure, you could create this import mechanism. You need to go to importcsv/controllers/DefaultController.php. In method "actionIndex" you can remove steps 1 and 2 and create custom condition, which will import to database (part of step 3).
Re: Help With Extension
Thank you heaps for your help. Great module - easy to customise and due to the great commenting very easy to tweak controller and javascript. Best Yii extension I have used.
Feature Request
Hi. Please consider adding some error messages when the import is not working. It would help a lot when trying to figure why it didn't work.
Hi it didn't worked for me
I had raw csv file from excel which has some empty fields, so it is inserting the empty data in new rows
I'm unable to use this
I'm able to import via cvs file using phpmyadmin with no problems, but when I use this for the exact file and settings I don't get any warning or error, and nothings gets imported.
Any ideas?
hello frnds...i got this error,please help me ....
Call to undefined function str_getcsv() in /var/www/subbu/yiiAddressBook/protected/modules/importcsv/controllers/DefaultController.php on line 192**
Re: hello frnds...i got this error,please help me ....
Hi Subbu,
You need PHP >= 5.3.0 for using this function. Also you can find more information here: http://php.net/manual/en/function.str-getcsv.php
After clicking NEXT button, nothing happens
After installing I can select the cscv file, it uploads correct to the path, but when I click on NEXT button nothing happens....
Any help appreciated.
Thnx
setup
My project separates the code into backend, common, console, and frontend, and I have installed the module in the module folder, a subfolder of the backend folder. When I try and access the module, I get an error that CWebModule can not be found.
I am not sure what I need to do to insure that following php code - class ImportcsvModule extends CWebModule will work.
Please note the the yii framework CwebModule class is located in the folder - \common\lib\Yii\web\CWebModule.
Any help would be greatly appreciated
With large CSV files
Did anybody face problems importing large CSV files?
I keep getting the error - Error: Download file is not a .csv - when i'm selecting a large file.
Any thoughts?
is it working with yii2
I have configured in yii 1.X versions successfully.
Is this module working for yii2 ?
Hi,
If I want to skip the wizard and just want to import the CSV directly after the upload is it possible?
If you have any questions, please ask in the forum instead.
Signup or Login in order to comment.