LOAD DATA INFILE questions

matbennett

Bit stuck with a LOAD DATA INFILE question. I've broken this into 2 parts as I have a feeling that the second part may not be possible:

PART 1:
I need to swap the field order around whilst I am loading data into my table. for instance my CSV file may contain the following:

fred bloggs, 1 Some Street, Some Town, 111-111-1111
john doe, 7 the park, another town, 222-222-2222

Assuming that my table structure is:
ID (auto increment)
Name
Phone

How do I get the data into the correct fields?

Part 2:
This is the bit that I have a hunch will not be possible:
Can I manipulate the data in a standard way on the way in? For instance, in the example above could I add "Mr. " to all the name fields as they were entered onto the database?

Any help with this would be appreciated, as I find the mySQL manual is a lot harder to get information from than the PHP one!

Ta'

Norman_Graham

Hi Matt

I struggled with INFILE commands for a while. I've since stopped using txt/csv files and now use sql files with the full commands.

Anyway, in a csv file, the entries go into the fields in the order in which they appear. If you want to skip a field, you have to enter NULL, which can do by putting \n in the appropriate spot in your file.

I used to use tab-separated values. I had a txt file and put a single tab between each value. NB: You have to put a tab at the end of each line before hitting the carriage return. The Notepad editor that comes with Windows is a completely garbage tool for creating these files because you can't make either tabs or carriage returns visible. I used to create the files in WORD and then paste them into a txt file once I'd checked everything. A decent text editor, however, would sort this problem.

To answer your other question: to the best of my limited knowledge, you can't automatically add "Mr." to one set of field values. However, I would suggest that it's bad practice to have different types of information in an SQL field. I suggest you make a table with: first_name, last_name, title [i.e. Mr, Ms., Mrs. Dr. etc.], town_city, street_name, street_title [i.e. avenue, terrace, boulevard, street etc.], street_number, area_code, phone_no etc. etc. I like to split data like this up into little pieces so that each field contains only one type of data. This makes it a lot easier to correctly define conditions in WHERE statements.

HTH

Norm

matbennett

Hi Norm & thanks for the reply.

Unfortunately I have no control over the data itself as it comes from an inflexible third party, otherwise I would have done this how your suggested (that is how I have always done it before as well).

The Mr example was actually pretty poor, a case of me trying to over-simplify. What I am actually trying to do is to retrieve product information from a variety of sources and get them into a common format. "mr" would actually be a supplier number that prevents different suppliers using the same product reference numbers.

I was really hoping to get everything formatted in one-hit, but it now looks likely that I will have to put the data into a temporary table, reformat it all then copy it to where I want it. Fun fun fun! 🙂

maxpup979

What you may want to do then, is write a PHP script that opens the .csv file, reads the data, manipulates the data as you need it, and then throws an insert at the DB--

something like:

<?php
$fp = fopen ("test.csv","r");
while ($data = fgetcsv ($fp, 1000, ",")) {
$insert="insert into table(name,phone,address,city)values(";
$insert.="'MR. ".$data[0]."','".$data[3]."','".$data[1]."','".$data[2]."');";
print $insert."<br>";
}
fclose ($fp);
?>

Drakla

If you couldn't be buggered writing the extra php code you could do it all with MySQL like this...

do the Load Data Infile into your tempTable, which say has columns field1, field2, field2 then:

INSERT INTO originalTable (field2, field1, field3) SELECT field1, CONCAT("Mr. ",field2), field3 FROM tempTable;

Jiggle your fields as appropriate. Then drop your tempTable.

I'm far from the voice of experience, though, but am incredibly lazy and that does wonders sometimes.