cat or cut to delimit csv?

Shell Programming and Scripting
1

Hi,

Im a pretty large noob to linux/perl etc and im trying to use mysql slurp to take a delimited file and import it into mysql using stdin (in the hope its faster)
mysqlslurp - slurp <STDIN> into a MySQL table - search.cpan.org
Christopher Brown / MySQL-Slurp - search.cpan.org

Using "cat": This seems to only take a tab delimited file. My import files have varying delimiters e.g. "|" with the quotes. Is there any way to get cat to work with other delimiters such as pipe.

I have tried using cut: This just puts everything in one column

cut -f1-9 -d="|" myfile.csv | mysqlslurp --database=my_db --table=my_table

I also tried using cat: This doesnt seem to work with mysqlimport options as mysqlslurp mentions that they can be passed through

cat myfile.csv | mysqlslurp --database=my_db --table=my_table --fields-terminated-by="|"

any help is much appreciated

Cheers :slight_smile:

2

Can you paste a sample input line and your expected o/p that will eventually feed back to the my sqlslurp?

cheers,
Devaraj Takhellambam

3

absolutely :slight_smile:

here is one line of delimited info:

"product name"|"http://example.com/productlink.htm"|"http://example.com/productimage.jpg"|"price $80"

The code I am sending to mysql slurp: (this converts pipes to tabs) I was hoping not to do any conversion and just to use cat but it has no delimiter feature. This also doesnt get rid of the quotes enclosing each field.

cat myfile.csv | tr '|' '\t' | mysqlslurp --database=mydb --table=mytable

I cant seem to work out how to use the --mysqlimport options. It mentions on the mysqlslurp site that its possible to use them. I was thinking if i can use the options i can use --fields-terminated-by and --fields-enclosed-by etc

from mysqlslurp: http://search.cpan.org/dist/MySQL-Slurp/script/mysqlslurp

  mysqlslurp 
      -D | --database                   Database    
      --table                           Table
      [--tmp | --tmpdir | --temp ]      Temporary Directory

      [ --usage ]                       Prints this synopsis
      [ --man ]                         Prints full man page

      [options passed to mysqlimport] 
      ( see mysqlimport --help for a list of options )


  Example: 
    cat file | mysqlslurp --database=my_db --table=my_table

I have a huge amount of these files (millions), so i was hoping to do it all with cat with as little text operation as possible from awk or perl etc

I guess I am looking for the fastest method to upload this to the db.

Cheers for any help :slight_smile:

4

no worries i worked it out - the mysqlimport options do work, i just got --fields-terminated-by=" wrong. I had to play around with this to get it to work