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
> Date Field With Am/pm Will Not Import, Access 2016    
 
   
LagoDavid
post Oct 31 2017, 10:40 AM
Post#1



Posts: 283
Joined: 12-October 03
From: Texas


I routinely receive a CSV text file that I am trying to import into Access that is failing to import the date field. The date field is in the format
08/21/2017 12:00:00 AM

While the import wizard recognizes the field as a General Date field, and creates a date field to store the data, the date data is not imported.

Apparently it is the AM qualifier that is causing the problem because I can import the file as an Excel file. When the file is opened in Excel, Excel strips the AM and converts the date to
08/21/2017 0:00

Access is then able to import this file as an Excel file. However, this is causing an extra step in the process I would like to avoid.

I have tried to import the data into an existing table with a general date field and I have tried importing the file into a new table, letting Access choose the date field. Neither works as long as the AM qualifier is present.

Is there a way to import this data directly without an intermediate step?
Go to the top of the page
 
Daniel_Stokley
post Oct 31 2017, 01:32 PM
Post#2



Posts: 246
Joined: 22-December 14
From: Grand Junction, CO, USA


Hello,
You might try importing that column as text.
Go to the top of the page
 
BruceM
post Oct 31 2017, 03:00 PM
Post#3


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


What is the import procedure you are using?
Go to the top of the page
 
LagoDavid
post Oct 31 2017, 05:56 PM
Post#4



Posts: 283
Joined: 12-October 03
From: Texas


I use a saved, wizard import to import the data into an existing table. Then from that table, I use a couple of queries to transfer the data into about three different tables.
Go to the top of the page
 
BruceM
post Nov 1 2017, 06:30 AM
Post#5


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


The import to the original table isn't working? I'm not very familiar with the import wizard, but here are some thoughts:

Save the csv file as an Excel file, format the field as date, and import from there

or

Import as text as Daniel suggested, then use CDate to convert the value to a date for transfer to the other tables
Go to the top of the page
 
LagoDavid
post Nov 1 2017, 07:56 AM
Post#6



Posts: 283
Joined: 12-October 03
From: Texas


Correct. The wizard throws errors and the date field does not get imported. The date field values are all null.

I was hoping to avoid either of those two work arounds since both require an extra step. I am currently converting to Excel and then importing from there. But it seems that if Excel can "convert" the date data, and change the 12:00:00 PM time portion to 0:00:00, that Access would also be able to do that.

Thanks to both of you for your replies.
Go to the top of the page
 
BruceM
post Nov 1 2017, 10:45 AM
Post#7


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I just did some tests with some random spreadsheet data. In one instance it was not able to import 12:00:00 AM into a date/time field, but I could not replicate the error. In other tests the import worked properly. I worked directly with CSV data. CSV files open in Excel by default, but opening such as file in Excel does not change the nature of the file.

When setting up the wizard, are you specifying the data type for the fields?

QUOTE
When the file is opened in Excel, Excel strips the AM and converts the date to 08/21/2017 0:00

Not exactly. It is just applying the default format for a date/time field. Most likely it is using General as the formatting choice, which tries to format the cell in a way appropriate to the data in it.

If the conversion fails consistently you may need to do an extra step. It may be to open the CSV file in Excel, find 12:00:00 AM, and replace it with nothing. Or import into a text field, and do a type conversion when moving the data to another table. I don't know how you are moving the data around within Access, but assuming you are running a procedure rather than performing a manual import each time you should be able to include the type conversion (CDate) in the procedure.

If you can attach a small amount sample data, with sensitive information removed or obfuscated, it may be possible to identify the problem. If you are importing into an existing table it would help to have that table also, or a screen shot or other presentation of design view of that table so it can be replicated.
Go to the top of the page
 
LagoDavid
post Nov 5 2017, 08:53 AM
Post#8



Posts: 283
Joined: 12-October 03
From: Texas


Bruce, thank you. I apologize for my delay in responding.

My workaround is fairly easy but I would prefer to simplify. The files are on a server as CSV files and I download them to my computer. As you say they default to open with Excel, and if I open them with Excel the data is converted to General Date with no AM/PM designation. So I now open them with Excel and then save them as an Excel file (.xlsx), and then run my saved import to get them into a temporary table in my Access database. Access will not import the files directly as CSV files, failing to recognize the date field. It is strange to me that Excel will convert them but Access will not.

I think that the best solution is going to be as you suggest, to create a procedure that imports the date field as text and use the type conversion CDate within the procedure. That sounds like what a "real" Access programmer would do, as opposed to my "wannabe" process.

To accomplish this, I assume I would create a form with a command button and a text box? The text box to identify the path and file name, and the command button to execute the procedure? Could you get me started with how to do this, or is this asking for too much help?

This will be my first effort to send anything with my message so I hope the screen shot of the temporary table works. Note that there are spaces in the field names only because that is how the field names are in the Excel file which is imported. I assume that if I have a procedure that I can map the fields and eliminate the spaces?
Go to the top of the page
 
LagoDavid
post Nov 5 2017, 08:55 AM
Post#9



Posts: 283
Joined: 12-October 03
From: Texas


well my file upload did not happen
trying again
Attached File(s)
Attached File  Capture.PNG ( 578.46K )Number of downloads: 5
 
Go to the top of the page
 
BruceM
post Nov 6 2017, 07:41 AM
Post#10


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


As an aside, Alt + PrtScn (Print Screen) should give a screen shot just of the active window.
Go to the top of the page
 
LagoDavid
post Nov 6 2017, 10:26 PM
Post#11



Posts: 283
Joined: 12-October 03
From: Texas


thank you for that tip.

I did not realize I had captured both monitors until I saw the post.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 02:47 PM