Convert Date Field From Excel Import
Karl Burrows
national affiliate every quarter and they send the date members joined in
the following format as a General Number (not formatted as a date):
YYYYMMDD
I would like to be able to import these lists into my database that is setup
as a Date field MMDDYYYY. Is there a way to do this?
Thanks!!
--
Remove 'spam' from email address to contact me directly
Tom Wickerath
You didn't say what file type that you are receiving. Can you save it as a comma separate
variable (.CSV) text file? For example, if your data is in Excel, you can export it to
this format. It is helpful if your file includes field names, separated by commas, in the
first row.
In Access, click on File > Get External Data > Import
Select Text Files (*.txt; *.csv; *.tab; *.asc) in the Files of Type combo box and browse
to your source file.
Select Delimited for a comma delimited file
Click on the Next button. Place a check in the checkbox that reads: "First Row Contains
Field Names".
Click on the Advanced... button. Here, you can define an import specification.
Select Data Type as Date/Time
Select Date Order as YMD
Important ---> Clear the Date Delimiter (I had to select it and press on my space bar)
I recommend clearing the Time Delimiter as well
Click Save As... to save this import spec. for future use.
Follow the rest of the prompts to finish the import process.
The next time you use this import spec., make sure to select it by clicking on Advanced...
and then selecting the Specs button.
Import specs. can sometimes be a bit problematic. Post a message back if you cannot get
this to work. I can suggest another method, using Excel and some built-in functions, to
reorder your data in the proper order with a date delimiter such as the slash character
( / ). Access should be able to import that without any problems.
Tom
_________________________________
"Karl Burrows" <kf...@spambellsouth.net> wrote in message
news:4LQFb.9587$uh1....@bignews6.bellsouth.net...
Karl Burrows
I use the Import data all the time in Excel. Glad to know that will work in
Access, however, will that remove the existing data? We plan to add our own
local info to the national data, so we would want to keep the current
records intact, updating as needed from the Excel workbook.
Thanks!!
"Tom Wickerath" <AOS168Remove...@comcast.net> wrote in message
news:Ap-dnQVYw4F...@comcast.com...
Tom Wickerath
> Glad to know that will work in Access, however,
> will that remove the existing data?
No. You should be able to select an existing table and import new records into it without
affecting your existing data. The trick is to first use Excel's Save As feature to save
your .XLS file to a .CSV text file. Import specifications are available for text files.
Too bad they are not available for other file types, such as .XLS.
Tom
____________________________________________
"Karl Burrows" <kf...@spambellsouth.net> wrote in message
news:ZjZFb.5209$ED....@bignews2.bellsouth.net...
Sorry, I get the data in an Excel worksheet.
I use the Import data all the time in Excel. Glad to know that will work in
Access, however, will that remove the existing data? We plan to add our own
local info to the national data, so we would want to keep the current
records intact, updating as needed from the Excel workbook.
Thanks!!
____________________________________________
Karl Burrows
to date and then set the field properties.
This Excel worksheet gets sent to us each quarter, so there's not much I can
do without creating another worksheet in Excel to import data, strip out the
year, day and month separately and then combine again and then import into
Access.
Surely there is a better way...
Thanks!
"Tom Wickerath" <AOS168Remove...@comcast.net> wrote in message
news:K6idnQ3UQ6f...@comcast.com...
Tom Wickerath
> I got the data imported, but I still cannot change the formatting from
> text to date and then set the field properties.
I'm not sure what you're trying to say. Did you have success importing it into Access as
a Date/Time datatype? The directions I provided earlier did not include importing first
as text data. These directions had you start with a text data source, which was imported
as Date/Time data.
However, I suppose you could import first as strictly text. Then add a new field in the
same table that is a Date/Time datatype. For purposes of this example, lets name the
table "tblImportedDates", the text field "strDateIn", and the new field "dteDate" (without
the quotes). This should work as long as your data includes leading zeros for months 1-9,
and leading zeros for days 1-9 (ie. 20040101 for January 1, 2004). So, you will do a
standard import into the strDate field with data that is in YYYYMMDD format. The new
dteDate field will not include any data yet.
1. Create a new query in design view, but do not select any tables--just close the Show
Table dialog box.
2. Click on the black SQL button, which should be the first toolbar button visible, to
open the SQL window. You should see the word SELECT; selected. Press the backspace key
to delete this selected keyword.
3. Copy (Ctrl + C) the following SQL statement and paste it (Ctrl + V) into the SQL
window:
UPDATE tblImportedDates
SET tblImportedDates.dteDate =
Left([strDateIn],4) & "/" &
Mid([strDateIn],5,2) & "/" &
Right([strDateIn],2);
4. Try running the query. You should receive a warning that reads in part:
"You are about to update x row(s).", where x = the number of records you imported.
5. Save the query.
Please let me know if this idea works better for you.
Tom
________________________________________
"Karl Burrows" <kf...@spambellsouth.net> wrote in message
news:acaGb.6792$ux4...@bignews4.bellsouth.net...
I got the data imported, but I still cannot change the formatting from text
to date and then set the field properties.
This Excel worksheet gets sent to us each quarter, so there's not much I can
do without creating another worksheet in Excel to import data, strip out the
year, day and month separately and then combine again and then import into
Access.
Surely there is a better way...
Thanks!
________________________________________