redirecting out put to comma seperated file

[N Gopal]

Hi All,

I am generating one cooma sepearted CSV file.

I have one txt file:

myfile.txt has 2 entries like:

"200","001234FBBJ 001001","0040","0009","1","1213207","2007-03-02","2007-03-13"

"200","001234LARA 001001","0080","0009","1","1213291","2007-04-12"
*****************************************
while read line
do

part1=`echo "$line" | cut -f1-4 -d','`
part2=`echo "$line" | cut -f5-8 -d','`
line=$line1$comma$line2
echo $line >> MYREPORT.CSV
done
But in CSV file value for 2nd field comes like
001234LARA 001001.
But actuall value contains 2 spaces means 001234LARA 001001.

Can some suggest how to resolve this?

Thanks

[Yogeeraj_1]

hi,

can you try:

SRV2:home/yogeeraj>cat test.sh
comma=","
while read line
do
line=$(echo "$line"|awk '{print $1}')$comma$(echo "$line"|awk '{print $2}')
echo $line >> MYREPORT.CSV
doneSRV2:home/yogeeraj> chmod +x test.sh
SRV2:home/yogeeraj>cat myfile.txt
hello1 111
hello2 222
SRV2:home/yogeeraj> ./test.sh
SRV2:home/yogeeraj>cat MYREPORT.CSV
hello1,111
hello2,222
SRV2:home/yogeeraj>cat test.sh

hope this helps!

kind regards
yogeeraj

[Yogeeraj_1]

hi,

can you try:

SRV2:home/yogeeraj>cat test.sh
comma=","
while read line
do
line=$(echo "$line"|awk '{print $1}')$comma$(echo "$line"|awk '{print $2}')
echo $line >> MYREPORT.CSV
doneSRV2:home/yogeeraj> chmod +x test.sh
SRV2:home/yogeeraj>cat myfile.txt
hello1 111
hello2 222
SRV2:home/yogeeraj> ./test.sh
SRV2:home/yogeeraj>cat MYREPORT.CSV
hello1,111
hello2,222
SRV2:home/yogeeraj>cat test.sh

hope this helps!

kind regards
yogeeraj

[N Gopal]

Hi Raj,

I am restating my question.
Mytest.txt:
"200","001234FBBJ 0012","0008","ddfdf df"

"200","001234LARA 0012","0009","errr "


while read line
do

part1=`echo "$line" | cut -f1-2 -d','`
part2=`echo "$line" | cut -f3-4 -d','`
line=$part1$comma$part2
echo $line >> MYREPORT.CSV
done
Then CSV file show results:
200,001234FBBJ 0012(space is suppresed),0008,ddfdfdf(space is suppressed)
etc. It shows that it supress the space between 001234FBBJ 0012.

Are my question is clear?

[Dennis Handly]

>But actual value contains 2 spaces means 001234LARA 001001.
>Can some suggest how to resolve this?

After changing your code, your real problem is due to missing quotes in your echo:
comma=","
while read line; do
line1=$(echo "$line" | cut -f1-4 -d',')
line2=$(echo "$line" | cut -f5-8 -d',')
line=$line1$comma$line2
echo "$line" >> MYREPORT.CSV
done < myfile.txt

[Yogeeraj_1]

hi again,

sorry for the confusion. We need to implement the OFS (output field separator) variable in the awk command.

can you try:
awk -F, '{OFS=",";print $1,$2,$3,$4}' myfile.txt


revert
kind regards
yogeeraj

[N Gopal]

Hi Dennis,

Suppose i have code like:

echo $line$comma$Vistec1$comma$Vistec2>>MYREPORT.CSV
in this case what to do?

[Dennis Handly]

>Suppose i have code like: in this case what to do?
echo $line$comma$Vistec1$comma$Vistec2 >> MYREPORT.CSV

Add the quotes:
echo "$line$comma$Vistec1$comma$Vistec2" ...

If you don't add the quotes, the shell will remove whitespace when it passes the args to echo. Actually there is no whitespace between args and it is echo that is adding one space back.

[Yogeeraj_1]

hi again,

much more easier, would be to run:

cat myfile.txt | sed -e 's/^"//g' -e 's/","/,/g' -e 's/"//g'

e.g.
SRV2:home/yogeeraj>cat myfile.txt | sed -e 's/^"//g' -e 's/","/,/g' -e 's/"//g'
200,001234FBBJ 0012,0008,ddfdf df
200,001234LARA 0012,0009,errr
SRV2:home/yogeeraj>


kind regards
yogeeraj

[N Gopal]

Hi Dannis,
When i am doing like that out put in my CSV file comes like

""(Additional field are coming),"200","0012345 SS","0050","0003"

But now i can see that it keeping spaces.

Can you please suggest how to correct this?

Hi Raj,

Thanks a lot but i want something like what Dennis is trying to suggest.

[Dennis Handly]

>When i am doing like that output in my CSV file comes like
""(Additional field are coming),"200","0012345 SS","0050","0003"

>But now I can see that it keeping spaces. Can you please suggest how to correct this?

I'm confused. What does your input file look like and what is your current script?

My changes keeps your embedded two spaces. Are you saying you don't want some of those?

[Yogeeraj_1]

hi,

Dennis is right! it sounds that the input file and output file generated are same.

Please post a more detailed description of what you want to achieve.

kind regards
yogeeraj

[N Gopal]

Hi Dennis,

Please find attachment. Zip contains:

File1:Notcorrect formate.CSV(After applying your changes)
File2:CorrectFormate.CSV

Script:i am using to generate this out put file


In script you can refer code from line 280 to 310.

PLease let me know in case of further clarifications.

Thanks

[Dennis Handly]

I'm still confused. I can't run the script and I don't know how the CorrectFormate.CSV was created.

I see the first line (header) as different.
I see "," at the end of the line.
I see "," at the beginning of the line.
I see extra "" in the middle of a line.
I see "U P" split on the Correct file. And in the bad file, there are two lines.

You need to ask questions with smaller pieces of data.

[N Gopal]

Hi Dennis,

I will explain you how to generate the csv file.

Say you have one txt file:
works_final.txt contains following values:
in this files 2nd field value has 2 spaces in between(004165A JSY1).

"201","004165A JSY1","0040","0003","1"
"202","004165D JSY2","0050","0004","2"
"203","004165C JSY3","0060","0005","3"
"204","004165C JSY4","0070","0006","4"

Now i want to put this data in xsl sheet(.csv file-comma seperated).
************ code started**********

Header="PREFIX,PROMO_REF,STATUS,WORK_TYPE"
echo $Header>correct_formate.csv

chmod 700 correct_formate.csv
while read line
do
refkey=`echo "$line" | cut -f1-2 -d','`
des=`echo "$line" | cut -f3-4 -d ','`
echo $refkey$comma$des>>correct_formate.csv
done*********************************
Now it will generate one correct_formate.csv file looks like:

PREFIX,PROMO_REF,STATUS,WORK_TYPE
"201","004165A JSY1","0040","0003","1"
"202","004165D JSY2","0050","0004","2"

and so on. If you open this file in windows it is like xsl file.
Now i am asking that in this file 2nd field value (e.g 004165A JSY1)has 2 sapces in between(in txt file it has two spaces) but when we echo it in csv file it brings only one space.

I hope you can replicate this. Please let me know if it is not clear.

Thanks

[OldSchool]

On Apr 4, 2008 12:10:34 GMT - N Gopal said:



Say you have one txt file:
works_final.txt contains following values:
in this files 2nd field value has 2 spaces in between(004165A JSY1).

"201","004165A JSY1","0040","0003","1"
"202","004165D JSY2","0050","0004","2"
"203","004165C JSY3","0060","0005","3"
"204","004165C JSY4","0070","0006","4"

Now i want to put this data in xsl sheet(.csv file-comma seperated).
************ code started**********



Now it will generate one correct_formate.csv file looks like:

PREFIX,PROMO_REF,STATUS,WORK_TYPE
"201","004165A JSY1","0040","0003","1"
"202","004165D JSY2","0050","0004","2"

and so on. If you open this file in windows it is like xsl file.
Now i am asking that in this file 2nd field value (e.g 004165A JSY1)has 2 sapces in between(in txt file it has two spaces


=======================================================================================================


So the ONLY difference between the two files is:
a) The header "PREFIX,PROMO_REF,STATUS,WORK_TYPE"
b) And the file extension is to be ".csv" instead of ".txt"????????????

If thats the case, you've really taken the wrong approach.

All you need do is:

echo "PREFIX,PROMO_REF,STATUS,WORK_TYPE" > MYREPORT.CSV
cat myfile.txt >> MYREPORT.CSV and be done with it

If you insist on doing the read loop, you need to enclose the echo'd variable that contains multiple spaces in double
quotes, like in the following (Note the " around the last 'echo' variables and the change in the "cut" for "des"):

comma=","

while read line
do
refkey=`echo "$line" | cut -f1-2 -d','`
des=`echo "$line" | cut -f3-5 -d ','`
echo "$refkey$comma$des"
done < works_final.txt

[Bill Hassell]

The compression of two (or more) spaces into one is absolutely normal. The cut command is doing the right job but are assigning a string with white space that must be retained. The way you do this is the always refer to $part1 and $part2 enclosed in quotes. Here is a simple demo:

$ A="a B"
$ echo "A is ${#$A} chars"
A is 6 chars

$ echo $A | xd -xc
0000000 6120 420a
a B \n
0000004

$ echo "$A" | xd -xc
0000000 6120 2020 2042 0a00
a B \n
0000007

The A variable has spaces in the middle but when the shell expands the A variable, all white space (including linefeed) is collapsed into 1 space. Enclose the variable in double quotes as in "$A" and the spaces will be retained.

[N Gopal]

Hi Old S, Bill

Appologies for late reply. I am insisting on while loop because we are serching some particular referances from txt file not copying all records.
As both of you suggesting to put quote before outputing to csv file, that i have tested and it is not giving me proper out put. I can not apply this approach.
Can you please suggest anything else if possible?

Thanks

[Dennis Handly]

>in this files 2nd field value has 2 spaces in between(004165A JSY1).
>echo $refkey$comma$des>>correct_formate.csv
>i am asking that in this file 2nd field value (e.g 004165A JSY1)has 2 spaces in between (in txt file it has two spaces) but when we echo it in csv file it brings only one space.

I'm confused. I told you how to fix that at Apr 4, 2008 06:33:06 GMT & Apr 4, 2008 06:56:56 GMT.
And OldSchool and Bill just repeated that.

[N Gopal]

Hi Dennis,

Thanks for your concerns. As you suggested me to put quote like:

echo "$line" >> MYREPORT.CSV and
echo "$line$comma$Vistec1$comma$Vistec2"

I have already tried this and it is not giving me desired results. I am requesting that is anyother way to deal with this.
Please correct me if i am wrong.

Thanks

[Dennis Handly]

>I have already tried this and it is not giving me desired results.

Ok, what's going wrong again?
If you do the following, which is wrong?
echo "$line"
echo "$Vistec1"
echo "$Vistec2"

[N Gopal]

Hi Dennis,

I am running fresh test to generate new file file, as per your changes. Let us see how it comes. I will update you.

Many thanks.

[OldSchool]

I'm not sure what the "problem" is. I used your test data, and got the desired "output". You *did* want to keep the spaces as they were in the original file, did you not?

Did you *try* just the sample I posted, or did you "integrate" it into a larger script? If the later, I'd suggest that the error is there.

[OldSchool]

I just looked over your script again, and you do a *lot* of concatenating fields together into new variables...some of which are used in echo statements to the csv file.

if they have multiple spaces in them, you need to wrap those in double qoutes as well when you concat them together. for instance:


desc=`grep "$larefkey" $FILES/desc_final.txt | cut -f4 -d'"'`
part1=`echo "$line" | cut -f1-4 -d','`
part2=`echo "$line" | cut -f5-6 -d','`
line1=$part1$comma$con_name$comma$twu_regn$comma$lob$comma$part2

If any part that makes up line1 has more than one space, then you really need:

line1="$part1$comma$con_name$comma$twu_regn$comma$lob$comma$part2"

[Dennis Handly]

>OldSchool: If any part that makes up line1 has more than one space, then you really need:
line1="$part1$comma$con_name$comma$twu_regn$comma$lob$comma$part2"

This is incorrect, though I thought so at first. It is only needed if there is white space on the RHS before variable expansion but is probably a good practice.
Note: This is a shell variable assignment and it does not follow the rules for command invocation as in echo(1).

#!/usr/bin/sh
part1="P 1" # quotes needed here, of course
part2="P 2"
comma=,
con_name="C N"
twu_regn="T R"
lob="L B"

line1=$part1$comma$con_name$comma$twu_regn$comma$lob$comma$part2
line2=$part1$comma$con_name$comma$twu_regn$comma$lob$comma$part2
echo "line1=$line1" # quotes needed here
echo "line2=$line2"