Parse a txt file and save as csv?
 

Hello;

I am trying to come up with some vba that will parse a txt file. There are
two files that come with different size delimited strings. I would like to
try to find a generic script that will parse the string lengths according to
their size and then add a comma after the end of the string length. I would
then like to save it into a csv file with line breaks. Can anyone direct me
to an accurate solution.

The string lengths are different, but I don't mind having two separate
scripts to run on the separate files.

Any help or direction would be greatly appreciated.

Thanks

Frank Pytel

Parse a txt file and save as csv?
 

This code is extracting CSV data

Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")


RowCount = LastRow + 1
If FName < "" Then
'open files
Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine < ""
DelimiterPosition = InStr(InputLine, Delimiter)
If DelimiterPosition 0 Then
Data = Trim(Left(InputLine, DelimiterPosition - 1))
InputLine = Mid(InputLine, DelimiterPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
End Sub
--------------------------------------------------------------------------------------
Here is a macro that reads one file and write to another file
Sub adddouble()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const ReadFileName = "test.txt"
Const WriteFileName = "outtest.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


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

'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

OutputLine = Replace(InputLine, Chr(34), Chr(34) & Chr(34))

tswrite.writeline OutputLine
Loop


tswrite.Close
tsread.Close


End Sub

"Frank Pytel" wrote:

Hello;

I am trying to come up with some vba that will parse a txt file. There are
two files that come with different size delimited strings. I would like to
try to find a generic script that will parse the string lengths according to
their size and then add a comma after the end of the string length. I would
then like to save it into a csv file with line breaks. Can anyone direct me
to an accurate solution.

The string lengths are different, but I don't mind having two separate
scripts to run on the separate files.

Any help or direction would be greatly appreciated.

Thanks

Frank Pytel

Parse a txt file and save as csv?
 

Joel;

Thank you for your response. It was greatly appreciated. I don't think this
is quite what I am looking for.

The GetCSVData() appears to get from and put to a CSV file. I am starting
with txt files.

adddouble() appears to be copying the original file to another txt file
prefixed with the word out.

I should have been much more clear on what I am trying to do. Please forgive
me.

I have a txt file that I download once a week. This txt file is a tab
delimited file which has a tab delimeter every 37 or 40 characters dependent
on the file downloaded. I would like to try to find a script that I can use
to run after I open Excel. Example.

1. Open Excel
2. Set the number of characters within a cell. (37 or 40 or any other number
of characters I should choose in the future up to the 255 limit)
3. Run the macro.
a. The macro would open the test.txt file.
b. The macro would count 37 characters and place this data in Column A.
This would be the Name.
c. The macro would proceed to the next character set and place these 37
characters in Column B. This would be the Address.
d. The macro would continue to break out the data sets / character
sets until the specified number is reached. At this point it would proceed to
the next line and repeat.

I need to run some further processing on the information, but this can be
done with functions and formulas easily enough. I want to exclude some zip
codes from the list and delete some corrupted portions.

Speaking of corrupted data, the total number of characters is the key. If
there is a bad zip code, the macro would just continue on counting and
writing until the end of the file. Once the end of the txt file is located,
it will shut down.

Thanks for your help Joel, and to anyone else that would be interested in
commenting. I don't know jack about VBA so any help I can get would be
greatly appreciated.

Thank You Very Much. Have a Great Day.

Frank Pytel




"Joel" wrote:

This code is extracting CSV data

Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")


RowCount = LastRow + 1
If FName < "" Then
'open files
Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine < ""
DelimiterPosition = InStr(InputLine, Delimiter)
If DelimiterPosition 0 Then
Data = Trim(Left(InputLine, DelimiterPosition - 1))
InputLine = Mid(InputLine, DelimiterPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
End Sub
--------------------------------------------------------------------------------------
Here is a macro that reads one file and write to another file
Sub adddouble()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const ReadFileName = "test.txt"
Const WriteFileName = "outtest.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


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

'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

OutputLine = Replace(InputLine, Chr(34), Chr(34) & Chr(34))

tswrite.writeline OutputLine
Loop


tswrite.Close
tsread.Close


End Sub

"Frank Pytel" wrote:

Hello;

I am trying to come up with some vba that will parse a txt file. There are
two files that come with different size delimited strings. I would like to
try to find a generic script that will parse the string lengths according to
their size and then add a comma after the end of the string length. I would
then like to save it into a csv file with line breaks. Can anyone direct me
to an accurate solution.

The string lengths are different, but I don't mind having two separate
scripts to run on the separate files.

Any help or direction would be greatly appreciated.

Thanks

Frank Pytel

Parse a txt file and save as csv?
 

You were very generaly in your first response so I gave waht I thought would
get you started. Now that you said you have Tab Delimeted Data is becomes
much easier. All yu have to do is to go to the menu Data - Import External
data - Import Data. Select the file you want. Next select delimited and
Press Next. check the Tab obtion and then press finish.

To save the file as CSV all you have to do is do a File - SaveAs and choose
CSV as the option in the type box on the bottom of the window. A macro can
be written to do both steps automatically.

"Frank Pytel" wrote:

Joel;

Thank you for your response. It was greatly appreciated. I don't think this
is quite what I am looking for.

The GetCSVData() appears to get from and put to a CSV file. I am starting
with txt files.

adddouble() appears to be copying the original file to another txt file
prefixed with the word out.

I should have been much more clear on what I am trying to do. Please forgive
me.

I have a txt file that I download once a week. This txt file is a tab
delimited file which has a tab delimeter every 37 or 40 characters dependent
on the file downloaded. I would like to try to find a script that I can use
to run after I open Excel. Example.

1. Open Excel
2. Set the number of characters within a cell. (37 or 40 or any other number
of characters I should choose in the future up to the 255 limit)
3. Run the macro.
a. The macro would open the test.txt file.
b. The macro would count 37 characters and place this data in Column A.
This would be the Name.
c. The macro would proceed to the next character set and place these 37
characters in Column B. This would be the Address.
d. The macro would continue to break out the data sets / character
sets until the specified number is reached. At this point it would proceed to
the next line and repeat.

I need to run some further processing on the information, but this can be
done with functions and formulas easily enough. I want to exclude some zip
codes from the list and delete some corrupted portions.

Speaking of corrupted data, the total number of characters is the key. If
there is a bad zip code, the macro would just continue on counting and
writing until the end of the file. Once the end of the txt file is located,
it will shut down.

Thanks for your help Joel, and to anyone else that would be interested in
commenting. I don't know jack about VBA so any help I can get would be
greatly appreciated.

Thank You Very Much. Have a Great Day.

Frank Pytel




"Joel" wrote:

This code is extracting CSV data

Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")


RowCount = LastRow + 1
If FName < "" Then
'open files
Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine < ""
DelimiterPosition = InStr(InputLine, Delimiter)
If DelimiterPosition 0 Then
Data = Trim(Left(InputLine, DelimiterPosition - 1))
InputLine = Mid(InputLine, DelimiterPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
End Sub
--------------------------------------------------------------------------------------
Here is a macro that reads one file and write to another file
Sub adddouble()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const ReadFileName = "test.txt"
Const WriteFileName = "outtest.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


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

'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

OutputLine = Replace(InputLine, Chr(34), Chr(34) & Chr(34))

tswrite.writeline OutputLine
Loop


tswrite.Close
tsread.Close


End Sub

"Frank Pytel" wrote:

Hello;

I am trying to come up with some vba that will parse a txt file. There are
two files that come with different size delimited strings. I would like to
try to find a generic script that will parse the string lengths according to
their size and then add a comma after the end of the string length. I would
then like to save it into a csv file with line breaks. Can anyone direct me
to an accurate solution.

The string lengths are different, but I don't mind having two separate
scripts to run on the separate files.

Any help or direction would be greatly appreciated.

Thanks

Frank Pytel

Parse a txt file and save as csv?
 

Joel;

Thank you. I understand how to import. I am hoping to avoid this as it
causes problems for my function mapping.

I am not entirely sure that this is tab delimited. Like I stated earlier,
this is based on a character string of predefined characters that vary in
length. I was hoping for a script that would accomplish parse by the total
number of characters.

Do you think you might have any templates that work off of a character
string length?

Thank you for all of your help and consideration, Joel.

Frank Pytel


"Joel" wrote:

You were very generaly in your first response so I gave waht I thought would
get you started. Now that you said you have Tab Delimeted Data is becomes
much easier. All yu have to do is to go to the menu Data - Import External
data - Import Data. Select the file you want. Next select delimited and
Press Next. check the Tab obtion and then press finish.

To save the file as CSV all you have to do is do a File - SaveAs and choose
CSV as the option in the type box on the bottom of the window. A macro can
be written to do both steps automatically.

"Frank Pytel" wrote:

Joel;

Thank you for your response. It was greatly appreciated. I don't think this
is quite what I am looking for.

The GetCSVData() appears to get from and put to a CSV file. I am starting
with txt files.

adddouble() appears to be copying the original file to another txt file
prefixed with the word out.

I should have been much more clear on what I am trying to do. Please forgive
me.

I have a txt file that I download once a week. This txt file is a tab
delimited file which has a tab delimeter every 37 or 40 characters dependent
on the file downloaded. I would like to try to find a script that I can use
to run after I open Excel. Example.

1. Open Excel
2. Set the number of characters within a cell. (37 or 40 or any other number
of characters I should choose in the future up to the 255 limit)
3. Run the macro.
a. The macro would open the test.txt file.
b. The macro would count 37 characters and place this data in Column A.
This would be the Name.
c. The macro would proceed to the next character set and place these 37
characters in Column B. This would be the Address.
d. The macro would continue to break out the data sets / character
sets until the specified number is reached. At this point it would proceed to
the next line and repeat.

I need to run some further processing on the information, but this can be
done with functions and formulas easily enough. I want to exclude some zip
codes from the list and delete some corrupted portions.

Speaking of corrupted data, the total number of characters is the key. If
there is a bad zip code, the macro would just continue on counting and
writing until the end of the file. Once the end of the txt file is located,
it will shut down.

Thanks for your help Joel, and to anyone else that would be interested in
commenting. I don't know jack about VBA so any help I can get would be
greatly appreciated.

Thank You Very Much. Have a Great Day.

Frank Pytel




"Joel" wrote:

This code is extracting CSV data

Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")


RowCount = LastRow + 1
If FName < "" Then
'open files
Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine < ""
DelimiterPosition = InStr(InputLine, Delimiter)
If DelimiterPosition 0 Then
Data = Trim(Left(InputLine, DelimiterPosition - 1))
InputLine = Mid(InputLine, DelimiterPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
End Sub
--------------------------------------------------------------------------------------
Here is a macro that reads one file and write to another file
Sub adddouble()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const ReadFileName = "test.txt"
Const WriteFileName = "outtest.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


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

'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

OutputLine = Replace(InputLine, Chr(34), Chr(34) & Chr(34))

tswrite.writeline OutputLine
Loop


tswrite.Close
tsread.Close


End Sub

"Frank Pytel" wrote:

Hello;

I am trying to come up with some vba that will parse a txt file. There are
two files that come with different size delimited strings. I would like to
try to find a generic script that will parse the string lengths according to
their size and then add a comma after the end of the string length. I would
then like to save it into a csv file with line breaks. Can anyone direct me
to an accurate solution.

The string lengths are different, but I don't mind having two separate
scripts to run on the separate files.

Any help or direction would be greatly appreciated.

Thanks

Frank Pytel