how to import text ,, with colums sepcified in each row followed by th

[voipcanada]

Hi and thanks to every one for there help

here is the link to the file

http://starkom.com/bill20050820_000000

that i wish to import

the coloum is specified in the row of the raw text,
Sat Aug 20 10:59:31 2005, HOST=66.24.17.72,
DST-NUMBER-IN=0033512877596, etc etc

except the date. can some one advice how to import this text file into
mysql

thanks

 

[TonyGroves]

Assuming each row contains the same columns, in the same order, then you can simply import the file using LOAD DATA INFILE into a temporary table, then transfer the data to your live table, interpreting the date field and stripping the non-data bits from the other fields.

 

[voipcanada]

can u look at the files on the link specified and make an example code ,, thanks in advance

 

[TonyGroves]

I've already made a suggestion about how it can be done. You could try that first, and then let us know about any specific problems you can't resolve.

 

[voipcanada]

yes i under stard that ,, but what i need to know is how to do "" interpreting the date field and stripping the non-data bits from the other fields."" what is the querry to strip

 

[TonyGroves]

First, you need to check that each line in the file does actually contain the same fields, in the same order. If not, stop right here; you need to write a program to do the job.

(2) Create a temporary table, whose field names and sizes match the data in the file.

(3) If your target table does not yet exist, create it, using suitable field names, types, and sizes.

(4) Use a LOAD DATA INFILE command to load the data from the file into the temporary table.

(4) Now, in your temporary table, each date/time field value will be in a descriptive form which will need to be converted into a raw date/time format, and the other fields will all contain a prefix consisting of the field name and an equals sign, which will have to be stripped. This is done in the next step.

(5) Write an INSERT ... SELECT query, which will select fields from your temporary table, and insert them into your target table, in the process converting the field values into formats acceptable to the target table. For the date/time field, you would use string functions to convert the date and time into the form "yyyy-mm-dd hh:mm:ss". For the other fields, you would need to strip off the field-name prefixes (SUBSTRING_INDEX might be useful here), and do any other format conversions needed.

(6) Your target table should now be loaded with the data in the correct format.

All the SQL commands and functions mentioned here are described in the MySQL manual.