Text file to MySql

[Anon]

I would like to read a text file into a mysql table. The format of the text file is
"U98401","1-604-501-0612","","WIRE ROPE INDUSTRIES LTD.","","", etc for each record.

I would like to have each value between the quotes placed into a variable which can then be "INSERT INTO" a Mqsql database. I have gotten to making a connection and have been able to send some test data to the database so that parts working. Now i just need what goes in between the "while" statement?

while(!feof($fpread))
{
$str = fgets($fpread, 255);
parse_str($str);
$query = "INSERT INTO
$dbTable VALUES
(
$var1, $var2 etc
)";

	MYSQL_QUERY($query);
	}

I know this will be a pretty simple task for most but I am still learning.

Thanks

[Anon]

I would pull the text file in as an array and then loop through each element and split in on commas and the create a query for each like so🙁This is a sample of this very thing that I did a few night ago)

$dbhost = "";
$dbuser = "";
$dbpass = "";
$dbname = "";
if (!($db = mysql_connect($dbhost, $dbuser, $dbpass))) echo "error connecting to ". $dbname ."on ". $dbhost;
mysql_select_db($dbname,$db);
$file = file("./doitup2.txt");
while (list($key, $val) = each ($file)) {

$val = ereg_replace('\"', "", $val);

$data = explode(",", $val);

$sql = sprintf("INSERT into tablename VALUES ('%s', '%s', '%s', '%s', '%s', '%s')", $data[0], $data[1], $data[2], $data[3], $data[4], $data[5])
$result = mysql_query($sql, $db);
}

[Anon]

I tend to use phpmyadmin for this sort of stuff which is available free from www.phpwizard.net

But here's something from the mysql manual which allows you to import data from a text file into the database:

LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\' ]]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]

rgds,
scott d~

[Anon]

That's how somebody who doesn't read manuals would do it. 🙂

MySQL has functions for importing delimited text into tables.

Read the mysql manual sections about LOAD DATA INFILE
and
MYSQLIMPORT

those are about a gazillion times faster than doing it in php.

[Anon]

Vincent,
While I have a manula at my feet as we speak, I simply gave hima solution that he can use if he doesn't have telnet access to his server. Some hosting companies offer php and mysql but don't give you telnet or ssh Now how could you use LOAD IN TEXTFILE if you have no access? Believe me I resort to the manula at least 10 times a day, so Easy there Tiger!

[Anon]

Um.... LOAD DATA INFILE is an SQL statement. So you don't need telnet or ssh for this.

Maybe you should open the manauls instead of using them as a foot-rest?

:-)

I'm not trying to stirr up trouble here, just joking with ya :-)

[Anon]

Maybe I jumped the gun on this one a bit, but Iwas trying to save face. Turns out I have been doing it the hard for some time now.

[Anon]

I don't blame you, after the recent "read the **** manual" thread. :-)

But in the end we are all here to learn, not to fight.

Your method works just fine, but there are faster/easier methods.

Still, it's good to see different ways of doing the same thing.

[Anon]

Thank you all for your advise. I did know of the loadfile function but I need to do this with a dynamically updated text file so I used Louie's example/advise and it worked great. I was glad to hear that the snide comments were in jest and apparently between a couple guys that know each other.

[Anon]

So now the question would be, how do you do it if it is not comma delimited, parse int?

say for eaxmple you have data like this

S10111045 Some Text File that makes the line just 79 Chars long,
S10111045Csome more text that the C indicates a continuation of above line.
B10121099 And then another record, letting you have a huge file.

Where you would put the first Char into a field, then the next 2 into another and the next two after that into another and the next four into another and then look at the space to see if there is a C, if not put the rest of the line in another field. But then if the next line contained a C add everything after the C to the previous last field with only a single space and throw out the rest of the parse read.

[[deleted]]

I think that after a year and two months he will have solved the problem, don't you? :-)

[Anon]

Yes, I was almost sure he had solved it and that's why I asked. I use the archives here to learn as much as I can from others. Sometimes, the obvious slips by the experienced programmer in their explaination. (They figure any idiot would know it and they keep making better idiots)

I could have started a new thread I guess, but it's the same question with a parsing twist. Kind of a "What do you do if you don't have a delimited text file. Some of us are still converting text flat files from 1984. <grin> "Old programmers seldom die, they just decompile."

[wwwwizard]

OK, guys, that was interesting. Now let's see how well you know mysqlimport...

I have a table with cols A, B, and C.
I have a textfile with cols
Z,Y,A,C,B,D

I am hoping to be able to use the --columns='' feature on mysqlimport, but can't figure out how to get it to ignore certain columns.

In other words, I can do --columns='A,C,B' and it would know to put them in the right spots, but I can't seem to do it all, which would mean ignoring the other columns.

Here's what I am trying, which doensn't work:
--columns=',,A,C,B,' but it throws an error. What should I put in place of blanks to have mysqlimport skip those columns?