CODEWITHSUNDEEP
phpmysqlcsvimport
user1393955
user1393955

Reputation: 27

Import CSV into MySQL - With Fields Already Established

This may be a dumb question, but I can't seem to find an answer on google or programmatically create one. I have a huge CSV file called contacts2.csv (comma separated) that I want to import into my MySQL db. Now the tricky part is that I already have a bunch of fields in the DB that i want to place the data into, but they don't match up to the CSV.

For example, The first column may be "Company" on the CSV, but its actually the 5th column on MySQL. I also have a auto increment "id" column that is currently at 129483653 that I'd like to keep auto-incrementing as these are imported.

I found a few scripts online to show the data and to import into a new table, but I need to add all of these records into my existing table without overwriting anything.

Thank you

I used a CSV to MySQL converter, and got this for my output:

CREATE TABLE `tempcontacts` ( 
`company` varchar(255) NOT NULL DEFAULT '', 
`contact` varchar(255) NOT NULL DEFAULT '', 
`address_1` varchar(255) NOT NULL DEFAULT '', 
`city` varchar(255) NOT NULL DEFAULT '', 
`state` varchar(255) NOT NULL DEFAULT '', 
`zip` varchar(255) NOT NULL DEFAULT '', 
`phone` varchar(255) NOT NULL DEFAULT '', 
`fax` varchar(255) NOT NULL DEFAULT '', 
`title` varchar(255) NOT NULL DEFAULT '', 
`phone_ext_` varchar(255) NOT NULL DEFAULT '', 
`web_site` varchar(255) NOT NULL DEFAULT '', 
`e_mail` varchar(255) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `tempcontacts` VALUES 
('Construction', 'Bill Billy', '4201 State Route 1', 'Orlando', 'FL', '11111', '312-922-2125', '', '', '235', 'www.construction.com', ''),

Can't this just be somehow switching into an "insert" command for my actual table called "contacts"?

UPDATE: I think I may need to have someone help me write something custom in order to do all of this. I have tried:

http://www.codewalkers.com/c/a/Database-Code/PHP-CSV-Importer-20/

http://www.geeklog.net/forum/viewtopic.php?showtopic=71161

Among other sources.

Upvotes: 1

Views: 7419

Answers (2)

GDP
GDP

Reputation: 8178

A word from someone that travels this road a dozen times a week. The advantages of importing to a temporary table and using the SQL engine or PHP script to figure it out from there far outweigh the grief of trying to match/map and hope. Answered this earlier here. Just my opinion, I could be wrong.

Working from your edited question: That converter generated an INSERT statement that you could modify something like this. Change the table you to your final table, and adjust the column names shown to the actual columns in the final table withOUT change their order (the column names have to be in the same order as the: data below it, but does NOT have to be in the same order that they are in the table structure)

INSERT INTO `contacts` 
(`company`, `contact`, `address_1`, `city`, `state`, `zip`, `phone`, `fax`, `title`,     `phone_ext_`, `web_site`, `e_mail`) 
VALUES
('Construction1','Bill Billy','4201 State Route 1','Orlando','FL','11111','312-922-2125','','','235','www.construction.com',''),
('Construction2','Bill Billy','4201 State Route 1','Orlando','FL','11111','312-922-2125','','','235','www.construction.com','');

;

Upvotes: 1

Aaron F.
Aaron F.

Reputation: 94

I think your best bet would be reordering your mysql table to what your csv file order is. Then just reorder the mysql table in the order you want it. It's a mosh posh work around. even if you mess up the order everything is var(255) so you can just rename the column

edit:

As long as you have your rows in the correct order so that 1 is on line 1 and 2 is on line 2 etc. and your columns are in the correct order: Company, contact, address_1 etc. in your csv to match your table then you can just use a Data Infile script

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

an example when i had to import a csv file:

LOAD DATA INFILE '/usr/name/file.csv' INTO TABLE `mytable`
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

then just add your column primary key with an auto_increment

I used this because someone had an excel sheet with a bunch of stuff that i needed to import to the database one time. I am still learning everything with databases so I don't know if there is a security risk if you made this a routine script.

EDIT:

using one of the examples from http://dev.mysql.com/doc/refman/5.0/en/load-data.html

LOAD DATA INFILE '#{DATA_FILE_NAME}' IGNORE
INTO TABLE zipcodes
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\\n'
(city, state_code, @zip, area_code, county_fips, county_name, @preferred, timezone, 
dst, lat, lon, msa, pmsa, @city_abbreviation, ma, zip_type)

To do this though, all your lines (rows) in your CSV file should be following the same pattern. So say: order of company, ownerid, name, title, address, then a hard enter (\n)

so it should look like

PharmacyPlace, 2222, Bob Pills, Pharmacists Awesome Man, 51 Main st
Walmart, 12331, Anna Smith, Manager, 99 main st

keeping the same pattern

If you have a line that is any other way then you going to have to fix that but if you have:

Taco Bell,,,Manager, 59 Main st

then each comma will just tell it go to the next column.

so the script for this example to upload to your database would be

LOAD DATA INFILE 'DATA_FILE_NAME' INTO TABLE `your table`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(company, ownerid, name, title, address);

I would back up your table first, then copy it to another table as a temp to test this all out. You are going to have to some trial and errors. Like i said a mosh posh solution.

Upvotes: 2

Related Questions