Insert Data Feed through phpmyadmin

data feed phpmyadmin

[matthewamzn]

I have a datafeed that is in a text file. I have created the table with all of the correct fields in my database. I'm trying to upload my datafeed.txt file into the table.

The problem is everything is put into the first field. It doesn't seem to be able to put the correct data in the correct column.

In the text file the fields are seperated by spaces. Should I change this?

Thanks

[dhardisty]

are you using mysql? You can use mysqlimport (mysql program, not php) or mysql's "load data infile" command. Both can be configured to use spaces to delimit fields.

FYI, usually commas or tabs are used to delimit fields.

You should set up your text file so that each new line indicates a new row in the database.

If you want to use php to load the data instead of mysql's import features, you could use something like:
<?php
//open the data file
$handle = fopen("test.csv", "r");
//read each row into an array called $data
while (($data = fgetcsv($handle, 1000, ",", " "))!== FALSE) {
//build the query
$sql = "insert into test_table values(";
//each element of the array is added to the query
foreach ($data as $value) {
$sql .= "\"$value\",";
}
//cut off the last comma
$sql = substr($sql, 0, -1);
$sql .= ")";
//some error handling in case things go wrong
mysql_query($sql, $db) or die("Error in query $sql:" . mysql_error());
}
fclose($handle);
?>

best,
Dave

[jatar_k]

is it possibly the file format?

you could also do it using the command line using LOAD DATA [dev.mysql.com], which is what phpmyadmin does but it may be easier to do it manually.

It could also just be the options you are using.

[matthewamzn]

It is mysql and I'm tying to upload the data using phpmyadmin. The datafeed is a .txt file and the fields are seperated by tabs. But when I try to insert the data, the rows are seperated but everything is bunched into the first column (id). I can't figure out why it wouldn't see the other fields.

[edited by: matthewamzn at 4:19 pm (utc) on Aug. 7, 2005]

[jatar_k]

you'll need someone to help who is familiar with the phpmyadmin settings, I never use it

ther has to be some options on the import that you haven't set correctly, 'FIELDS TERMINATED BY' needs to be set to a tab

something like

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

this is the default behaviour of LOAD DATA INFILE

which means (from the link I provided above)
the defaults cause LOAD DATA INFILE to act as follows when reading input:

Look for line boundaries at newlines.

Do not skip over any line prefix.

Break lines into fields at tabs.

Do not expect fields to be enclosed within any quoting characters.

Interpret occurrences of tab, newline, or '\' preceded by '\' as literal characters that are part of field values.

[dhardisty]

check your phpmyadmin settings. When you click on the "Insert data from a textfile into table" link, check the "Fields terminated by" field. The default delimiter is ;, which isn't what you are using. \t means tab, so try that.

Also, look at the "Fields enclosed by" field. You should probably check the "OPTIONALLY" box.

Also, the example row you pasted above doesn't have any tabs or spaces -- I'm assuming you didn't make the same mistake in your txt file.

[edited by: dhardisty at 4:25 pm (utc) on Aug. 7, 2005]

[jatar_k]

the software here will have ripped out the tabs

[matthewamzn]

Got it to work. For anyone else tying to insert txt data through phpmyadmin, I had to use these settings:

Replace table data with file (should by checked)
Fields terminated by \t
Fields enclosed by (optionally should be checked)
Fields escaped by \
Lines terminated by \r\n

[frumau]

Is it possible to insert a Word rich text format file into a table by using phpmyadmin as well?
I exported a table as a Word 2000 document which I want to edit and upload to the database again. I guess I cannot uplaod it being a Word document or html document..so therefore the above question.

Thanx in advance for any insight.

Jasper

[dhardisty]

if you want to edit it with word, you should save it as a "text only" .txt file.

In general, when editing files that will be processed by php and mysql, it's better to use a simple text editor such as Notepad or Wordpad.

[frumau]

Thanks for the response. I stored it as an Excell csv file. I was able to uplaod it but now all my thai characters got deformed and characters like é û etc for the translitteration are deformed as well.

I have the original Excell csv file on my hard disk, but how do I get it to replace the currenct messed up rows in my dictionary file? I had 350 words so far!

Jasper

[dhardisty]

sorry, I don't know much about non-english character encoding.

One idea: be sure your collation values for your mysql text columns are set to UTF8 or something rather than latin1.

good luck