RabPHP Posted October 27, 2006 Share Posted October 27, 2006 Greetings,I have a tough issue. I use text files extensively to read data and then enter it into MySQL. I use semi-colons for field delimination and then typically a new line between records.Recently I found that I also need to add another field to one of the text files, but this field has hard returns in it. This causes PHP to read each return in this field as a new line and thus a separate recordI believe I either need to figure out how to specify the record ending delimiter or use text qualifiers. Does anyone have any information on either of these?Rab Quote Link to comment Share on other sites More sharing options...
Psycho Posted October 27, 2006 Share Posted October 27, 2006 There are some easy solutions depending upon the type of data this is. If this is data to be displayed you could replace all the line breaks with \n or <br> before you save to the database.Or, if that would not work for this data, you could still replace the returns with a "key" before inserting into the database and then replace the "key" with hard returns when you read the data back out from the database. Quote Link to comment Share on other sites More sharing options...
RabPHP Posted October 27, 2006 Author Share Posted October 27, 2006 The problem is differentiating bewteeen a new line. Here is some example data.Joe;Smith;09/07/06;10/15/06 - Some Entry10/15/06 - another entry10/16/06 - Third Entry Log;Jane;Doe;09/08/08;10/12/06 - Some Entry10/12/06 - another entry;After the first name and lastname there is date. PHP sees this as the end of the record because of the new line even though the entries after them belong to the same record. I cannot remove the returns in the last field, because PHP thinks they are new lines, not just a field.Anyone have an example of how to parse text files with text qualifiers? For example.."Joe";"Smith";"09/07/06;""10/15/06 - Some Entry10/15/06 - another entry10/16/06 - Third Entry Log";"Jane";"Doe";"09/08/08";"10/12/06 - Some Entry10/12/06 - another entry";Help appreciated. ThanksRab Quote Link to comment Share on other sites More sharing options...
effigy Posted October 27, 2006 Share Posted October 27, 2006 Are you using MySQL's[tt] LOAD DATA[/tt]? If so, you can specify the surrounding text delimiters--the double quotes. Quote Link to comment Share on other sites More sharing options...
RabPHP Posted October 27, 2006 Author Share Posted October 27, 2006 I am pulling the data from a text file and then updating the appropriate records in MySQL. Quote Link to comment Share on other sites More sharing options...
Psycho Posted October 27, 2006 Share Posted October 27, 2006 Using you example data above, this should work:[code]<?php$fileAry = file('file.txt');$fname = "";$lname = "";$date = "";$entries = "";foreach ($fileAry as $line) { if ($fname=="") { //This is the first line of a new record $pieces = explode(";", $line); $fname = $pieces[0]; $lname = $pieces[1]; $date = $pieces[2]; } else { if (substr($line,strlen($line))!=";") { //Not the last line of the record $entries .= $line } else { //last line of the record $entries .= substr($line,0,strlen($line)-1) //Do the sql statement to insert the values //Reset the values $fname = ""; $lname = ""; $date = ""; $entries = ""; } }}?>[/code] Quote Link to comment Share on other sites More sharing options...
RabPHP Posted November 7, 2006 Author Share Posted November 7, 2006 I appreciate the help mjdamato. I've been working with your suggestion for the past couple of days and have not been able to get it to work. I've changed how I'm exporting the data in those attempts to try and simplify.Here is a real example of the data now...121222;10/05/2006 8:37AM - Notes for this loan are in the 1st file- John Doe|121223;11/02/2006 8:41PM - was told would have UW update today by asst--still didn't receive--phoned the UW--she left at 4:00-sent an email to her and the asst asking for update--told we are trying to close next week and needed an uw update on Friday urgently. Will follow up again on Friday. - John Doe11/01/2006 11:09PM - no updates from UW yet on items sent over--will follow up on Thursday. - John Doe|121224; 11/04/2006 10:14AM Notes for this loan are in the 2nd File file- John Doe|Here you can see the following...Records are seperated by PIPE "|"Fields are seperated by Semi-ColonEntries in the last field are seperated by a New Line if there are more then 1 entry.I can explode the records seperated by a | without a problem, but then how do I explode the parts in the record by a semi-colon and then in the last field, by a new line if one is present?I'm in a pickle, if anyone can help please let me know.Rab Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted November 7, 2006 Share Posted November 7, 2006 If the fields that contain the newlines can also contain a semi-colon then you're really in trouble.I recently had to battle importing dbase memo fields from Visual FoxPro into MySQL text fields. The solution there was to use more complicated delimiters. I separated fields with [<>] and records with <[]>.So:fld1[<>]fld2[<>]fld3[<>]memo fieldthat hashard returns in it<[]>fld1[<>]fld2[<>]fld3[<>]another memofield with hardreturns<[]>For my import code, I did something like:[code]<?php$file = file_get_contents('the/file/path.txt');if(strlen($file)){ $Records = explode( '<[]>', $file ); if(is_array($Records) && count($Records)){ foreach($Records as $Key => $Record){ $Fields = explode( '[<>]', $Record ); if(is_array($Fields) && count($Fields)){ foreach($Fields as $FieldKey => $FieldVal){ $Fields[$FieldKey] = trim($FieldVal); } $Records[$Key] = $Fields; }else{ unset($Records[$Key]); } } }}?>[/code]The fun part about that was our FoxPro programmer, who has at least 5 years experience, couldn't figure out how to export the memo fields intact. I sat down and figured it out in 3 hours on a language I'd never even heard of until I started working here. Go figure!Hope that helps you out. Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 7, 2006 Share Posted November 7, 2006 This works with your sample data. It just puts the data into an array, but you can do with it what you want.[code]<?php//Open the file to be parsed$filename = "data.txt";if (!($fp = fopen($filename, "r"))) { die("cannot open ".$filename); }$cleanData = array();$currentRecord = "";//parse the datawhile ($line = fgets($fp)){ $line = str_replace("|","|EOR|",$line); $data = explode("|", $line); foreach ($data as $value) { if ($value!="EOR") { if ($currentRecord) { $currentRecord .= ";"; } $currentRecord .= $value; } else { processRecord($currentRecord); $currentRecord = ""; } } }echo "<pre>";print_r($cleanData);echo "<pre>";function processRecord($record) { global $cleanData; $record = explode(";", $record); $acctNo = $record[0]; $cleanData[$acctNo] = array(); for ($i=1; $i<count($record); $i++) { $cleanData[$acctNo][] = $record[$i]; }}?>[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.