Text file to CSV with field data separated by blank lines
Hello,
I have some data in a text file where fields are separated by blank lines. There are only 6 fields however some fields have several lines of data as I will explain. Also data in a particular field is not consistently the same size but does end on a blank line. The first field start with the number 12 so I can use that as a start/stop point I guess but it has been a while and I forgot much of my shell scripting. Here is an example and what I need to do:
Code:
12 ABC5
01/01/2012 NameOfCity
Street Address
Person 1
Person 1 continued
Person 1 extra data
Person 2
12 ABC6
01/01/2012 NameOfCity
Street Address
Street Address continued
Street Address extra
Person 1
Person 2
Person 2 extra data
12 ABC7
01/01/2012 NameOfCity
.........and so on
What I want it to look like delimited for CSV use is:
Code:
12 ABC5;01/01/2012 NameOfCity;Street Address;Person 1,Person1 continued, Person1 extra data;Person 2
12 ABC6;01/01/2010 NameOfCity;StreetAddress;Person 1;Person 2, Person2 extra data
12 ABC7;....and so on........to the end of file.
In a perfect world I would like to delimit the date from city name like so:
Code:
12 ABC5;01/01/2012;NameOfCity;Street Address;Person 1,Person1 continued, Person1 extra data;Person 2
12 ABC6;01/01/2010;NameOfCity;StreetAddress;Person 1;Person 2, Person2 extra data
12 ABC7;....and so on........to the end of file.
So you see the problem is that Address, Person 1 and Person 2 can just be one line or may have many lines (up until a blank line) but that is not the same for every field every time. Just need to get this stuff all on single lines so I can import into excel or MYSQL etc.
Preferably I would like to do this with sed and loop in common bash script as my current script that got me this far is in bash. I'll take anything at this point though even if I have to call another program.
Thanks in advance for any replies!
Art
Last edited by Franklin52; 03-18-2012 at 10:52 AM..
Reason: Please use code tags for data and code samples, thank you
Could any one help me in basic shell script to export text file data to csv.
I need to export only particular data from text file to csv column.
I am a newbie to UNIX could anyone help me with sample script code (3 Replies)
I have a csv file which has three columns
mem no. name surname
1234 John Smith
12345 John Doe
I want to change the mem no. to add TF to the mem no. field i.e.
mem no. name surname
1234TF John Smith
12345TF John Doe
How do you do this for all records in the file? (3 Replies)
Hi all,
Sorry for the title, I was unsure how to word my issue. I'll get right to the issue. In my text file, I need to find all lines with the same data in the first field. Then I need to create a file with the matching lines merged into one. So my original file will look something like... (4 Replies)
Hi,
I am using BASH. How can I remove any lines in a text file that are either blank or begin with a # (ie. comments)? Thanks in advance.
Mike (3 Replies)
Hi.
I have a tab separated file that has a couple nearly identical lines. When doing:
sort file | uniq > file.new
It passes through the nearly identical lines because, well, they still are unique.
a)
I want to look only at field x for uniqueness and if the content in field x is the... (1 Reply)
I have a csv file that I need to extract some data from depending on another field after reading info from another text file.
The text file would say have 592560 in it.
The csv file may have some data like so
Field 1 Field2 Field3 Field4 Field5 Field6
20009756 1 ... (9 Replies)
Hi.. I'm facing a trouble in replacing two blank lines in a file using shell script...
I used sed to search a line and insert two blank lines after the searchd line using the following sed command.
sed "/data/{G;G;}/" filename . In the file, after data tag, two lines got inserted blank lines..... (4 Replies)
Gents
Using the script attached (raw2csv). i use to create the file .csv.. The input file is called 201.raw.
Kindly can you check if there is easy way to do it. The script works fine but takes a lot time to process
Thanks for your help (8 Replies)
Hi,
I tried the following options but was unable to delete blank lines from file
Input file = temp.hash.txt
temp.hash.txt content
90
0
89.56
0
0
57575.4544
56.89 (9 Replies)
Discussion started by: uuuunnnn
9 Replies
LEARN ABOUT REDHAT
join
JOIN(1) FSF JOIN(1)NAME
join - join lines of two files on a common field
SYNOPSIS
join [OPTION]... FILE1 FILE2
DESCRIPTION
For each pair of input lines with identical join fields, write a line to standard output. The default join field is the first, delimited
by whitespace. When FILE1 or FILE2 (not both) is -, read standard input.
-a SIDE
print unpairable lines coming from file SIDE
-e EMPTY
replace missing input fields with EMPTY
-i, --ignore-case ignore differences in case when comparing fields
-j FIELD
(obsolescent) equivalent to `-1 FIELD -2 FIELD'
-j1 FIELD
(obsolescent) equivalent to `-1 FIELD'
-j2 FIELD
(obsolescent) equivalent to `-2 FIELD'
-o FORMAT
obey FORMAT while constructing output line
-t CHAR
use CHAR as input and output field separator
-v SIDE
like -a SIDE, but suppress joined output lines
-1 FIELD
join on this FIELD of file 1
-2 FIELD
join on this FIELD of file 2
--help display this help and exit
--version
output version information and exit
Unless -t CHAR is given, leading blanks separate fields and are ignored, else fields are separated by CHAR. Any FIELD is a field number
counted from 1. FORMAT is one or more comma or blank separated specifications, each being `SIDE.FIELD' or `0'. Default FORMAT outputs the
join field, the remaining fields from FILE1, the remaining fields from FILE2, all separated by CHAR.
AUTHOR
Written by Mike Haertel.
REPORTING BUGS
Report bugs to <bug-coreutils@gnu.org>.
COPYRIGHT
Copyright (C) 2002 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICU-
LAR PURPOSE.
SEE ALSO
The full documentation for join is maintained as a Texinfo manual. If the info and join programs are properly installed at your site, the
command
info join
should give you access to the complete manual.
join (coreutils) 4.5.3 February 2003 JOIN(1)