CSV formatting with prefixing, appending and padding field
Hi I have a very large csv file with some hundreds of thousands of rows of data.
The data is in the following format:
Up to four alpha numeric characters for the first word. This is either set as 2 characters followed by 2 spaces, or as a single 4character word. This is then followed by an 8 character alphanumeric word making a total of 12 characters in total. The number of characters and positioning is the same for every line. ie, 4 and 8.
I need a way to reformat the code by taking the first 4 characters and then padding them to the end of the field as an 18 character field. So there will be 6 spaces in between the 8 character word and the original first 4 characters. ie. 8, 6, and 4.
For example the data above will become:
This will then be prefixed with a static text of "CODE," and then appended with 18 commas to make it a proper csv file ready for database import.
See final result below(minus the double quotes):
I can do the last step of prefixing and appending by using the following code where input.csv will be the results of the first text swap and padding:
however I am stuggling to perform the first step of the text swapping and padding. I was thinking of the following logic to get this to work:
1. Append 6 blank spaces to end of field.
2. Copy first 4 characters and append to end of field.
3. Delete first 4 characters.
I can't seem to get step 2 though.
I would appreciate any hints on getting this whole thing working.
Hi Guys,
I'm having a bit of a problem with a script, i need to get the day, month and day of month into a string, so i'm using:
CURRENT_DATE=`date +"%a %b %e"`
It is getting the correct date out, however it is not keeping the padding on the day of month. The %e is supposed to pad the day... (5 Replies)
Hi,
This is driving me nuts, can't think of any easy way to do it.
I need to append a string ".00" only in the third field of a file, and only if it does NOT have a decimal point already
Here is what the file looks like-
1400030846,2,17,POL GENERAL
1400030900,3,14.95,FIC GENERAL
If... (7 Replies)
Hi,
I've internally searched through forums for about 2+ hours. Unfortunately, with no luck. Although I've found some cases close to mine below, but didn't help so much.
Actually, I'm in short with time. So I had to post my case. Hoping that you can help.
I have 2 files,
FILE1
... (0 Replies)
I am working to import a file and have almost all the information accounted for except this last item. First of all the file name is the category and this is what I need to append to the file.
The catch I believe is the fact the file name has spaces in it.
Filename CPU Products.csv
File... (3 Replies)
Hi
I have a date which I get as parameter. I want it to be added as first column in all the rows in a csv file.
I have tried the below code but no success.
date=$1
awk -F"," '{print $date","$0}' z3.csv > z4.csv
Could you tell the correct code for the above req.?
How to use code... (1 Reply)
Hi,
I have a requirement to append = in particular row in csv file. Data in csv is as follow:
row1,a,a,a
row2,b,b,b
row3,c,c,c
row4,d,d,d
csv should be modified at row3 and no. of columns are not fixed but rows are. output should be as:
row1,a,a,a
row2,b,b,b
row3,=c,=c,=c... (2 Replies)
Hello people,
I am having problem to sort, sed and zero padding of column in csv file.
7th column only.
Input of csv file:
1,2,3,4,5,6,4/1/2010 12:00 AM,8
1,2,3,4,5,6,3/11/2010 9:39 AM,8
1,2,3,4,5,6,5/12/2011 3:43 PM,8
1,2,3,4,5,6,12/20/2009 7:23 PM,8
Output:... (5 Replies)
I have this csv file that I would like to sort on the 20th and 21st field. They are high lighted below. My challenge is that when I sort on those fields they are not in order as I would have liked. It seems like I have to pad those fields to the longest value in that fields data.
... (6 Replies)
I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below:
column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
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)