PDA

View Full Version : Solved: How to convert to tab-delimited text files?



Dreamer
07-11-2006, 07:40 PM
Hi all,

I have around 50 csv files in a folder.

I don't know how to edit the following code if I need the tab-delimited format. Also, I have another problem, can it be possible if I want to skip the first line of every csv file when exporting to tab-delimited text files?

Thank a lot! :)


sub a ()
Oldname=Dir("*.csv")
Do while Oldname <>""
NewName = Left(Oldname,Len(Oldname)-4)&",txt"
Name Oldname As Newname
Oldname = Dir
Loop
End Sub

Jacob Hilderbrand
07-11-2006, 08:35 PM
What are you doing with each file in the loop? Can you paste the rest of the code?

If you are importing, then you will need to change the deliminator used from comma to tab. After importing the file (assuming that is what you are doing), you could just delete the first row.

Dreamer
07-11-2006, 10:17 PM
Hi DRJ,

Thanks for your reply. Actually I want to convert all 50 csv comma-delimited files to tab-delimited files with first line excluded.. that's all I need .. Surely I'm also seeking the way to save the time of deleting first line x 50 times. :doh:

Jacob Hilderbrand
07-11-2006, 10:44 PM
Try this. Just change Path to the folder you want to process.


Option Explicit

Sub ConvertFiles()

Dim FileName As String
Dim Path As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

Path = ThisWorkbook.Path
FileName = Dir(Path & "\*.csv")
Do Until FileName = ""
Workbooks.Open FileName:=Path & "\" & FileName
Range("A1").EntireRow.Delete
ActiveWorkbook.SaveAs FileName:=Path & "\" & _
Left(FileName, Len(FileName) - 3) & "txt", FileFormat:=xlText
ActiveWorkbook.Close
FileName = Dir()
Loop

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

mdmackillop
07-11-2006, 11:47 PM
Hi Jake,
On your second loop, I think that the code will open any file type, not limited to the next csv.
Regards
Malcolm

Edit: Please ignore my previous comment! Just tested it.

Dreamer
07-12-2006, 01:30 AM
Hi,

Thx all!

Now I encounter another problem... how can I keep the decimal places ? I want to fix the width, all data should be given to 4 decimal place(this applies to all files), with DRJ's code, 0.0000 becomes 0.

Thank you so much!

mdmackillop
07-12-2006, 05:40 AM
Amend the loop to include a formatting line

Do Until Filename = ""
Workbooks.Open Filename:=Path & "\" & Filename
Range("A1").EntireRow.Delete
Cells.NumberFormat = "0.0000"
ActiveWorkbook.SaveAs Filename:=Path & "\" & Left(Filename, Len(Filename) - 3) & "txt", _
FileFormat:=xlText
ActiveWorkbook.Close
Filename = Dir()
Loop

Dreamer
07-14-2006, 10:39 PM
THANK YOU !!