mjhjmtu

My file: (1 sample line)

MMP,"01_janitorial,02_cleaning_tools",1,,CUBIC_INCH,"(14) tray capacity, 6" upright with 3" spacing, mounts on 48"W x 24"D, taupe epoxy, fits MetroMax i
& MetroMax Q shelf, NSF",CLEANING

I need to read this into a Postgresql table with 7 columns.

Breakdown of columns:

1. MMP


2. "01_janitorial,02_cleaning_tools"


3. 1


4. 
   


5. CUBIC_INCH


6. "(14) tray capacity, 6" upright with 3" spacing, mounts on 48"W x 1. 24"D, taupe epoxy, fits MetroMax i & MetroMax Q shelf, NSF"


7. CLEANING

The file is basically comma delimited, but I need to ignore the commas, carriage return (if present), and double quotes IF the text is inside a double quote. As in column 2 and 6.

I can use a postgresql copy command to load, or convert the file using awk, perl, sed or whatever to convert the file and then load.

Simply using -F, is often not enough to parse a CSV file. Especially if, as described, the delimiter can be part of a quoted string. You can work around some of this by using FPAT to use an expression to define a field rather than defining a character for your field delimiter, but awk will still go line-by-line, so you will have to preemptively consume the line breaks within your data.

Once done, you can do something such as awk 'BEGIN ("[^"]+")" /* normal processing here */ ' /path/to/file.

That expression will define as a field either "anything that is not a comma" or "A double-quote, one or more of anything that is not a double-quote, followed by a double-quote".

This will, however, explode if any of your quoted data themselves contain double-quotes.

As was said, the file was generated incorrectly. Nevertheless, you may try to workaroumd it using not only , delimiter but also ", and ,". Of course, custom script will be needed and no warranty you won't meet something like that in your 6th field.

Alternatively, you can strip first five fields assuming the 6th field is the only one messed, then from the result cut out the last field and comma. The remains will be 6th field content.

The solution is going to be very specific to your data file since the quotes aren't properly escaped off. Since there is only one trouble column, it's quite doable though. Here ya go:

#!/bin/bash
while IFS='' read -r line || [[ -n "$line" ]]; do
 echo "Line: $line"

# grabbing the first field is easy ..
 f1=$(echo $line | cut -d, -f1 )

# now remove the first field from the line
 line=$(echo $line | sed "s/$f1,//" )
 echo "Line is now: $line"

# to grab the second field use quote as a delimiter
 f2=$(echo $line | cut -d" -f2 )

# now remove the second field from the line
 line=$(echo $line | sed "s/"$f2",//" )
 echo "Line is now: $line"

# fields 3,4,5 are trivial .. just repeat the same pattern as 1 and then remove them
 f3=$(echo $line | cut -d, -f1 )
 line=$(echo $line | sed "s/$f3,//" )
 echo "Line is now: $line"
 f4=$(echo $line | cut -d, -f1 )
 line=$(echo $line | sed "s/$f4,//" )
 echo "Line is now: $line"
 f5=$(echo $line | cut -d, -f1 )
 line=$(echo $line | sed "s/$f5,//" )

# here is the "trick" ... reverse the string, then you can cut field 7 first!
 line=$(echo $line | rev)
 echo "Line is now: $line"
 f7=$(echo $line | cut -d, -f1 )

# now remove field 7 from the string, then reverse it back
 line=$(echo $line | sed "s/$f7,//" )
 f7=$(echo $f7 | rev)

# now we can reverse the remaining string, which is field 6 back to normal
 line=$(echo $line | rev)
# and then remove the leading quote
 line=$(echo $line | cut --complement -c 1)
# and then remove the trailing quote
 line=$(echo $line | sed "s/"$//" )
 echo "Line is now: $line"
# and then double up all the remaining quotes
 f6=$(echo $line | sed "s/"/""/g" )

 echo f1 = $f1
 echo f2 = $f2
 echo f3 = $f3
 echo f4 = $f4
 echo f5 = $f5
 echo f6 = $f6
 echo f7 = $f7
 echo $f1,"$f2",$f3,$f4,$f5,"$f6",$f7 >> fixed.txt
done < "$1"

I made it echo lots of output to show you how it works, you can remove all the echo statements to make it fast once you understand it. It appends the fixed line to fixed.txt.

Here is an example run and output:

[root@alpha ~]# ./fixit.sh test.txt
Line: MMP,"01_janitorial,02_cleaning_tools",1,,CUBIC_INCH,"(14) tray capacity, 6" upright with 3" spacing, mounts on 48"W x 24"D, taupe epoxy, fits MetroMax i & MetroMax Q shelf, NSF",CLEANING
Line is now: "01_janitorial,02_cleaning_tools",1,,CUBIC_INCH,"(14) tray capacity, 6" upright with 3" spacing, mounts on 48"W x 24"D, taupe epoxy, fits MetroMax i & MetroMax Q shelf, NSF",CLEANING
Line is now: 1,,CUBIC_INCH,"(14) tray capacity, 6" upright with 3" spacing, mounts on 48"W x 24"D, taupe epoxy, fits MetroMax i & MetroMax Q shelf, NSF",CLEANING
Line is now: ,CUBIC_INCH,"(14) tray capacity, 6" upright with 3" spacing, mounts on 48"W x 24"D, taupe epoxy, fits MetroMax i & MetroMax Q shelf, NSF",CLEANING
Line is now: CUBIC_INCH,"(14) tray capacity, 6" upright with 3" spacing, mounts on 48"W x 24"D, taupe epoxy, fits MetroMax i & MetroMax Q shelf, NSF",CLEANING
Line is now: GNINAELC,"FSN ,flehs Q xaMorteM & i xaMorteM stif ,yxope epuat ,D"42 x W"84 no stnuom ,gnicaps "3 htiw thgirpu "6 ,yticapac yart )41("
Line is now: (14) tray capacity, 6" upright with 3" spacing, mounts on 48"W x 24"D, taupe epoxy, fits MetroMax i & MetroMax Q shelf, NSF
f1 = MMP
f2 = 01_janitorial,02_cleaning_tools
f3 = 1
f4 =
f5 = CUBIC_INCH
f6 = (14) tray capacity, 6"" upright with 3"" spacing, mounts on 48""W x 24""D, taupe epoxy, fits MetroMax i & MetroMax Q shelf, NSF
f7 = CLEANING

If you need to escape the quotes in some other manner that should be pretty obvious given the above.

I get the final product by removing carriage returns within a quoted filed as following script :

$ cat remove_cr.awk
#!/usr/bin/awk -f
 record = record $0
 # If number of quotes is odd, continue reading record.
 if ( gsub( /"/, "&", record ) % 2 )
 record = record " "
 next
 

 print record
 record = ""