.txt to .csv

[carlos_ray86]

I have a bunch of .txt files that I want to be converted to .csv and
then I want to open them. I know how to do a file open but I'm not
sure on how to convert the .txt into a .csv.

 

[Guest]

Converting text to CSV is very simple. You add a comma to indicate you want
to place data in the next column. You can put two commas in a row to skip a
column. Add a new line character (CR) to start putting data in a new row.
Then rename the text file to .csv. Here is an example of one of my
conversions.


Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "longtext.txt"
WriteFileName = "longtext.csv"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)


Do While tsread.atendofstream = False

InputLine = tsread.ReadLine


If (InStr(InputLine, "TEXT") > 0) Then

If Len(OutputLine) > 0 Then
tswrite.WriteLine OutputLine
OutputLine = ""

End If
Else
If Len(OutputLine) > 0 Then
OutputLine = OutputLine + "," + InputLine
Else
OutputLine = InputLine
End If
End If

Loop

tswrite.Close
tsread.Close

Exit Sub
End Sub

 

[carlos_ray86]

Converting text to CSV is very simple. You add a comma to indicate you want
to place data in the next column. You can put two commas in a row to skip a
column. Add a new line character (CR) to start putting data in a new row.
Then rename the text file to .csv. Here is an example of one of my
conversions.

Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "longtext.txt"
WriteFileName = "longtext.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

If (InStr(InputLine, "TEXT") > 0) Then

If Len(OutputLine) > 0 Then
tswrite.WriteLine OutputLine
OutputLine = ""

End If
Else
If Len(OutputLine) > 0 Then
OutputLine = OutputLine + "," + InputLine
Else
OutputLine = InputLine
End If
End If

Loop

tswrite.Close
tsread.Close

Exit Sub
End Sub





- Show quoted text -

I tried to do it but it did not work. This might make things easier. I
right now basically manually change the .txt file to .csv by going
into the folder where the files are at. I currently have the following
code

UF = Application.GetOpenFilename(FileFilter:="CSV Files(*.csv),*.csv",
Title:="Satlog Measured")

Workbooks.OpenText Filename:=UF, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2))

However, I would like something in front of this that would change
my .txt files into the .csv file so that right after it can run that
code above. Thanks

 

[carlos_ray86]

I tried to do it but it did not work. This might make things easier. I
right now basically manually change the .txt file to .csv by going
into the folder where the files are at. I currently have the following
code

UF = Application.GetOpenFilename(FileFilter:="CSV Files(*.csv),*.csv",
Title:="Satlog Measured")

Workbooks.OpenText Filename:=UF, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 2))

However, I would like something in front of this that would change
my .txt files into the .csv file so that right after it can run that
code above. Thanks- Hide quoted text -

- Show quoted text -

-I figured a new way to do what I wanted thanks for the help.