MySql Load Utility Questions

[Rick]

When using the load utility on a CSV file, it seems I always need to
edit the input file and add an extra comma after the last field in
order for MySql to parse the line properly.

I use a command like:

LOAD DATA INFILE 'absolute path to file.csv'
IGNORE
INTO TABLE table-name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED
BY '\n' ;

for an input file created on a Linux system and I always have to add
the extra comma to the end of each record in order to get the records
loaded correctly.

Does anyone have some experience with this and can provide some
suggestion?

Also, is it possible to use the mative MySql load utility to load a
fixed-format input file? From everything I am reading it seems you
have to use some other language like perl or python or php, etc in
order to load a fixed-format file.

Any help is welcome.

Thanks

[Jeff North]

On 18 Aug 2004 18:21:03 -0700, in mailing.database.mysql
ri***@csmarketing.net (Rick) wrote:

| When using the load utility on a CSV file, it seems I always need to
| edit the input file and add an extra comma after the last field in
| order for MySql to parse the line properly.
|
| I use a command like:
|
| LOAD DATA INFILE 'absolute path to file.csv'
| IGNORE
| INTO TABLE table-name
| FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED
| BY '\n' ;
|
| for an input file created on a Linux system and I always have to add
| the extra comma to the end of each record in order to get the records
| loaded correctly.
|
| Does anyone have some experience with this and can provide some
| suggestion?
This sounds like an extra field has been added to the table but not
included within the csv file.

When I need to import data I
send the csv data to a temp table
use as many queries as needed to reformat/check the data
move the data into the appropriate table(s)
delete the temp table data
| Also, is it possible to use the mative MySql load utility to load a
| fixed-format input file? From everything I am reading it seems you
| have to use some other language like perl or python or php, etc in
| order to load a fixed-format file.
|
| Any help is welcome.
|
| Thanks

---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Rick]

Thanks for the feedback. From what I understand, MySql will ignore any
extra data in the csv file, but if there is not enough data in the csv
file to match the table, you need to include the column names on the
load utility.

Have you ever been able to get MySql native load utility to load a
fixed-format flat file?

Thanks
-Rick