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

I don't think you can enter the hex 6. Some funny hex characters work ok, but I
couldn't get alt-0006 or ctrl-f to work.

But what you can do is run a macro that can use that hex character.

Select your range to parse, then turn on the macro recorder and do the text to
columns procedure.

But specify Other and a vertical bar (or any character that isn't used in your
data).

Then turn off the macro recorder.

If anything got parsed, hit edit|undo to put things back the way they belong.

Now hit alt-f11 to get to the VBE.

You'll see the recorded code with a portion that looks like:

OtherChar:="|"
change it to
OtherChar:=Chr(6)

Then back to excel and select your range and use alt-f8 to select your macro and
run it.

Gary wrote:

I'm really only trying to find out how to enter a hex value into the
delimiter prompt on the import file wizard. There is a check box for Tab so
I'm assuming there must be a way to enter a different hex value.

Thanks for your reply,
Gary
"Joel" wrote:

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

--

Dave Peterson
.

Relevant Pages