problems replacing or splitting on a tab contained in a string (1 Viewer)

Zedster

Registered User.
Local time
Today, 09:16
Joined
Jul 2, 2019
Messages
105
I am trying to process a tab delimited text file, the ultimate aim to populate a table in access with the contents of each line. But I am unable to detect the "tabs" in the test file. I have tried using the split function with various arguments and also strReplace with various argument. None of them can detect a tab:

Code:
Public Function ReplaceTabs(strInput) As String

10    On Error GoTo err_ReplaceTabs

20        ReplaceTabs = Replace(strInput, vbTab, ">>><<<") 'doesn't work but should? string with tabs doesn't split
'20        ReplaceTabs = Replace(strInput, Chr(9), ">>><<<") 'also doesn't work
'20        ReplaceTabs = Replace(strInput, " ", ">>><<<") 'tab key from keyboard also doesn't work it splits on tabs but on spaces as well

exit_ReplaceTabs:
30        Exit Function
          
err_ReplaceTabs:

40        MsgBox Err.Number & " " & Err.Description & vbCr & vbCr & "Error Line: " & Erl
50        Call Logger("Error", "meetings.basFunctions.ReplaceTabs," & Erl, Err.Number, Err.Description)
60        Resume exit_ReplaceTabs

End Function

Code:
Public Sub SplitStringByTab(strInput As String)

'this doesnt appear to work for any attempt to split by tab unsure why

10    On Error GoTo err_SplitStringByTab

          Dim arrOutput() As String
          Dim i As Integer

20        arrOutput = Split(strInput, vbTab)   'doesn't work but should? string with tabs doesn't split
'20        arrOutput = Split(strInput, Chr(9))  'doesn't work?
'20        arrOutput = Split(strInput, " ")      'tab key from keyboard also doesn't work it splits on tabs but on spaces as well

30        For i = LBound(arrOutput) To UBound(arrOutput)
40            Debug.Print i & " = " & arrOutput(i)
50        Next i
          
exit_SplitStringByTab:
70        Exit Sub
          
err_SplitStringByTab:

80        MsgBox Err.Number & " " & Err.Description & vbCr & vbCr & "Error Line: " & Erl
90        Call Logger("Error", "meetings.basFunctions.SplitStringByTab," & Erl, Err.Number, Err.Description)
100       Resume exit_SplitStringByTab

End Sub

I am trying to run either procedure from the immediate window. I have tried manually typing in a string with tabs and also copying and pasting a line from a tab delimited text file. Nothing appears to work. Driving me mad!
 

arnelgp

error reading drive A:
Local time
Today, 17:16
Joined
May 7, 2009
Messages
10,878
Maybe ther is no Tab after all.
Post the raw .txt file.
 

Zedster

Registered User.
Local time
Today, 09:16
Joined
Jul 2, 2019
Messages
105
Maybe ther is no Tab after all.
Post the raw .txt file.

The file is a tab delimited text file which has been output from Excel. I have attached an example. I tried copying and pasting a single line (third one down) as an argument to the function & sub in the VBA immediate window. I also tried typing a string into the function and sub "A<tabkey>B<tabkey>C". In both cases the sub does not split the string it returns an array with a single item which is the entire line, the function does not substitute ">>><<<" for the tabs.

It appears the tabs in both tests are not being recognised.
 

Attachments

  • example.txt
    442 bytes · Views: 21

arnelgp

error reading drive A:
Local time
Today, 17:16
Joined
May 7, 2009
Messages
10,878
if you remove the "Project Task" from the textfile, you can import it to a table.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:16
Joined
Sep 21, 2011
Messages
7,179
I cannot get the string to split using vbTab. It comes back out as one string, the same as it goes into the function.?
 

Minty

AWF VIP
Local time
Today, 09:16
Joined
Jul 26, 2013
Messages
7,555
According to a variety of editors there are definitely tabs in it;
1601039896983.png

the Arrows indicate a tab and the last one is one I typed in.

Maybe vbTab isn't the same?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:16
Joined
Oct 29, 2018
Messages
13,305
Hi @Zedster. How did you populate strInput with your file content? Just curious...

1601040248266.png
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 09:16
Joined
Sep 21, 2011
Messages
7,179
I have multiple chr(32) in between the words with this string copied?
Code:
strTab = "Owner   Start   Deadline    Status  Comments"
 

June7

AWF VIP
Local time
Today, 00:16
Joined
Mar 9, 2014
Messages
3,362
Do this test in VBA immediate window:

?vbTab
generates 4 non-printing characters which do not equate to spaces
?Chr(9)
also generates 4 non-printing characters which do not equate to spaces
?Chr(9) =vbTab
returns true

I wrote a procedure to print the ASCII code for each character in line and guess what, code 9 is output for the field separator. So then I tested Split() and Replace() functions and they work.

Provide your code for reading text file.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 01:16
Joined
Mar 14, 2017
Messages
2,838
@Zedster
I created a table in Access with each one of the columns indicated in the text file. Then I removed the top line of the text file (which you can also automate using VBA) superfluous text.
I then used the wizard (which you can also automate using TransferText) and imported the file as a tab-delimited text file. The import worked perfectly.

My advice is, just do it that way. Perform further needed actions on the data after you've imported it to a table.
 

arnelgp

error reading drive A:
Local time
Today, 17:16
Joined
May 7, 2009
Messages
10,878
coincidence?! on my test i did remove the Title and import the file that leads to suggesting post #4.
 

Zedster

Registered User.
Local time
Today, 09:16
Joined
Jul 2, 2019
Messages
105
@Zedster
I created a table in Access with each one of the columns indicated in the text file. Then I removed the top line of the text file (which you can also automate using VBA) superfluous text.
I then used the wizard (which you can also automate using TransferText) and imported the file as a tab-delimited text file. The import worked perfectly.

My advice is, just do it that way. Perform further needed actions on the data after you've imported it to a table.

I may have to resort to that, but I was hoping I wouldn't need to because I don't like things that don't work when it looks like they should.
 

Zedster

Registered User.
Local time
Today, 09:16
Joined
Jul 2, 2019
Messages
105
if you remove the "Project Task" from the textfile, you can import it to a table.
"Project Task" is not part of the problem, because I do not include it in my real import, just got lazy when I posted the txt file example and left it in.
 

Zedster

Registered User.
Local time
Today, 09:16
Joined
Jul 2, 2019
Messages
105
Do this test in VBA immediate window:

?vbTab
generates 4 non-printing characters which do not equate to spaces
?Chr(9)
also generates 4 non-printing characters which do not equate to spaces
?Chr(9) =vbTab
returns true

I wrote a procedure to print the ASCII code for each character in line and guess what, code 9 is output for the field separator. So then I tested Split() and Replace() functions and they work.

Provide your code for reading text file.

I decided to go back to basics as you have here, below is step by step what I did. What is clear is Access on my machine will not create tabs and will not recognise tabs in text strings. I am completely confused! In the example below I have used the vb immediate window and cut and pasted contents into Notepad++ with "show spaces" enabled. Lines 1 to 22 have been entered into the immediate window and then copied and pasted into Notepad ++. Lines 23 & 24 were entered into Notepad++ Notice when I enter the tab in notepad ++ I get an arrow representing the tab. But even when I cut and paste the Notepad++ string containing tabs into the immediate window, it does not recognise them. When I copy and paste back into Notepad++ the 4 arrows have each been replaced with 3 dots!

Frustrating!

Capture3.PNG


Update! I have now tried this on three separate machines two running access 363, one running access 2007. None of them appear to recognise tabs with the replace function using chr(9) or vbtab. Even if the string is copied and pasted from Notepad++ where I can see the tabs are there using the show whitespace option.
 
Last edited:

Zedster

Registered User.
Local time
Today, 09:16
Joined
Jul 2, 2019
Messages
105
SOLVED!

After a bit of reverse engineering it appears that Access recognises tabs from an excel generated tab delimited file as chr(32) not chr(9) and not vbTab. Weirdly, even if the tabs are cut and pasted from another application such as Notepad++ it converts them to chr(32). Gasman and Minty were along the right lines.

Interesting experiment, the below string with tabs between the letters is created in Notepad++:

a b c d e

Running it through a text to ascii converter gives:

97 9 98 9 99 9 100 9 101

The tab's are clearly chr(9)

Then if I copy and past this same string into Access immediate window. And copy and paste from the immediate window into the text to ascii converter I get.

97 32 32 32 98 32 32 32 99 32 32 32 100 32 32 32 101

So Access appears to convert a chr(9) character into three chr(32) symbols. Very strange!
 

arnelgp

error reading drive A:
Local time
Today, 17:16
Joined
May 7, 2009
Messages
10,878
are you sure, coz i already processed it.
remove the "Project Tasks" from the file and save it.
run the form.
 

Attachments

  • SplitDatText.zip
    45 KB · Views: 15

Zedster

Registered User.
Local time
Today, 09:16
Joined
Jul 2, 2019
Messages
105
are you sure, coz i already processed it.
remove the "Project Tasks" from the file and save it.
run the form.

I am not sure about anything anymore to be honest. I will checkout your code later today, have a busy morning. I have modified my sub, see below. I need to use three chr(32) rather than vbTab:

Code:
Public Sub SplitStringByTab(strInput As String)

'vbTab doesn't appear to recognise a tab, neither does chr(9), to recognise a tabe it appears to need Chr(32) & Chr(32) & Chr(32)

10    On Error GoTo err_SplitStringByTab

          Dim arrOutput() As String
          Dim i As Integer
          'use the split function
20        arrOutput = Split(strInput, Chr(32) & Chr(32) & Chr(32))
'20        arrOutput = Split(strInput, vbTab) 'doesn't recognise tab
'20        arrOutput = Split(strInput, Chr(9))'doesn't recognise tab
30        For i = LBound(arrOutput) To UBound(arrOutput)
40            Debug.Print i & " = " & arrOutput(i)
50        Next i
        
exit_SplitStringByTab:
70        Exit Sub
          
err_SplitStringByTab:

80        MsgBox Err.Number & " " & Err.Description & vbCr & vbCr & "Error Line: " & Erl
90        Call Logger("Error", "meetings.basFunctions.SplitStringByTab," & Erl, Err.Number, Err.Description)
100       Resume exit_SplitStringByTab

End Sub

When I run this in the immediate file and copy and paste one line of the tab delimited file it works fine (vbTab still doesn't though)

Code:
SplitStringByTab("XY002   Procure Decide on potential suppliers   Must be flexible to change  List of suppliers   AS 0819: contacted potential suppliers to identify capability   AS  8/19/2020           test data")
0 = XY002
1 = Procure Decide on potential suppliers
2 = Must be flexible to change  List of suppliers
3 = AS 0819: contacted potential suppliers to identify capability
4 = AS  8/19/2020
5 =
6 =
7 =   test data
 

Zedster

Registered User.
Local time
Today, 09:16
Joined
Jul 2, 2019
Messages
105
are you sure, coz i already processed it.
remove the "Project Tasks" from the file and save it.
run the form.

Just tried your code, it works, thanks for that. I am now wondering if the problem was caused by the immediate window converting vbTab to chr(32) chr(32) chr(32).

I was testing my function in the immediate window rather than feeding it the contents of a file.

Will do a bit more digging because I am keen to understand why.
 

Isaac

Lifelong Learner
Local time
Today, 01:16
Joined
Mar 14, 2017
Messages
2,838
But even when I cut and paste the Notepad++ string containing tabs into the immediate window, it does not recognise them. When I copy and paste back into Notepad++ the 4 arrows have each been replaced with 3 dots!
Well, I think that's to be expected. You simply cannot expect the Immediate window to be a professional text editor, into which can be pasted anything and everything. Sometimes people try to paste non English characters there (or in the vba project too), and expect it to "just work", which is not necessarily the case, depending on other factors.

If I were you, and really wanted to figure out what was going on with the vba, then the one and only way I would test it and consider this test meaningful would be to do something like:

Code:
dim fso as object, ts as object, strLine as string
set fso=createobject("scripting.filesystemobject")
set ts=fso.opentextfile("path to text file", 1)

'here you set up some loop, or a breakpoint or a watch whatever you're comfortable with to show you lines in the text file
'maybe something like:
do until ts.atendofstream=true
    strLine=ts.readline
    if instr(1,strLine,chr(9))>0 then
        'put a breakpoint here on next line:
        msgbox "found"
    end if
loop
And when your code breaks, ask the immediate window all your questions.
But DO NOT expect the immediate window to accurately represent copiable text in cases anything other than plain text. Neither the Immediate Window nor the VBA project is meant for that and all of those tests are meaningless.

You can then ask the immediate window:
Code:
?strLine
?Ubound(Split(strLine,chr(9)))
?Split(strLine,chr(9))(0)
?Split(strLine,chr(9))(1)
?Split(strLine,chr(9))(2)

In fact you could adjust the code I put in order to go through every single character of strLine and tell you precisely what character code it is. Then the guessing will be over.
But do not expect copy, paste, or view-rendering to be a meaningful and definitive way of testing this.
 

Users who are viewing this thread

Top Bottom