[Solved] CSV to CSV MS-DOS | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

[Solved] CSV to CSV MS-DOS

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 03-14-2013
shadyuk shadyuk is offline
Registered User
 
Join Date: May 2012
Last Activity: 20 January 2014, 6:57 AM EST
Posts: 39
Thanks: 9
Thanked 1 Time in 1 Post
[Solved] CSV to CSV MS-DOS

Hello,

I have the following script which converts some data in a CSV file from one format to another. However, I have to convert the CSV (Macintosh) file to CSV or CSV (MS-DOS) first else the script wont work. I do this in Excel. I know it has something to do with the line endings, where CSV (Macintosh) saves with CR as the new-line character, and CSV (Comma delimited) and CSV (DOS) both use CR/LF.

Is there a way to add something to the top of my script that negates the need for me to convert manually?


Code:
#!/bin/ksh

if [ $# -ne 1 ] || [ ! -f "$1" ]
then    echo "Usage: $0 file" >&2
        exit 1
fi
awk '
  NR==1{
    $0="header1,header2,headerN"
  }
  NR>1{
    for(i=2; i<=NF; i++) if($i~/^[KMG0-9]+\/[KMG0-9]+$/) sub("/",",",$i)
    $0=$0
    for(i=2; i<=NF; i++) if($i~/^[KMG0-9]+$/){
      p=0
      if($i~/G/) p=9
      if($i~/M/) p=6
      if($i~/K/) p=3
      sub(/[GMK]/,".",$i)
      $i*=10^p
    }
  }
  1
' FS=, OFS=, "$1"

Sponsored Links
    #2  
Old 03-14-2013
vbe's Avatar
vbe vbe is offline Forum Staff  
Moderator
 
Join Date: Sep 2005
Last Activity: 17 April 2014, 3:54 AM EDT
Location: Switzerland - GE
Posts: 5,490
Thanks: 148
Thanked 362 Times in 339 Posts
Check if you dont have ux2dos or unix2dos utilities before rewriting something... (and to revert dos2ux /dos2unix...)
Sponsored Links
    #3  
Old 03-14-2013
shadyuk shadyuk is offline
Registered User
 
Join Date: May 2012
Last Activity: 20 January 2014, 6:57 AM EST
Posts: 39
Thanks: 9
Thanked 1 Time in 1 Post
Thanks. I tried unix2dos as follow:

unix2dos -437 -o Test.csv

This seemed to add the CR/LF new line character. However the script still fails. I still have to open in Excel and save as CSV or CSV(MS-DOS).

Sample input

Code:
232/344
21K7/5K48
5K32/4K85

Expected output

Code:
232,344
21700,5480
5320,4850

    #4  
Old 03-14-2013
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 17 April 2014, 6:29 PM EDT
Location: Saskatchewan
Posts: 18,531
Thanks: 681
Thanked 3,036 Times in 2,856 Posts
unix2dos does what it says, unix to dos. It doesn't turn / into ,.

Converting the / is easy though.
Code:
tr '/' ',' < inputfile > outputfile

Sponsored Links
    #5  
Old 03-14-2013
shadyuk shadyuk is offline
Registered User
 
Join Date: May 2012
Last Activity: 20 January 2014, 6:57 AM EST
Posts: 39
Thanks: 9
Thanked 1 Time in 1 Post
Thanks Corona. That seems to work except that it also splits the date. How to amend so that it ignores column 2?
Sponsored Links
    #6  
Old 03-14-2013
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 17 April 2014, 6:29 PM EDT
Location: Saskatchewan
Posts: 18,531
Thanks: 681
Thanked 3,036 Times in 2,856 Posts
The example you showed doesn't have any dates in it.

Show representative input and representative output and we can try. Otherwise we'll blindly do things which do what you say, not what you actually want.
Sponsored Links
    #7  
Old 03-14-2013
shadyuk shadyuk is offline
Registered User
 
Join Date: May 2012
Last Activity: 20 January 2014, 6:57 AM EST
Posts: 39
Thanks: 9
Thanked 1 Time in 1 Post
The data has many columns so the script above searches for any columns with format xxx/xxx and then converts according to the calculations. It also avoids column 2.

I hope the following is sufficient.

Sample input.

Code:
19347222,25/02/2013 06:46,1361803573,232/344
19347290,25/02/2013 06:46,1361803583,21K7/5K48
19347359,25/02/2013 06:46,1361803588,5K32/4K85

Expected output

Code:
19347222,25/02/2013 06:46,1361803573,232,344
19347290,25/02/2013 06:46,1361803583,21700,5480
19347359,25/02/2013 06:46,1361803588,5320,4850

It looks like that if I first replace "/" with "," with the tr utility, and then run the script, it works. However, its of course also splitting the date. So, I need to incorporate something into the script so that it replaces the delimiter first, avoids the date column and then runs the calculations on xxx/xxx.
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Need help. How to create csv file? thenuie Shell Programming and Scripting 3 07-03-2013 05:36 AM
Comparing 2 CSV files and sending the difference to a new csv file Naresh101 Shell Programming and Scripting 1 03-13-2013 04:14 PM
Perl search csv fileA where two strings exist on another csv fileB PerlNewbRP Shell Programming and Scripting 8 04-13-2012 07:05 AM
CSV to SQL insert: Awk for strings with multiple lines in csv khayal Shell Programming and Scripting 4 12-12-2011 01:00 PM
Need to compare two csv files values and write into another csv file chinnahyd Shell Programming and Scripting 2 02-06-2009 01:17 PM



All times are GMT -4. The time now is 10:29 AM.