-
SED Fixed Width to CSV
Hello I am rather new to SED but am trying to convert a fixed width txt file into a CSV so I can insert it into my database every night.
The fixed file comes over in a form such as this
12345Ftb and so on. I have the widths marked out so the first portion is ID which is 1-20, eligibility: 22-23, and so on.
I created this but am getting a blank csv file when I run it:
sed -e 's/./&,/20' -e 's/./&,/24' -e 's/./&,/37' -e 's/./&,/44' -e 's/./&,/59' -e 's/./&,/65' -e 's/./&,/69' -e 's/./&,/73'
-e 's/./&,/87' -e 's/./&,/101' -e 's/./&,/119' -e 's/./&,/139' frexport.txt > frexport.csv
rem ----- Convert Unix to dos (lf)
sed.exe -i -e "s/$/|r/" frexport.txt
Any help would be much appreciated,
Tustir
-
Here is a bit more information. I am moving a text file from one server to another. Unfortunately one server only outputs fixed width. I am then trying to create a CSV file and upload it into MS SQL SERVER 2008. An example of the file that I get is below:
005555 N 0 2000 11 27 25600 0.60 08/16/2010
The length definitions are as follows:
id: 1-20
eligibility: 22-23
Eligibility Type: 26-35
Programs: 38-41
Expire: 44-55
Elig Date (year): 57-60
Elig Date (month): 62-63
Elig Date (Day): 65-66
Universal Pin: 70-79
Approval Date: 83-92
Balance: 96-109
Balance date: 117-128
Application Process date: 138-147
This is what I am trying now, but still am doing it incorrectly
rem sed
path=%path;c:\sed\bin
c:
cd\Holdarea
rem
-------------------------------------------------------------------------------------------------------------------------------
rem -start of naormal batch statements
-
rem
-------------------------------------------------------------------------------------------------------------------------------
rem ------ Create copy/backup file before any SED changes applied
copy frexport.txt frexport-B4Sed.txt
sed.exe -e 's/\(.\{20\}\)\(.\{2\}\)\(.\10\)\(.\{4\}\)\(.\{12\}\) \(.\{4\}\)\(.\{2\}\)\(.\{2\}\)\(.\{10\}\)\(.\{10\} \)\(.\{14\}\)\(.\{12\}\)\
(.\{10\}\).*/\1,\2,\3,\4,\5,\6,\7,\8,\9,\10,\11,\12,\13/' frexport.txt
rem ----- Convert Unix to dos (lf)
sed.exe -i -e "s/$/|r/" frexport.txt
Once again thank you.
-
Hi,
I am just a beginner myself but I've checked some peaces ...
First:
Code:
echo "1234567890" | sed -e 's/./&-/5'
12345-67890
Seems to work ... your first script should work fine ...
A problem could be, the sed isn't changing the " " into a , but adding an ,.
Secound:
Code:
echo "005555 N 0 2000 11 27 25600 0.60 08/16/2010" | sed -e 's/./&,/20' -e 's/./&,/24' -e 's/./&,/37' -e 's/./&,/44' -e 's/./&,/59' -e 's/./&,/65' -e 's/./&,/69' -e 's/./&,/73'
005555 N 0 2000 11 2,7 2,5600 0.60 08,/16/20,10
Here is something interesting ...
As I understood you "005555 N 0 2000 11 27 25600 0.60 08/16/2010" is one example line you get from the server.
It looks like the positions you are setting the "," are wrong.
Besides, I am not sure if MS is using , or ; for cvs.
So if you adding a csv into a MS SQL Server you might wanna try setting ; as separation.
Hope that helps you.
