UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Type Mismatch 13 With Filesystemobject And Split Function, Access 2016    
 
   
ozdave
post Feb 28 2018, 04:24 AM
Post#1



Posts: 54
Joined: 5-April 17
From: Albury, Australia


I'm having a problem with my code that is producing a Type Mismatch Err 13. I'm attempting to import a CSV file by using the FileSystemObject and deploying the Split function to parse the values. What I'm getting is Error 13 at the line where the DataArray = Split(ts.Readline, ", "). I've double checked the CSV file and it seems OK. It has got me stumped. I ran it first without the initial ts.skipline and then again by skipping the first line, both ways produced the error. The reason I'm using the FSO to begin with is I initially used the DoCmd.TransferText method and I kept getting the Type Conversion failure. Similar error? Again there seemed to be no obvious reason for that, so I switched to using the FSO, but a similar error occurs there as well. Oh well, I'm hoping someone can make sense of it for me. Please help, thanks.

CODE
Public Sub ImportTextFile()
    
    Dim strFilePath As String
    Dim strFileName As String
    Dim FSO As Scripting.FileSystemObject
    Dim DataArray() As Variant
    Dim ts As Scripting.TextStream
    Dim i As Integer
    
    On Error GoTo ImportTextFile_Error
    
    'Open the database and recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("AMX_Ccard_PM")
    strFilePath = gvarFile
    
    'Open connection to CSV file
    Set FSO = New Scripting.FileSystemObject
    Set ts = FSO.OpenTextFile(strFilePath, ForReading)
    
    
    'Start data import from here
    'skip header row if needed
    ts.SkipLine
    
    'loop through rows in CSV file
    Do Until ts.AtEndOfStream
        'read first line into array
        DataArray = Split(ts.ReadLine, ",")
        
        'add a new record
        rst.AddNew
        
        'read values from array into specific columns
        rst("TransDate").Value = DataArray(0)
        rst("Amount").Value = DataArray(1)
        rst("Details").Value = DataArray(2)
        
        'commit changes to record
        rst.Update
    Loop
    
    MsgBox "Import Complete", vbInformation, APP_NAME
    

CleanExit:
On Error Resume Next
Set FSO = Nothing
Set rst = Nothing
Set dbs = Nothing
    Exit Sub

ImportTextFile_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportTextFile of Sub basDataManagement"
    Resume CleanExit
End Sub



Go to the top of the page
 
Larry Larsen
post Feb 28 2018, 04:41 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,169
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
May be worth a try, I like to place hold each line into a string before the Split() function takes over..

eg:
CODE
Do Until ts.AtEndOfStream
        'read first line into array
        strNextLine = ts.Readline
        DataArray = Split(strNextLine, ",")

thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
ozdave
post Feb 28 2018, 05:45 AM
Post#3



Posts: 54
Joined: 5-April 17
From: Albury, Australia


Thanks Larry, I just amended my code and ran it again but unfortunately same err 13 Type Mismatch. The first field in the CSV is a date field, could that be the culprit? Anyway, thanks for trying.
Go to the top of the page
 
cheekybuddha
post Feb 28 2018, 06:15 AM
Post#4


UtterAccess VIP
Posts: 9,978
Joined: 6-December 03
From: Telegraph Hill


Try declaring DataArray As Variant (without the array brackets)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LPurvis
post Feb 28 2018, 07:12 AM
Post#5


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Or, since Split returns an array of Strings, you could declare it as such.

Dim DataArray() As String

Cheers

--------------------
Go to the top of the page
 
ozdave
post Feb 28 2018, 06:09 PM
Post#6



Posts: 54
Joined: 5-April 17
From: Albury, Australia


I ran the code with ts.skipline enabled and when I removed the brackets from DataArray as variant it worked perfectly. But, I had disabled the ts.skipline, so that the first line was included, and it crashed again. The thing with the ts.skipline was it is to do with whether the text file has headers or it doesn't. Some bank's data do and some don't (have headers). This casts suspicion over the first row of the CSV. When the code broke, it was at the line rst("TransDate").Value = DataArray(0) I held the mouse over DataArray(0) and the intellisense said it was "i*?09-02-2017" in other words it had 3 hidden characters (the question mark was upside down) and I'm not sure if the * was a * or something else that looks like a *. The question seems to be somehow these characters have entered the first line and corrupted it. These characters seem to be hidden (meaning I can't "see" them by just looking at the file, so how do I get rid of them? By the way, I ran the code again and I queried the value of DataArray(0) in the Immediate window and the value was 09-02-2017. Those characters need to be removed, but they're not visible to the eye, how can I do this?? They seem to be what is bringing this process undone! BTW thanks for your replies.

Go to the top of the page
 
orange999
post Feb 28 2018, 07:30 PM
Post#7



Posts: 1,777
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Dave,

Does the bank that supplies this data have any documentation of the file structure?

For clarity, is it only the first record that has this "issue"?

--------------------
Good luck with your project!
Go to the top of the page
 
zaxbat
post Feb 28 2018, 08:24 PM
Post#8



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


think you are getting a null so it throws the error since it can't stuff it into a date field?

you could test every incoming field before accepting it but that would slow your import way down.

If (VarType(indat) = vbNull) or (VarType(indat) = vbEmpty) then
'ignore this field and move on to next
else
'process the field
endif

You could do a more comprehensive check but this is probably the main culprit.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
ozdave
post Mar 1 2018, 12:08 AM
Post#9



Posts: 54
Joined: 5-April 17
From: Albury, Australia


From my previous post, when I checked the value of DataArray(0) in the immediate window it returned, from the left, 3 characters that I have never seen before, the 1st was an i with 2 dots, the 2nd was like a double angle bracket >> and 3rd was an upside down question mark. I copied them into my previous post, and can be viewed there. What followed was the normal field value, (a date). The other fields appeared unaffected.

The code ran perfectly when ts.skipline was enabled, which showed the issue is with the first record. It has been those hidden, unseen characters at the beginning of the first line that has caused the problems. I need some code to remove them from my text file. Can You help?
Go to the top of the page
 
stevep
post Mar 1 2018, 03:24 AM
Post#10



Posts: 60
Joined: 9-November 14



Well, this is a kludge but might work.

It seems the sending system is adding 3 mystery characters to its export. If the first field is supposed to be a date, you could check the result with IsDate(), and if there is an error, try stripping off the first 3 characters and repeating.

firstline = mid(firstline,4)
' rest of process

This assumes the 3 mystery characters are *always* the first 3 characters in the first line in the csv file you get. But it should get you past this issue.
This post has been edited by stevep: Mar 1 2018, 03:32 AM
Go to the top of the page
 
ozdave
post Mar 1 2018, 04:57 AM
Post#11



Posts: 54
Joined: 5-April 17
From: Albury, Australia


Thanks for your suggestion. It sounds like a good idea, but, I suspect it will break as soon as it encounters the first unwanted character.
Go to the top of the page
 
PhilS
post Mar 1 2018, 05:37 AM
Post#12



Posts: 477
Joined: 26-May 15
From: The middle of Germany


The unexpected characters you encounter are a Unicode Byte Order Mark.

Open your text file explicitly as Unicode file and these characters should disappear from the input stream.

CODE
Const UnicodeFormat as Long = -1
Set ts = FSO.OpenTextFile(strFilePath, ForReading, False, UnicodeFormat )


The built-in text import of Access would probably work as well, if you would specify utf8 as character encoding for the file.


--------------------
New article: The Access ADP Story
Go to the top of the page
 
ozdave
post Mar 3 2018, 03:00 AM
Post#13



Posts: 54
Joined: 5-April 17
From: Albury, Australia


Thanks Phil, I tried it but unfortunately the code broke at that same place, see above. I have done further research and found a function that removes non-Ascii characters, which sounds like what I need but I can't get it to work for me. I'm sure it's me not the function. I've included it below, I'm not sure how the string I pass to it tells me whether my file does actually contain non-Ascii characters. That is my shortcoming not the author of it. Apparently I have, in my CSV file, a Byte Order Mark (BOM) as I have been told. I don't know how it got there but I'd sincerely like it to leave! I really want to be able to read this file into my Access table, which still, at this stage, I've been unable to do. I appeal to anyone who is following along on this thread, please post an answer. Thanks.

CODE
Public Function StringContainsNonASCII(str As String) As Boolean
    Dim i As Integer
    'Default is false
    StringContainsNonASCII = False
    'Remove question marks
    str = Replace(str, "?", "")
    For i = 1 To Len(str)
        'Search for question marks
        If Asc(Mid(str, i, 1)) = 63 Then
            StringContainsNonASCII = True
            Exit Function
        End If
    Next i
End Function

Go to the top of the page
 
LPurvis
post Mar 5 2018, 08:45 AM
Post#14


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi there.

Have you examined the file itself? (I mean opened the file in an application and examined the first row of data.)
As mentioned, it's possible the Unicode characters are incorrect or that the VBE can't render them.
(Though the VBE should render such an non-ASCII character as a normal question mark, rather than an upside down one)

For example:
CODE
    Dim strText As String
    
    strText = "Hello" & ChrW(9702)
    Debug.Print strText

won't display the empty bullet point. The VBE can't render that. But the Unicode supporting Access UI can display it in a control or table.

Examining the file will help you determine what character is at the start of that date value.
Until you can return a valid date, you'll always get the Type Mismatch trying to assign it to the Date variable.
If all you want are ASCII characters, you can amend your function to not just look for 63.
If all you want are date valid characters, then you can limit even further.

To make that super simple, it could be just:
CODE
Function fReturnDateChars(strVal As String)

    Dim intPos As Integer
    Const cDateChars = "0123456789/"
    For intPos = 1 To Len(strVal)
        If InStr(cDateChars, Mid(strVal, intPos, 1)) > 0 Then
            fReturnDateChars = fReturnDateChars & Mid(strVal, intPos, 1)
        End If
    Next
    
End Function

Giving you something like:
rst("TransDate").Value = fReturnDateChars(CStr(Nz(DataArray(0),"")))

But you're still making some assumptions as to what you're receiving. See how you go.

Cheers


--------------------
Go to the top of the page
 
cheekybuddha
post Mar 5 2018, 09:38 AM
Post#15


UtterAccess VIP
Posts: 9,978
Joined: 6-December 03
From: Telegraph Hill


I had an issue with UTF8 text files and FSO recently, though I can't remember exactly what.

In the end I ditched the FSO in favour of ADO streams instead.

Your original code would be adjusted like:
CODE
Public Sub ImportTextFile()
    
    Dim strFilePath   As String
    Dim strFileName   As String
    Dim DataArray()   As String
    Dim i             As Integer
    
    Dim ts            As Object
    Dim strLine       As String
    
    Const UTF8        As String = "utf-8"
    Const adReadLine  As Integer = -2
    
    On Error GoTo ImportTextFile_Error
    
    'Open the database and recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("AMX_Ccard_PM")
    strFilePath = gvarFile
    
    'Open connection to CSV file
    Set ts = CreateObject("ADODB.Stream")
    With ts
      .Charset = UTF8
      .Open
      .LoadFromFile strFilePath
      'Start data import from here
      'skip header row if needed
      .SkipLine
      'loop through rows in CSV file
      Do Until .EOS
        'read first line into array
        strLine = .ReadText(adReadLine)
        DataArray = Split(strLine, ",")
        'add a new record
        rst.AddNew
        'read values from array into specific columns
        rst("TransDate").Value = DataArray(0)
        rst("Amount").Value = DataArray(1)
        rst("Details").Value = DataArray(2)
        'commit changes to record
        rst.Update
      Loop
      .Close
    End With
    
    MsgBox "Import Complete", vbInformation, APP_NAME

CleanExit:
On Error Resume Next
    Set ts = Nothing
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

ImportTextFile_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportTextFile of Sub basDataManagement"
    Resume CleanExit
End Sub


Might be worth a go to see if it handles the byte order characters better.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ozdave
post Mar 8 2018, 09:20 PM
Post#16



Posts: 54
Joined: 5-April 17
From: Albury, Australia


Thanks Leigh, you've really given me something to think about. The only place I have examined the file as a whole is in NotePad, which of course is a Text Editor and as such it didn't display those characters in the first row. The only thing I have done "out of the ordinary" was using Excel to combine a couple of files to create the file in question. I exported it from Excel as a CSV file. Maybe Excel put those characters there? I don't know. I was only able to "see" the characters in question was to hold the mouse over the value of DataArray(0) with the intellisense. They are what PhilS described earlier as a Byte Order Mark, which is apparently made up of Unicode characters. OK, so the point is getting the date values into the "TransDate" field as you've demonstrated in your function in your post. So, I will give that a go and report back. Thanks.
Go to the top of the page
 
ozdave
post Mar 8 2018, 11:17 PM
Post#17



Posts: 54
Joined: 5-April 17
From: Albury, Australia


Success Leigh! Not at first though. I F8'ed through the code and I noticed your function wasn't building the date with a date separator in it. It produced an Error 3421, data type conversion error. I realised my dates have a hyphen as separator, so I inserted a hyphen into your constant cDateChars and it worked! That was a simple yet brilliant function Leigh! Rather than trying to get rid of the rogue characters it just side-stepped their presence and continued on with the rest of the job. Thanks very much! My only question would be whether to leave your function there to continue on, or view this as a one off situation and use it if needed in some future event? Anyway, thanks.
This post has been edited by ozdave: Mar 8 2018, 11:25 PM
Go to the top of the page
 
LPurvis
post Mar 11 2018, 09:40 AM
Post#18


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi Dave

Sorry for not replying sooner.
I'm glad you got there. And yes, I'd not read back up the thread to check what the date separator was that you were using in this case, but adding both - and / to the constant is totally appropriate.
And also I see no reason not to leave the function.
It's quite normal for developers to have standard modules they import into any project. You can then call on the contained functions to use as if they were built in ones. You can judge for yourself what you want to keep for such purposes.

For example, you could turn that function into something more re-usable (generic that can operate on other strings).
CODE
Function fReturnChars(strVal As String, strDesiredChars As String)

    Dim intPos As Integer
    
    For intPos = 1 To Len(strVal)
        If InStr(strDesiredChars, Mid(strVal, intPos, 1)) > 0 Then
            fReturnChars = fReturnDateChars & Mid(strVal, intPos, 1)
        End If
    Next
    
End Function

And call that when you need it - including from your reworked fReturnDateChars
CODE
Function fReturnDateChars(strVal As String)

    Const cDateChars As String = "0123456789/-"

    fReturnDateChars = fReturnChars(strVal, cDateChars)

End Function


Anyway, continued success.

Cheers

--------------------
Go to the top of the page
 
ozdave
post Mar 12 2018, 03:16 AM
Post#19



Posts: 54
Joined: 5-April 17
From: Albury, Australia


Hi Leigh,

I wonder with the first function that you've listed
CODE
Function fReturnChars(strVal As String, strDesiredChars As String)
could you flesh that one out a little bit please? In terms of the arguments I get that strVal is a string that I may need to deal with, and, strDesiredChars I'm a little bit fuzzy on. And if you could say something about the If statement as well. Thanks Dave.
Go to the top of the page
 
LPurvis
post Mar 12 2018, 08:12 AM
Post#20


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

>> strDesiredChars I'm a little bit fuzzy on.

That's basically a list of characters that you're wanting to be allowed in the return value. In the example of an altered fReturnDateChars you'll see that it's passing the string constant "0123456789/-"
There's no need for it to be a constant of course. That's just for clarity. The function could equally be:
CODE
Function fReturnDateChars(strVal As String)

    fReturnDateChars = fReturnChars(strVal, "0123456789/-")

End Function

Passing the string as a list of permitted characters in the second parameter.

>> if you could say something about the If statement as well.
Indeed. The line:
CODE
If InStr(strDesiredChars, Mid(strVal, intPos, 1)) > 0 Then

It's taking one character at a time from the string you pass to check (strVal) and seeing if that character is contained in the permitted string list.
If it's in there, it's returned and added to the returned value from the function.
The Instr function is a built-in VBA function which you can get further details on in Online Help (hitting F1 with the function name selected).

Cheers

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 02:23 AM