[Solved] CSV to CSV MS-DOS


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting [Solved] CSV to CSV MS-DOS
# 1  
Old 03-14-2013
[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"

# 2  
Old 03-14-2013
Check if you dont have ux2dos or unix2dos utilities before rewriting something... (and to revert dos2ux /dos2unix...)
# 3  
Old 03-14-2013
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
unix2dos does what it says, unix to dos. It doesn't turn / into ,.

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

# 5  
Old 03-14-2013
Thanks Corona. That seems to work except that it also splits the date. How to amend so that it ignores column 2?
# 6  
Old 03-14-2013
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.
# 7  
Old 03-14-2013
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

2. Shell Programming and Scripting

Compare 2 csv files in ksh and o/p the difference in a new csv file

(say) I have 2 csv files - file1.csv & file2.csv as mentioned below: file1.csv ID,version,cost 1000,1,30 2000,2,40 3000,3,50 4000,4,60 file2.csv ID,version,cost 1000,1,30 2000,2,45 3000,4,55 6000,5,70 The... (7 Replies)
Discussion started by: Naresh101
7 Replies

3. Shell Programming and Scripting

Comparing 2 CSV files and sending the difference to a new csv file

(say) I have 2 csv files - file1.csv & file2.csv as mentioned below: file1.csv ID,version,cost 1000,1,30 2000,2,40 3000,3,50 4000,4,60 file2.csv ID,version,cost 1000,1,30 2000,2,45 3000,4,55 6000,5,70 ... (1 Reply)
Discussion started by: Naresh101
1 Replies

4. UNIX for Dummies Questions & Answers

[Solved] Perl Question - split function with csv file

Hi all, I have a csv file that appears as follows: ,2013/03/26,2012/12/26,4,1,"2017/09/26,5.75%","2017/09/26,1,2018/09/26,1,2019/09/26,1,2020/09/26,1,2021/09/26,1",,,2012/12/26,now when i use the split function like this: my @f = split/,/; the split function will split the data that is... (2 Replies)
Discussion started by: WongSifu
2 Replies

5. Shell Programming and Scripting

Reading from a CSV and writing in same CSV file

Hi, I am tryng to read from a csv file and based on some grep command output I will modify one of the column in the same csv. Example:- Input CSV:- 20120829001415,noneAA,google.com 20120829001415,dfsafds,google.com 20120829001415,noneAA,google.com Intermediate Step:- If 2nd column of... (3 Replies)
Discussion started by: kmajumder
3 Replies

6. Shell Programming and Scripting

awk read column csv and search in other csv

hi, someone to know how can i read a specific column of csv file and search the value in other csv columns if exist the value in the second csv copy entire row with all field in a new csv file. i suppose that its possible using awk but i m not expertise thanks in advance (8 Replies)
Discussion started by: giankan
8 Replies

7. Shell Programming and Scripting

Perl search csv fileA where two strings exist on another csv fileB

Hi I have two csv files, with the following formats: FileA.log: Application, This occured blah Application, That occured blah Application, Also this AnotherLog, Bob did this AnotherLog, Dave did that FileB.log: Uk, London, Application, datetime, LaterDateTime, Today it had'nt... (8 Replies)
Discussion started by: PerlNewbRP
8 Replies

8. Shell Programming and Scripting

CSV to SQL insert: Awk for strings with multiple lines in csv

Hi Fellows, I have been struggling to fix an issue in csv records to compose sql statements and have been really losing sleep over it. Here is the problem: I have csv files in the following pipe-delimited format: Column1|Column2|Column3|Column4|NEWLINE Address Type|some descriptive... (4 Replies)
Discussion started by: khayal
4 Replies

9. Shell Programming and Scripting

2 problems: Mailing CSV file / parsing CSV for display

I have been trying to find a good solution for this seemingly simple task for 2 days, and I'm giving up and posting a thread. I hope someone can help me out! I'm on HPUX, using sqlplus, mailx, awk, have some other tools available, but can't install stuff that isn't already in place (without a... (6 Replies)
Discussion started by: soldstatic
6 Replies

10. Shell Programming and Scripting

Need to compare two csv files values and write into another csv file

Hi all, Am new to scripting. So i just need your ideas to help me out. Here goes my requirement. I have two csv files 1.csv 2.csv abc,1.24 abc,1 def,2.13 def,1 I need to compare the first column of 1.csv with 2.csv and if matches then need to compare... (2 Replies)
Discussion started by: chinnahyd
2 Replies
Login or Register to Ask a Question