• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Importing from .csv with table lookups

I have a CSV file that I want to import data from into an existing MySQL database. However, during the import I need to look certain values in order to insert the records correctly into the database. For example, I need to lookup the CompanyID using the CompanyName which is found in both the DB and the CSV file. I have done stuff like this in the past for Microsoft SQL Server using SSIS but wasn't sure if there was a similar type of tool for MySQL.
0
mikedgibson
Asked:
mikedgibson
  • 2
1 Solution
 
dqmqCommented:
Import the CSV "as is" to a staging table.  Then populate the final table by joining the staging table to the lookup table(s) to retrieve the foreign keys.  

0
 
nemws1Database AdministratorCommented:
How are you doing the import now?  Are you using LOAD DATA LOCAL INFILE?

Usually in these cases, I'll write a short program to parse the CSV file and load it, and within this script I'll do any lookups I need to do (along with any other data munging/fixing that I might need to do, like removing extra spaces from TEXT fields or making sure numbers are formatted the way I want them).

However, you can certainly do as dqmq suggests and that might work better for you, especially if you have a very large number of rows that you need to import on a regular basis.
0
 
K VDatabase ConsultantCommented:
I think this can be done along with LOAD DATA itself:

consider you have b.csv with ID values and you need to populate value from another table. You can do it as follows:


load data local infile 'c:/b.csv' into table table1 lines terminated by '\n' (ID) set value=(SELECT value-to-be-set from table2 where table2.ID=table1.ID);

Refer This: http://kedar.nitty-witty.com/blog/load-delimited-data-csv-excel-into-mysql-server
0
 
K VDatabase ConsultantCommented:
@mikedgibson!
I'm fine with no-points but I assume solution I provided will work as well!!!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now