Welcome Guest, Not a member yet? Register   Sign In
importing a csv into the db
#21

[eluser]alberto24[/eluser]
removed
#22

[eluser]alberto24[/eluser]
[quote author="ray73864" date="1225857031"]as i said before, you need to copy the file to a temporary directory, php can copy to /tmp/ so create an upload script that uploads the file to /tmp/ then use that in the load data infile and when the import is complete have php 'unlink' the file.[/quote]

same error with the tmp file

here's the full error:

Code:
A Database Error Occurred
Error Number: 1045

Access denied for user '369706_cloud1'@'%' (using password: YES)

LOAD DATA INFILE "/tmp/moo.csv" REPLACE INTO TABLE csvtest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (f_name, l_name, country, @dummy, phone)

and full code

Code:
function index ()
    {  
    if ( !copy("/mnt/target03/fullpath/csv/test1.csv", "/tmp/moo_item.csv") )
            $data['message'] = "Could not copy CSV file to temporary directory ready for importing.";

        $query = $this->db->query("LOAD DATA INFILE \"/tmp/moo_item.csv\" REPLACE INTO TABLE csvtest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (f_name, l_name, country, @dummy, phone)",array('/tmp/moo_item.csv'));
        
        if ($query) {
            $data['message'] = "All items imported successfully.";
        } else {
            $data['message'] = "Import failed.";
        }
        
        unlink ("/tmp/moo_item.csv");

    }
#23

[eluser]ray73864[/eluser]
i would ask that you change the 'capelite' to something else as that is the name of a business i am doing a website for.

the error you have given sounds like your mysql username does not have the 'FILE' permission granted on it, dunno if your host would be willing to give it to you, i don't see why not though since they should be able to grant that permission on a per-database basis.
#24

[eluser]alberto24[/eluser]
[quote author="ray73864" date="1225864224"]i would ask that you change the 'capelite' to something else as that is the name of a business i am doing a website for.

the error you have given sounds like your mysql username does not have the 'FILE' permission granted on it, dunno if your host would be willing to give it to you, i don't see why not though since they should be able to grant that permission on a per-database basis.[/quote]

Thanks Ray - changed the file name - sorry about that.

If the import doesn't work, how come it doesn't spit out the error message and instead just sits there blank?
#25

[eluser]ray73864[/eluser]
in my full code for it, $data['message'] is going to a view $this->load->view('import',$data);

if you want to see the error message then you will need to echo it out somewhere, for testing purposes you could just do:
Code:
if ($query)
  echo 'Import Succeeded';
else
  echo 'Import Failed';
#26

[eluser]alberto24[/eluser]
fyi in speaking with host now - they tell me that my db user is set to the highest possible level of permissions
#27

[eluser]Randy Casburn[/eluser]
"Access denied for user '369706_cloud1'@'%' (using password: YES)"


This user needs a MySQL account on the local host. 369706_cloud1'@localhost with the correct privileges will fix the problem.

I'll explain why when I get time.

Randy
#28

[eluser]megabyte[/eluser]
Solved, I had a silly typo.


I tried load data infile.

Any ideas why I am getting this error?

Code:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'products product_code FIELDS TERMINATED
BY ',' OPTIONALLY ENCLOSED BY '"' LINES ' at line 1

LOAD DATA INFILE '/tmp/CATALOGUE.csv' REPLACE INTO products product_code FIELDS TERMINATED
BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (id, cat_id, product_code,
gst, case_size, list_order, product_name, price1, price2, price3, price4, price5)

Here is my table structure, the last 3 columns in the table do not exist inthe csv and are to be updated later.

Code:
--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL auto_increment,
  `cat_id` varchar(20) NOT NULL,
  `product_code` varchar(10) NOT NULL,
  `gst` varchar(3) default NULL,
  `case_size` float NOT NULL,
  `list_order` int(11) NOT NULL,
  `product_name` text NOT NULL,
  `price1` decimal(11,2) NOT NULL,
  `price2` decimal(11,2) NOT NULL,
  `price3` decimal(11,2) NOT NULL,
  `price4` decimal(11,2) NOT NULL,
  `price5` decimal(11,2) NOT NULL,
  `description` text NOT NULL,
  `photo` varchar(255) NOT NULL,
  `created` date NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `product_code` (`product_code`),
  KEY `Id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Will this still work if none of the coumns in the csv are named? The csv just starts off with rows of data right away
#29

[eluser]umefarooq[/eluser]
hi try this library its is working fine to import CSV in you database have look on it. really nice library.

http://codeigniter.com/wiki/CSVReader/
#30

[eluser]btray77[/eluser]
I know this is an old thread, but I've run into this same problem...

try adding LOCAL

like:
Code:
LOAD DATA LOCAL INFILE

This works on my server, without local it does not work on my shared host.

-Brad




Theme © iAndrew 2016 - Forum software by © MyBB