Help parsing comma delimited txt file! Thanks!

brianb

Hi,

I'm trying to create a script that parses a bunch of data from a text file.

The text file looks something like this:

"02110651","Lucas","Scott C."
"02110655","COOPER, JR.","WILLIE"
"02110718","Tinker","William"

etc. etc. etc.

Now, what I'd like to do is parse each of those elements into their own database entries, and then throw the data into the proper fields.

For instance, the entry would look like this:

Entry 1: 02110651 into 'account field', Lucas into 'Last Name' field, Scott C. into 'First Name' field

...then new entry, each variable going in accordingly.

Basically, I just need to know the easiest way to code a little script to parse the data in which each field is being seperated by "," and each new entry is on a new line.

Any and all help is greatly appreciated!

Thanks in advance!

scoppc

I dont have much time to explain this but basically to get line by line from the text file, use fget()
Separate all the contents by the comma, use explode()

HTH.

brianb

Thanks.

Can you possibly give me a quick coding example?

I am confused about splitting entries line by line.

Any help is appreciated!

Thanks.

tasistro

$file_data=file($yourfilehere);
for ($counter=0;$counter<count($file_data);$counter++) {
$file_row_data=explode("\n",$file_data[$counter]);
// now $file_row_data is an array with all the fields in it $file_row_data[0] being the first element in the row
// now you have to make the proper SQL command out of this data to insert into the dbase
}

brianb

Hey guys...

Ok, check this out:

Here's my script:

$row = 1;

$parse = fopen($filename, "r");

while ($data = fgetcsv($parse, 10000, ",")) {
    $num = count ($data);
    print "<p> $num fields in line $row: <br>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        print $data[$c] . "<br>\n";
    }
}

fclose ($parse);

Now the output is here:
http://mortgagesfastquickapply.com/xml/test2.php?id=2003_07_20_2010.txt

We're parsing this file:
http://mortgagesfastquickapply.com/xml/input/2003_07_20_2010.txt

Now, why isn't this working correctly? All of the fields are present the way they should be on their own lines, but still the script fails to parse this file correctly.

Please help.

Any and all help is greatly appreciated!

Thanks once again in advance.

agoe

$lines = file ('file.txt');

foreach ($lines as $line) {
$line_array = explode (",", $line);
//do the sql-queries $line_array[0], [1], [2] is the line-elements
}

think that should do it

brianb

Hmmm...

Now, I can get this fucntion to work, BUT I think fgetcsv is going to be better suited to my needs...especially when some elements between the quotes have commas....so it's breaking wrong.

Any ideas on how to get my fgetcsv script working? or, is there a better solution I should be working towards?

Let me know!

Thanks again!

feldon23

why not explode on ","?

brianb

See...there is my problem, some fields are enclosed by "", but then some are not.

Is there a simple way to convert fields NOT enclosed by "" to become enclosed by them?

For instance, here's an example of a line:

"02110655","COOPER, JR.","WILLIE","19446 COYLE STREET","DETROIT","MI","48235",$2110655.00,"Cash-Out/Debt Consolidation",2/21/2003 0:00:00,1/10/2003 0:00:00,"19446 COYLE STREET","DETROIT","MI","48235","WAYNE",47.39,$82650.00,None,8.25,$620.92

Notice how some fields are enclosed by quotes and others are not. I think this would be really simple if I could enclose ALL fields in quotes before I start parsing the lines.

Maybe some kind of replace function? I'm stuck.

Any coding help is appreciated!

Thanks.

ybinds

Hello,
See if the following code can help you out.

$fp=fopen("filename.txt","r");
while(!feof($fp))
{
	$buffer=fgets($fp);
	$buffer=chop($buffer);
	$sql="Insert into $table Values($buffer)";
	$result=mysql_query($sql);
}
fclose($fp);

Actually, you don't even need to parse them if you have your database fields in the same order as that of the fields in your text file. According to the example that you showed, only the string fields are enclosed with in the quotes and numeric values are not. So, try directly inserting the buffer. I am sure it should work fine.

In the above code, I am opening the file in the read mode, extracting each line into $buffer, removing the new line character and then inserting the values into the table. Hope this will help you.

brianb

Alrighty.

Got your script partially running. I've decided to instead of running the data into a database, I'm going to be creating an XML file of the data.

So, I need to pull each element in the array.

With your script above, how would I call the individual elements? Something like $line[0], $line[1], $line[2], etc. etc. etc.

Thanks again! Please help!

ybinds

U too are facing the same problem which I initially faced with these kind of data files. You can actually split them using either split or explode functions with a delimiter of ",". But if you observe there are few fields which include "," within the field contents. Even those will get parsed. And another problem is that we cannot explain to the function that fields within quotes are seperate fields. Hence why don't you do one thing, dump all this data in mysql database with the code that I gave you earlier and retrieve the fields fromt he db. Though its lengthy procedure, works fine.

feldon23

Can I ask who the hell came up with this file output format you're having to deal with?

It breaks every possible rule of CSV and QCSV.

brianb

yeah I know...

The file sucks. I think it's coming from someone who really doesn't know what they're doing. That's the nature of the game I guess.

ybinds

Well I guess, those people must be dumping all those text files from the db using "Select into Outfile". In my case I requested them to delimit with "|" which is not mostly used character in normal fields. This way, I could solve my problem.

syragon

just wanted to let all the people who replied know that this thread helped me alot 🙂