RE: Setting The Delimiter Character for a .Csv file with Excel

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Here is my own code for reading CSV. It can be modified to change the comma
to the character &H6. I suspect you may be reading binary data, if so the
code need significant modifications. binary data has to be read one
character at a time and the file has to opened in binary mode.

Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0



Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"

Newfolder = Application.GetOpenFilename("CSV (*.csv),*.csv")
If Not Newfolder = False Then
Folder = ""
Do While InStr(Newfolder, "\") > 0
Folder = Folder & Left(Newfolder, InStr(Newfolder, "\"))
Newfolder = Mid(Newfolder, InStr(Newfolder, "\") + 1)
Loop
'remove last character which is a \
Folder = Left(Folder, Len(Folder) - 1)
End If

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
RowCount = LastRow + 1
First = True
Do
If First = True Then
filename = Dir(Folder & "\*.csv")
First = False
Else
filename = Dir()
End If
If filename <> "" Then
'open files
Set fread = fsread.GetFile(Folder & "\" & filename)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

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

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

tsread.Close
End If
Loop
End Sub


"Gary" wrote:

I am trying to import a file delimited with a hex value of 06. When using
Excel's 2007 Text Import Wizard (step 2 of 3), a new delimiter character can
be entered. How do I enter a hex value into this field?

Thanks,

Gary
.

Relevant Pages

  • Re: Setting The Delimiter Character for a .Csv file with Excel
    ... Some funny hex characters work ok, ... But what you can do is run a macro that can use that hex character. ... If CommaPosition> 0 Then ... Cells(RowCount, ColumnCount) = Data ...
    (microsoft.public.excel.misc)
  • Re: Newbie to MS Project - Need Help with Views
    ... Do not use the comma character as it is a reserved special character. ... >> The Owner field is not an available field in a Portfolio Analyzer>> view. ... >> in the OLAP cube for inclusion in your custom Portfolio Analyzer view. ...
    (microsoft.public.project)
  • Re: using excel text functions (specific to strings)
    ... The comma tells Excel where one argument ends and the next one ... The second argument tells Excel what position number to use as the starting ... point for the data we want to extract. ... The comma is in position 6 (the 6th character) ...
    (microsoft.public.excel.misc)
  • Re: using excel text functions (specific to strings)
    ... The comma tells Excel where one argument ends and the next one ... The second argument tells Excel what position number to use as the starting ... point for the data we want to extract. ... The comma is in position 6 (the 6th character) ...
    (microsoft.public.excel.misc)
  • Re: using excel text functions (specific to strings)
    ... The comma tells Excel where one argument ends and the next one ... The second argument tells Excel what position number to use as the ... point for the data we want to extract. ... The comma is in position 6 (the 6th character) ...
    (microsoft.public.excel.misc)