Linked table date error trapping

[sportsguy]

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

Function UploadTime(anyTime As Variant) As Date

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

End Function

 

[theDBguy]

Are you trying to do this in a query? Try using the IsDate() function.

 

[sportsguy]

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

Function UploadTime(anyTime As Variant) As Date

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

End Function

Doesn't work

 

[Gasman]

Time to upload the dB then.

 

[The_Doc_Man]

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]

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]

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]

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]

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]

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:

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]

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]

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]

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

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]

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]

I remember MultiPlan. We created a system just using keystokes to generate weekly, monthly and yearly statistics.

 

[CJ_London]

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.