in excel 07 is there an ez way to export to a tdf ( tilde delimited file ) ?

I am going to be taking data all the time and feed a bash script.  

I need to parse out my fields fast & easy.

I can "save as " csv but there are commas in my data.

I can parse on pretty much anyting unique.  

TIMFOX123Asked:
Who is Participating?
 
Ardhendu SarangiConnect With a Mentor Sr. Project ManagerCommented:
Here's an easy way to do this -

OPTION 1 - Assuming data has no Comma's in it.
1. Save the file as csv.
2. Open the file in a notepad
3. Replace all Comma by Tilde.

Voila you are done!

OPTION 2 - Assumng data has Comma's in it.
1. Go to Control Panel -> Region & Language Settings.
2. Click on the first tab - Formats.
3. Click on the "Additional Settings" button at the bottom of the page
4. Change the list Separator to Tilde.
5. Click apply and then click OK to save the change.
6. Go back to excel and save the file as CSV.
7. It will be saved a  tilde-delimited file.
8. Remember to go back to your control panel and change the "List Separator" back to comma's or else you would be in trouble. :)

HTH,
Ardhendu
0
 
harr22Commented:
I don't think you can.  I'd do a find/replace, and replace tilde with comma.  Then save as CSV
0
 
SiddharthRoutCommented:
Try this code. This will export Sheet1 in the format that you want.

Sub Sample()
    Const Delim = "~"
    Const OutputFile = "C:\Sample.txt"

    Dim LastRow As Long, LastCol As Long
    Dim i As Long, j As Long
    Dim FileNum As Long

    FileNum = FreeFile
    
    With Sheets("Sheet1").Cells
        LastCol = .Find("*", [A1], , , xlByColumns, xlPrevious).Column
        LastRow = .Find("*", [A1], , , xlByRows, xlPrevious).Row
    End With
    
    Open OutputFile For Output As #FileNum
    
    For i = 1 To LastRow
        For j = 1 To LastCol
            Print #FileNum, Sheets("Sheet1").Cells(i, j).Value & Delim;
        Next j
        Print #FileNum,
    Next i
    
    Close #FileNum
End Sub

Open in new window


Sid
0
 
harr22Commented:
nevermind...i re-read your post...and my solution is useless
0
 
TIMFOX123Author Commented:
this does it

thx !!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.