1

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.

user1393955
  • 27
  • 1
  • 9

2 Answers2

2

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.

Aaron F.
  • 94
  • 1
  • 9
  • Hey thanks for the answer - I don't think I can do that.. there's too many functions and queries pulling by row number instead of row name.. :( – user1393955 May 25 '12 at 17:19
  • That shouldn't be a problem if you put it back in the right order. All you are trying to do is get the data from the csv into the database. Problem is you can't rearrange the csv but you can rearrange the table. After you get all the data in the table, then you can move the columns back in the correct order ie: ALTER tempcontacts MODIFY COLUMN contact VARCHAR(255) AFTER company; – Aaron F. May 25 '12 at 17:23
  • I can rearrange the CSV fine. I just need to be able to import the CSV and the auto-increment to do its job also.. Can you update your answer with a proposed script? – user1393955 May 25 '12 at 17:26
  • 1
    LOAD DATA INFILE lets you specify the fields so you don't have to re-arrange anything. If you leave out the autoincrement field in your csv file, it will generate a value for you. – Kibbee May 25 '12 at 17:49
  • question: are the people you adding from the csv file continue the auto increments or do they have their own auto increments? So say your table right now ends at 100, does your csv file start at 101? or does it start at 1? – Aaron F. May 25 '12 at 17:49
  • @Kibbee Oh yea, i totally forgot about that. +1 for you. thanks – Aaron F. May 25 '12 at 17:52
  • @CaucasianAsianX my CSV has nothing set for the auto increment. I want it to pick up where it leaves off in the sql. So using the script above, should I name the fields the same on the CSV as my table? – user1393955 May 25 '12 at 18:01
  • @CaucasianAsianX There are some fileds that aren't in a straight shot either. if its company, name, address in the CSV, it may be company, ownerID, name, title, address in the table. does that matter? – user1393955 May 25 '12 at 18:06
  • Once you add to the table, it should continue the auto_increment of the primary key. using that script will take the first column (company) and then the first phrase up to the ',' (comma) and stick it in company. (horrible english sorry). So you need to have your csv be in order. As @Kibbee said, you can actually specify which field are in which order. I will re-edit to show you an example. But if you have lines in your csv where it doesn't follow the same pattern of say company, id, name, title and it is id, name, company, title down a several lines, then you going to have to fix that. – Aaron F. May 25 '12 at 18:11
  • @CaucasianAsianX okay that is fine. I can reorder the CSV. But what I am saying is that there may be a few columns before and after each. Should I just make them look the same? For example: my CSV is company, contact, address1, city, state, zip. My table is id, company, ownerID, contact, address1, address2, city, state, zip. – user1393955 May 25 '12 at 18:19
  • @CaucasianAsianX great example! I use GoDaddy and their hosting is weird with MySQL, where should I put the actual CSV? Just at the very root of the hosting I assume? – user1393955 May 25 '12 at 18:23
  • yea correct of where you put it. I updated my edit again to give you a better example so you don't' have to rearrange your csv. Don't forget to vote me up if this helped :P We probably should have moved into a chat...I'm still learning this website :) hopefully a moderator doesn't get mad at us. – Aaron F. May 25 '12 at 18:23
  • @CaucasianAsianX You will be marked correct as soon as this is done! :) But what about the very first field (the auto increment) should that be named in the CSV and SQL statement? – user1393955 May 25 '12 at 18:28
  • you won't have to mark anything because you don't have a "column" for that in your CSV. As long as you make sure you list every column (company, address, etc) to correspond where the position is on your csv, it should just ignore your pk(primary key (auto increment)). Make sure you do this on a temp table liek i said cause you can't go backwards (undo) after you add to a table. Well you can but it will take forever – Aaron F. May 25 '12 at 18:32
  • I get a: Access denied for user 'user'@'%' (using password: YES) when trying this on Godaddy – user1393955 May 25 '12 at 18:40
  • Add a LOCAL to LOAD DATA INFILE so it is LOAD DATA LOCAL INFILE. Check http://stackoverflow.com/questions/2221335/access-denied-for-load-data-infile-in-mysql for more. – Aaron F. May 25 '12 at 18:43
  • Awesome, I'm glad it did on the first run. – Aaron F. May 25 '12 at 18:45
  • @CaucasianAsianX I am really surprised how easy that was, there is nothing helpful on google at all. – user1393955 May 25 '12 at 18:51
  • I had the same problem, this was my first thing i learned how to do when I was hired as a student assistant. Took some time for me to figure it out and understand it. Glad that I can share the fire. Now others can google and hopefully find this :) – Aaron F. May 25 '12 at 18:52
  • @CaucasianAsianX I have another question. Give me a second to post – user1393955 May 25 '12 at 19:24
  • @CaucasianAsianX http://stackoverflow.com/questions/10760334/update-table-based-on-condition-while-loop – user1393955 May 25 '12 at 19:31
1

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','');

;

Community
  • 1
  • 1
GDP
  • 8,109
  • 6
  • 45
  • 82