-
Jul 4th, 2018, 07:25 AM
#1
Thread Starter
New Member
[RESOLVED] Exporting NULL character in a txt file
Hello,
I apologize in advance for any English mistake, it is not my first langage .
So I'm working on a small VBA project (using Excel) and I'm having a problem exporting the NULL character in a txt file, as the title says.
Basically, one of the goal of my project, among others, is to export a row of my worksheet as a txt file, which is supposed to be later exported in an IBM mainframe.
But some of the characters of this file must be valued by the LOW-VALUE hexadecimal character : x'00'
I've tried a few things, but none achieved what I want : the NULL character is squeezed out of the txt file.
Here are some of the tries I've made :
Code:
Dim var As String
var = Chr(0)
Dim var As String
var = ChrW(&H2400)
Dim var As String
var = vbNullString
Dim var As String
var = vbNullChar
I hope I was clear enough and would really appreciate any help.
Thanks!
-
Jul 4th, 2018, 07:57 AM
#2
Re: Exporting NULL character in a txt file
The vbNullChar constant is the one you want. Whether those get written to disk depends on how you are writing the data, but I'm not sure what you might use that would filter them out. Use a file viewer that can display the data in hex to confirm that your NUL characters are in the file.
How are you copying the resulting file to the mainframe? That might be where you are losing NUL characters.
FTP text ("ASCII") transfer mode "cooks" the data and may drop NUL characters depending on the server software and any options set there.
Of course FTP binary ("IMAGE") transfer mode can be more problematic. Even if you create EBCDIC encoded data on the PC, which is easy enough, mainframe software often doesn't expect variable-length record files with line termination characters. Instead it often wants fixed length records with no line terminators.
-
Jul 4th, 2018, 09:34 AM
#3
Thread Starter
New Member
Re: Exporting NULL character in a txt file
Hello Dilettante and thank you for responding.
To answer your question : the NULL character is definitely not is the txt file, I checked with Notepad++. Other character works just fine like Chr(1) or Chr(2)
So the NULL character is not lost when transferring to the mainframe.
Also, it looks like the vbNullChar prevents from writing after it.
For instance :
Code:
Dim a, b, c as String
a = "1"
b= vbNullChar
c = "3"
cells(1,1) = a & b & c
would only write "1" in the cell.
-
Jul 4th, 2018, 09:48 AM
#4
Re: Exporting NULL character in a txt file
Would changing the format of the cell to something other than "text" allow the null character?
Thinking this thread would better be placed in the Office portion of the forums.
-
Jul 4th, 2018, 09:56 AM
#5
Re: Exporting NULL character in a txt file
Ahh, it sounds like you are trying to use the "export" feature of Excel.
You are pretty much doomed if you do that. Excel plays lots of games. It is better to post Office product questions in the Office forum, where its quirks are taken for granted.
But even in VBA opening and writing a text file using native I/O statements should not have this problem.
-
Jul 4th, 2018, 10:09 AM
#6
Thread Starter
New Member
Re: Exporting NULL character in a txt file
I didn't see the Office forum while choosing where to post this question, my bad!
I just posted it there.
Thank you for your time anyway .
-
Jul 4th, 2018, 10:43 AM
#7
Re: Exporting NULL character to a txt file from Excel
Give an example of a string, and how you'd want it to appear in the text file.
-
Jul 4th, 2018, 11:09 AM
#8
Thread Starter
New Member
Re: Exporting NULL character to a txt file from Excel
Hello Vbfbryce
Here's an exemple :
Code:
dim var1, var2, var3, output as string
var1 = "xxxx"
var2 = vbNullString
var3 = "xxxx"
output = var1 & var2 & var3
When exported as a txt, the output variable should look like 'xxxxNULxxxx' when looked on an editor as Notepad++ that shows special characters.
-
Jul 4th, 2018, 04:04 PM
#9
Re: Exporting NULL character in a txt file
There are tons of ways to accomplish this though. You just want to avoid using controls (or in Excel's case, WorkSheets) as data structures. In most places the underlying software will treat a NUL as a "stopper" for a value.
Here is a silly example that uses UDTs:
Code:
Option Explicit
Private Type RecType1
Field1 As String * 5 'Left-justified, space fill.
Field2 As String * 10 'Right-justified, space fill.
Field3 As String * 10 'Right-justified, zero fill.
Field4 As String * 2 'We'll put two NUL characters in this.
End Type
Private Type RecType1AsCLOB
CLOB As String * 27
End Type
Private Sub WriteTheFile()
Dim F As Integer
Dim RecType1 As RecType1
Dim RecType1AsCLOB As RecType1AsCLOB
F = FreeFile(0)
Open "exported.txt" For Output As #F
With RecType1
.Field1 = "ABCDE"
RSet .Field2 = CStr(1)
.Field3 = Right$(String$(Len(.Field3) - 1, "0") & CStr(66), Len(.Field3))
.Field4 = String$(2, vbNullChar)
End With
LSet RecType1AsCLOB = RecType1
Print #F, RecType1AsCLOB.CLOB
With RecType1
.Field1 = "X"
RSet .Field2 = CStr(2)
.Field3 = Right$(String$(Len(.Field3) - 1, "0") & CStr(132), Len(.Field3))
.Field4 = String$(2, vbNullChar)
End With
LSet RecType1AsCLOB = RecType1
Print #F, RecType1AsCLOB.CLOB
Close #F
End Sub
Dump of the result:
Code:
0000 41 42 43 44 45 20 20 20 20 20 20 20 20 20 31 30 ABCDE 10
0010 30 30 30 30 30 30 30 36 36 00 00 0d 0a 58 20 20 000000066....X
0020 20 20 20 20 20 20 20 20 20 20 20 32 30 30 30 30 20000
0030 30 30 30 31 33 32 00 00 0d 0a 000132....
-
Jul 4th, 2018, 04:19 PM
#10
Re: Exporting NULL character in a txt file
Originally Posted by akel
exported in an IBM mainframe
Hi Akel. Welcome to VBForums.
First, you say "exported" in an IBM mainframe. I assume you mean "imported" by an IBM mainframe. If that is correct, I think it would help us all a great deal if we understood the text file specification of whatever program on the IBM will be doing this importing.
Is it expecting a comma-separated-variable (CSV) file? Or, is it a fixed-length-field ASCII file? Is it ASCII (or possibly ANSI allowed), or is it possibly some flavor of Unicode?
If it's an IBM mainframe, and it's a true text (ASCII) file, it might also be expecting a Unix-style line terminator (a single &h0A rather than &h0D &h0A which is what VB6/VBA will do).
I think several of us would be delighted to help you, but I think a full set of specifications for this output file would be immensely helpful.
Good Luck,
Elroy
Last edited by Elroy; Jul 4th, 2018 at 04:29 PM.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Jul 4th, 2018, 04:29 PM
#11
Re: Exporting NULL character in a txt file
And personally, I'm confused about the confusion of putting a Chr$(0) (or vbNullChar) into a text file. It seems to work absolutely fine for me:
Code:
Open "c:\users\elroy\desktop\test.txt" For Output As 1
Print #1, vbNullChar; "asdf"
Close 1
Take Care,
Elroy
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Jul 4th, 2018, 04:37 PM
#12
Re: Exporting NULL character in a txt file
I suspect the problems arise when putting them into WorkSheet cells and then trying to "export" as text.
-
Jul 4th, 2018, 07:39 PM
#13
Re: Exporting NULL character in a txt file
Yeah, it doesn't look like that's going to work. I just tested from the immediate window in Excel, and here's what I got:
Code:
activeworkbook.activesheet.cells(1,1) = chr$(0)
? len(activeworkbook.activesheet.cells(1,1).value) ' Reports 0 Len.
? len(activeworkbook.activesheet.cells(1,1).value2) ' Reports 0 Len.
I also played around a bit with the format of the cell, and that didn't change anything. I even played around with using in the XML of the worksheet, and it didn't like that either. I don't think you're going to get an &h0 into an Excel cell.
akel, if you tell us how you're dumping the data to a text file, we might be able to help you get it done another way. There's not a problem getting a Chr$(0) into a text file. It's just that we can't get it into an Excel cell.
Good Luck,
Elroy
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Jul 5th, 2018, 07:14 AM
#14
Thread Starter
New Member
Re: Exporting NULL character in a txt file
Hello,
Thanks to the both of you for helping me, I really appreciate.
To answer you :
" I don't think you're going to get an &h0 into an Excel cell."
The closest I've done is ChrW(&H2400) which does display a "NULL" character in the cell. But then again, I can't manage to put it in a .txt file.
if you tell us how you're dumping the data to a text file, we might be able to help you get it done another way.
As you figured out, I first write everything into Excel's cells then use the following to generate a .txt file :
Code:
Sub Export_H()
Dim i As Integer
i = 4
While Cells(i, 8).Value <> ""
i = i + 1
Wend
Application.ScreenUpdating = False
ActiveSheet.Range("H4:H" & i - 1).Select
Selection.Copy
Workbooks.Add
Columns("A:A").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\Export_H.txt", _
FileFormat:=xlUnicodeText, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
I'm really new at Visual Basic so there is probably better ways to achieve what I want to do and I'm all ears.
-
Jul 5th, 2018, 09:06 AM
#15
Re: Exporting NULL character in a txt file
Hi akel,
I don't have a lot of time this morning, so I can't put together an example. However, if you're dealing with special situations like the null characters, I might build a loop that just went through all the rows and columns, and "manually" dumped it to a text file that I had opened for "Output". That way, you could do some checking, and possible replacements, for your special situations when the data were dumped.
Also, if you do it that way, don't forget your UsedRange property. I'll have to let you sort out how to use that.
Also, the reason your ChrW(&H2400) isn't working is because that's a Unicode (USC-2) character. I didn't study your code in detail; however, you're not going to get a Unicode character into an ASCII text file. USC-2 takes 2-bytes per character, whereas ASCII is always 1-byte per character. However, if you're "manually" (with code) dumping your file, you could check for these and replace them with Chr$(0) when written to the file. The Replace$() function would be perfect for this.
Good Luck,
Elroy
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Jul 5th, 2018, 09:13 AM
#16
Re: Exporting NULL character to a txt file from Excel
I'm still not quite understanding. Why do you NEED to export a Null in the middle of the string? What are you doing with it once you have access to it in that manner?
-
Jul 5th, 2018, 11:20 AM
#17
Thread Starter
New Member
Re: Exporting NULL character in a txt file
Hi Elroy,
It took me a few hours, but I did it !
As you suggested I replaced "manually" every character I wanted as Chr$(0) in the texte file after the exportation.
Code:
Dim search As String
search = "ยต"
Dim repl As String
repl = Chr$(0)
Dim text As String
Dim arr As Variant
Dim line As String
Dim item As Variant
Open "C:\test.txt" For Binary Access Read Lock Read As #1
text = Input$(LOF(1), #1)
Close #1
arr = Split(text, vbNewLine)
Open "C:\test_repl.txt" For Output As #2 '<--temporary file
For Each item In arr
line = Replace(CStr(item), search, repl, 1, -1)
Print #2, line
Next
Close #2
Kill "C:\test.txt"
Name "C:\test_repl.txt" As "C:\test.txt"
I can finally move forward.
Thank you very much for all your help.
-
Jul 5th, 2018, 04:01 PM
#18
Re: Exporting NULL character to a txt file from Excel
post a sample of a text file saved
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jul 6th, 2018, 02:20 AM
#19
Re: Exporting NULL character in a txt file
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|