Results 1 to 19 of 19

Thread: [RESOLVED] Exporting NULL character in a txt file

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    Resolved [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!

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    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.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    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.

  4. #4
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    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.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    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.

  6. #6

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    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 .

  7. #7
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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.

  8. #8

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    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.

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    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....

  10. #10
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: Exporting NULL character in a txt file

    Quote Originally Posted by akel View Post
    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.

  11. #11
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    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
    
    Name:  nppp.png
Views: 1572
Size:  12.3 KB

    Name:  hex1.png
Views: 1424
Size:  7.4 KB

    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.

  12. #12
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    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.

  13. #13
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    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.

  14. #14

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    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.

  15. #15
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    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.

  16. #16
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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?

  17. #17

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    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.

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  19. #19
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Exporting NULL character in a txt file

    Threads Merged
    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
  •  



Click Here to Expand Forum to Full Width