Importing CSV to MySql (Mapping)

[pewe]

I have a mysql table which has a number of fields and I need to import data from an upload csv files.

 

As long as the order of the data in the csv files is set I have a script that works.

Here is the part of the script that reads the csv data and adds to the mysql table (the first line of the csv file must always contain the column headers hence the first line of this code to eliminate them from transfer) :

if($data[0] !='Latitude'){
           $timestamp = strtotime(str_replace('/', '-', $data[2]));
           mysql_query("INSERT INTO temp(
           Latitude,
           Longitude,
           DateOccurred,
           Altitude,
           Speed,
           Tripname,
           Angle
           )VALUES(
               '".mysql_real_escape_string($data[0])."',
               '".mysql_real_escape_string($data[1])."',
               '".mysql_real_escape_string(date('Y-m-d H:i:s', $timestamp))."',
               '".mysql_real_escape_string($data[16])."',
               '".mysql_real_escape_string($data[15])."',
               '".mysql_real_escape_string($data[6])."',
               '".mysql_real_escape_string($data[8])."'

           )")or die(mysql_error());

However there are occassions where the data in the csv file may be in a different order (still with headers in the first line) and what I need to do is to allow the uploader to specify which csv fileds should be added to which mysql table field.

 

I have very limited knowledge with coding and wonder if someone could give me some idea of how I may approach this problem.

 

Thanks in advance of any assitance.

[mac_gyver]

you would get the field names from the header line into an array and have an array of the fields you want. use array_intersect to filter out all the field names that you don't want between the first array and the second. the keys in the resulting array will be the index offsets [0],[16],[6], ... in the $data array for the corresponding field name.

 

if you post an example of the format of the header line someone could post code showing how to do this.

[mac_gyver]

as a continuation of the above reply, once you have the result from array_intersect, you can use array_intersect_key against the $data array to get just the correct data values from it.

 

quicker to just post some code -

// the fields you want (these can be in any order)
$want = array('Latitude','Longitude','DateOccurred','Tripname','Angle','Speed','Altitude');

// the fields from the header line (code to get this from your data is left up to you)
$have = array('Latitude','Longitude','DateOccurred','field3','field4','field5','Tripname','field7','Angle',
    'field9','field10','field11','field12','field13','field14','Speed','Altitude','field17');

$map = array_intersect($have,$want); // get only the fields you want
$fields = implode(',',$map); // use this to make the list of field names in the query statement
$date_field = array_search('DateOccurred', $map); // find the position of the date field for special handling


// make up some test data. the order of fields matches the $have array (your code would get this from the file)
$data = array('Latitude1','Longitude1','11/4/2013','field31','field41','field51','Tripname1','field71',
    'Angle1','field91','field101','field111','field121','field131','field141','Speed1','Altitude1',
    'field171');

// processing of the data starts here ----------------------------------------
$data = array_intersect_key($data,$map); // get only the data fields you want
$data[$date_field] = date('Y-m-d H:i:s',strtotime(str_replace('/', '-', $data[$date_field]))); // format the date
$data = array_map('mysql_real_escape_string',$data); // treating all fields like strings and apply escape
$sql =  "INSERT INTO temp ($fields) VALUES ('".implode("','",$data)."')"; // treating all fields like strings

echo $sql;

Edited April 11, 2013 by mac_gyver

[pewe]

Thank you mac.

 

The header line of an example csv (comma separated) is below:

Latitude,Longitude,DateOccurred,ID,IconName,Comments,TripName,ImageURL,Angle,CellID,SignalStrength,SignalStrengthMin,SignalStrengthMax,BatteryStatus,Source,Speed,Altitude

The sql database table has the following fields (as can be seen they are in a different order).

ID,FK_Users_ID,FK_Trips_ID,FK_Icons_ID,Latitude,Longitude,Altitude,Speed,Angle,DateAdded,DateOccurred,Comments,ImageURL,SignalStrength,SignalStrengthMax,SignalStrengthMin,BatteryStatus

SORRY - cross posted

 

Thank you for the code - I assume this just replaces the code I posted earlier???

Edited April 11, 2013 by pewe

[mac_gyver]

the order doesn't matter (see the code i posted.) the field list in the query and the data in the query match each other.

 

if any of the fields you do want are not present in the actual data file, you would need to set up appropriate default values in your database table since the code won't include a column name that isn't in the header line.

[pewe]

Thanks Mac.

 

I can't do any tests at present as the web server is down with network issues and the host says it may be a few hours before its back up.

 

However, reading the code at greater leisure I think I did not explain what I was after very well.

 

To illustrate, here is a link to a script that should do what I'm after, but it is quite a while since it was published and I could not get it to work when I tried it - and the developer does not seem to respond to any comments for assistance.

 

csv-import-with-mapping

 

[mac_gyver]

everything i posted had to do with working with an arbitrary order of the fields in the csv file, not relating a specific csv header name to its corresponding database column name.

 

do you want to do this upon each import or do you just want to set up a static definition that relates the csv header names, like "DateOccurred" to a database column name, like "date"? to do the former, you would need a script like the one you linked to. to do the latter, you could use the code I posted and just add one array that lists the database table columns and build the field list in the query from that array.

Edited April 11, 2013 by mac_gyver

[pewe]

do you want to do this upon each import or do you just want to set up a static definition that relates the csv header names, like "DateOccurred" to a database column name, like "date"?

 

Ideally the format of the csv file should always be the same - in which case a static definition would suffice.

 

Unfortunately that would not be the case. The csv files could come from a variety of sources and the problem is that although the base content would be the same (eg Latitude, Longitude, Date/Time etc) it would not necessarily be in the the same order depending on its source (ie different makes of GPS Data Logger), and there may be other data which would be superfluous for use with the database in which the data is stored.

 

Ideally creating a number of import scripts hardcoded (like my original one) for each type of logger file would be a solution - giving the uploader the facility to access the appropriate script from a choice on a link page. Unfortunately that has its practicality issues, and keeping up with changes could be a nightmare.

 

So, the only answer I could come up with short term was to have a script which showed a table with the field headings of the csv file and a drop down option for each field which allowed the user to select which database table each field should be imported to.

 

Over time, for user convenience, I could build hardcoded pages for specific popular formats, but still allow the option for people to choose the 'mapping' option to upload files that don't fit the 'standard' options.

 

I hope this makes sense.

 

I thought the scipt I linked to showed real promise when I first saw it, but as I said it unfortunately did not work 'out of the box' and was too complicated for me to debug with my limted coding knowledge.

 

 

[mac_gyver]

a form page like that linked to script is using would be simple to create.

 

1) read the first two lines of the uploaded csv file. extract the header names from the first line and extract sample data from the second line.

 

2) read the database table definition to get the column names and produce the drop-down menus.

 

3) when the form is submitted, any drop-downs that have been picked will define which csv column name goes with a database column name. any drop-downs that have not been picked don't map to anything and would cause that csv column to be ignored in the data.

 

the submitted information (or sets of data you have saved somewhere) would produce a map like this -

// list of csv header names to database column names
$field_list['Latitude'] = 'latitude';
$field_list['Longitude'] = 'longitude';
$field_list['DateOccurred'] = 'date';
$field_list['Tripname'] = 'trip';
$field_list['Angle'] = 'angle';
$field_list['Speed'] = 'speed';
$field_list['Altitude'] = 'alt';

the $want array in the code i posted above would be produced from the $field_list array keys. the $fields variable in the code i posted would need to loop through the resulting $map array to get the csv fields in the same order as the data will be and get the actual database column names out of the above $field_list array.

[pewe]

Thanks again mac.

 

I'll study your code in more detail and embark on some reading