Find the answer to your Linux question:
Results 1 to 3 of 3
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1

    Question 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,


  2. #2
    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

    rem -start of naormal batch statements
    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.

  3. #3
    I am just a beginner myself but I've checked some peaces ...

    echo "1234567890" | sed -e 's/./&-/5'
    Seems to work ... your first script should work fine ...
    A problem could be, the sed isn't changing the " " into a , but adding an ,.

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts