Find the answer to your Linux question:
Results 1 to 3 of 3
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. ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Sep 2010
    Posts
    2

    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,

    Tustir

  2. #2
    Just Joined!
    Join Date
    Sep 2010
    Posts
    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
    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.

  3. #3
    Linux User
    Join Date
    Dec 2009
    Posts
    260
    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.

Posting Permissions

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