Linked table date error trapping (1 Viewer)

sportsguy

Finance wiz, Access hack
Local time
Today, 02:44
Joined
Dec 28, 2004
Messages
363
Good afternoon whereever you are in the world!

I have a linked table from a text file with a date time field.
When I open up the table to look at the data in the date time field, there are many date stamps, and a few #num!

I am wanting to trap the error and return null . . .

Code:
Function UploadTime(anyTime As Variant) As Date

If Str(anyTime) = "#Num!" Then
    UploadTime= Null
Else
    UploadTime= anyTime
End If

End Function
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:44
Joined
Oct 29, 2018
Messages
21,613
Are you trying to do this in a query? Try using the IsDate() function.
 

sportsguy

Finance wiz, Access hack
Local time
Today, 02:44
Joined
Dec 28, 2004
Messages
363
Are you trying to do this in a query? Try using the IsDate() function.
Didn't work, none of the IsFunctions work:
IsError didn't work, IsNumeric didn't work, IsDate didn't work. .

That was my last attempt, I remember I did it once about 15 -20 years ago. .

Code:
Function UploadTime(anyTime As Variant) As Date

If IsDate(anyTime) = True Then
    UploadTime = anyTime
Else
    UploadTime = Null
End If

End Function

Doesn't work
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 07:44
Joined
Sep 21, 2011
Messages
14,607
Time to upload the dB then.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 28, 2001
Messages
27,488
This is stated as being a "text" file so unless I miss my guess, the whole file is text and you are hoping to interpret the field as a date. It would be really good if you would trap an error while trying to input this file so we can see what is liked.

There is another aspect, though. From a casual viewing, you can't tell the difference between a date/time field and a date/time-formatted text field until and unless you know the field's actual data type - but you already said this was a text file so the odds don't favor it being a date/time field. Are you trying to import this from some type of text like a .CSV file? If it is text file then there is no reason for a #Num! to be present because why would it convert anything? Therefore, there is something you haven't told us yet about this process or that file.
 

sportsguy

Finance wiz, Access hack
Local time
Today, 02:44
Joined
Dec 28, 2004
Messages
363
The file is downloaded as a tab delimited txt file from the SEC website, extracted from a zip file
As I typed in the original post, most of the rows convert to date properly in the linked text file specification just fine
There are a few lines where the linked file doesn't have a proper entry to convert to a date time format through the linked file specification.

I am trying to trap the error and replace it with a null in the upload query to my SQL Server. . .

simple as that. .
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:44
Joined
Oct 29, 2018
Messages
21,613
The file is downloaded as a tab delimited txt file from the SEC website, extracted from a zip file
As I typed in the original post, most of the rows convert to date properly in the linked text file specification just fine
There are a few lines where the linked file doesn't have a proper entry to convert to a date time format through the linked file specification.

I am trying to trap the error and replace it with a null in the upload query to my SQL Server. . .

simple as that. .
One potential approach: Change the spec to import all columns as Text into a Temp table and then use an APPEND query to convert and transfer the data into SQL Server?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 28, 2001
Messages
27,488
Is the field in the input file blank? Does it contain other text? Is the record that causes this error shorter than it should be?
 

sportsguy

Finance wiz, Access hack
Local time
Today, 02:44
Joined
Dec 28, 2004
Messages
363
One potential approach: Change the spec to import all columns as Text into a Temp table and then use an APPEND query to convert and transfer the data into SQL Server?
Yea, was trying to avoid that, since I had figured out the code prior to moving to SQL server admin decades ago, its just lost in the grey matter and who knows where the code is now. .
 

ebs17

Well-known member
Local time
Today, 08:44
Joined
Feb 7, 2020
Messages
2,022
In the import specification, change the data type for this column to text. Text can initially record everything.
Then, to link the text table, use a query like this:
SQL:
SELECT T.[FieldList]
FROM [Text;DSN=NameSpecification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;DATABASE=C:\temp\].[file01.txt] AS T
You can now install a check for content and conversion to date for your date column, using a calculation expression or an applied VBA function.
This selection query can be used as part of an append query.
 

ebs17

Well-known member
Local time
Today, 08:44
Joined
Feb 7, 2020
Messages
2,022
There are a few lines where the linked file doesn't have a proper entry to convert to a date time format through the linked file specification.
You could show these examples.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:44
Joined
Feb 19, 2013
Messages
16,731
When I open up the table to look at the data in the date time field, there are many date stamps, and a few #num!
Have you tried identifying a row or three where you get #num then opened the source file in notepad ( not excel) find the rows and see what the values actually are and how they differ from a valid value?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:44
Joined
Sep 12, 2006
Messages
15,749
Good afternoon whereever you are in the world!

I have a linked table from a text file with a date time field.
When I open up the table to look at the data in the date time field, there are many date stamps, and a few #num!

I am wanting to trap the error and return null . . .

Code:
Function UploadTime(anyTime As Variant) As Date

If Str(anyTime) = "#Num!" Then
    UploadTime= Null
Else
    UploadTime= anyTime
End If

End Function
I don't think #num is the actual value. It's the way access reports an invalid value, I think.

Would you know a specific value that produces the #num error in your code?. I can't think how a variant might not be representable as a string.
 

sportsguy

Finance wiz, Access hack
Local time
Today, 02:44
Joined
Dec 28, 2004
Messages
363
Have you tried identifying a row or three where you get #num then opened the source file in notepad ( not excel) find the rows and see what the values actually are and how they differ from a valid value?

SOLVED: So CJ London wins the "I discovered a lazy data analyst/programmer", 👋 GUILTY

I don't use excel, unless i have to. . I started using MS Multiplan prior to it being named Excel (I be old!) and once MS Access appeared on the scene, I started using that in the early 1990's and started hating on Excel for trying to be a poor man's database. . . If you notice my join date, that is 10 years after starting with MS Access. . . now retired so I be lazy

I did (finally) look at the source data in Notepad ++, and here is the issue:

I used the find/replace to eliminate the '.0' decimal in the time stamps. .
Find ":00.0" Replace with ":00"

That fixed the 99%, but then there were other malformed timestamps in the source file which was causing the issues.
example "9/18/2023 8:50.00.0 AM" which didn't get edited and replaced. . . so I found those and edit replaced them throughout the source document, and all loaded just fine. .
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:44
Joined
Sep 21, 2011
Messages
14,607
I remember MultiPlan. We created a system just using keystokes to generate weekly, monthly and yearly statistics. :)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:44
Joined
Feb 19, 2013
Messages
16,731
there were other malformed timestamps in the source file
the method I use is to use sql to correct data on import - much as suggested by ebs17 in post#10

Although I prefer to use schema.ini rather than using the specifications.
 

Users who are viewing this thread

Top Bottom