MySQL: LOAD DATA LOCAL INFILE is lost/not found

Databases
1

Hi people! I am running MySQL 5.6.17-debug (Homebrew install) on Mac OS X 10.7.5. I am having a problem getting imported .CSV data to persist from session to session. No Google search seems to address this scenario!

I am importing .CSV data with the statement as follows:


USE myDB;


DROP TABLE tbl_1;


CREATE TABLE tbl_1 (
  myType VARCHAR(10),
  Month VARCHAR(2),
  Day VARCHAR(2),
  Year VARCHAR(4),
  Description VARCHAR(360),
  Amount NUMERIC(10,2),
  Check_Slip VARCHAR(24)
  );

LOAD DATA LOCAL INFILE '/Users/me/Downloads/file.CSV' INTO TABLE tbl_1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\
';

Then, I build a tblMain using the same instructions that I used to build tbl_1. Finally, I pull the data from tbl_1 into tblMain with:


USE myDB;
UPDATE tblMain 
SET 
  tblMain.myType = tbl_1.myType
  ,tblMain.Month = tbl_1.Month
  ,tblMain.Day = tbl_1.Day
  ,tblMain.Year = tbl_1.Year
  ,tblMain.Description = tbl_1.Description
  ,tblMain.Amount = tbl_1.Amount
  ,tblMain.Check_Slip_Num = tbl_1.Check_Slip
WHERE 1 = 1;

I get the following error message:
“Error Code: 1630. FUNCTION tbl_1.column does not exist. Check the ‘Function Name Parsing and Resolution’ section in the Reference Manual”

I have tried different permutations of this method, but it seems like when I return to work on the db, it can’t find the columns (or the table itself!) of the original imported .CSV data.

  1. What mistake am I making?
  2. What is the ideal way to import .CSV data into MySQL?

Thanks in advance!

2

your update statement refers only to one table, hence the qualifier tbl_1 results in an error

i can’t tell whether you really want to do an update, but it sure sounds like you just want an insert –

INSERT 
  INTO tblMain 
     ( myType         
     , `Month`          
     , `Day`            
     , `Year`           
     , Description    
     , Amount         
     , Check_Slip_Num )
SELECT myType           
     , `Month`       
     , `Day`         
     , `Year`        
     , Description 
     , Amount      
     , Check_Slip  
  FROM tbl_1

note the backticks around some problematic column names – you should probably rename these to avoid being forced to use backticks all over the place

3

I think you want to insert data from tb1 to tbmain, so you should use insert into as previous post said, for update you should have unique key on table, the query should like
update tb1, tbmail set tb1.column=tbmaim.column where tb1.key=tbmain.key