Setting The Delimiter Character for a .Csv file with Excel

[Gary]

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

 

[Joel]

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]

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

 

[Dave Peterson]

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.

 

[Curt]

Recorded a mocro in word set delimiter when recording. When run macro it
stops on set delimiter screen. Is it possible to insert somewhere in macro to
set this?
I've tried all I know and cannot seem to find it on net. following is
recorded macro.
Debug yellows out from selection on. The text file is from excel word
reconizes end of row ok. It is being imported into a table for mail merge.
Thanks


Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _
Connection:="", SQLStatement:= _
"SELECT Name_of_Entry, Contact_Person, Address, CityState, Zip_ FROM
C:\Parade\ZZZ.txt WHERE ((Name_of_Entry IS NOT NULL ) AND (Contact_Person IS
NOT NULL ) AND (Address IS NOT NULL ) AND (CityState IS NOT NULL ) AND (Zip_
IS NOT NULL ))" _
& "", PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument _
:="", WritePasswordTemplate:="", DataSource:="C:\Parade\ZZZ.txt",
From:= _
-1, To:=-1, IncludeFields:=True
ChangeFileOpenDirectory "C:\Parade\"
ActiveDocument.SaveAs FileName:="Ztable.doc",
FileFormat:=wdFormatDocument _
, LockComments:=False, Password:="", AddToRecentFiles:=True, _
WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False,
SaveAsAOCELetter:= _
False
CommandBars("Stop Recording").Visible = False