Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Importing text files: Access doesn't like date/time 1
- Thread starter drhenry
- Start date
Can you show us the format of the date from the text file? It would also help to know the format of the date from your computer (American, european, etc) Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
-
1
- #3
tlbroadbent
MIS
When importing, Access will recognize the date/time field on the database but needs instruction about the import file formats.
Have you set the import specification date format to match the date format of your import file? In the import specification you can set such attributes as date order (mdy, ymd, dmy, etc), 2 or 4 digit years, leading zeros on dates, and the date delimiter. Set the attributes on the advanced screen of the Import Text Wizard. Terry
Have you set the import specification date format to match the date format of your import file? In the import specification you can set such attributes as date order (mdy, ymd, dmy, etc), 2 or 4 digit years, leading zeros on dates, and the date delimiter. Set the attributes on the advanced screen of the Import Text Wizard. Terry
Terry,
i use Access97.
I have always noticed that while using the Import Spreadsheet Wizard, the Data Type field is always grayed out. So it doesn't let me choose my data types when importing an Excel spreadhsheet.
Where is the advanced screen of the Import Text Wizard?
Ruth ruth.jonkman@wcom.com
i use Access97.
I have always noticed that while using the Import Spreadsheet Wizard, the Data Type field is always grayed out. So it doesn't let me choose my data types when importing an Excel spreadhsheet.
Where is the advanced screen of the Import Text Wizard?
Ruth ruth.jonkman@wcom.com
tlbroadbent
MIS
There is a button on the lower left portion of the screen labeled "Advanced..." Terry
Terry,
are you using Access2000?
are you importing an Excel file?
because using the Import Spreadsheet Wizard with Access97, i only have the buttons: Cancel, Back, Next and Finish at the bottom of the Wizard dialog box.
Ruth ruth.jonkman@wcom.com
are you using Access2000?
are you importing an Excel file?
because using the Import Spreadsheet Wizard with Access97, i only have the buttons: Cancel, Back, Next and Finish at the bottom of the Wizard dialog box.
Ruth ruth.jonkman@wcom.com
tlbroadbent
MIS
The advanced button is available for text file import/export/link. Access can read the file structure of Excel spreadsheets and thus doesn't need instructions about size, type or format of fields. Terry
MichaelRed
Programmer
If you are going to import things into Ms. Access on a regular basis, I have found that everthing needs to be 'validated' before adding to production data. this - ultimately - simplifies life a bit, but makes the front end a bit more interesting.
One simple fact/rule which all data processing should include:
The earlier an error is detected and corrected within the process, the easier / less expensive the correction will be.
In the situation where the information is 'prepared' outside of the database and imported into the database, the data should be imported into a temporary area, validated and only then added to production tables. An obvious and easy step in this process is to import EVERTHING as text, have the validation checks include the checks on non-text fields to be compatible with the data type of the final/destination fields. Often this can be ccomplished bby a set of queries which also be used as the basis for generating the table of 'BadRecords' which are refered to the originating entity for correction/clarification and subsequent addition to the database (after another round of validation).
While adoption of this approach may cause a brief interruption in the immediate processing, it has - at least for me - returned the time invested with HUGE interest in later work.
For me, this has become a standard. I do this as a regular part of all of my data import processes, even the "one-time" processes.
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
One simple fact/rule which all data processing should include:
The earlier an error is detected and corrected within the process, the easier / less expensive the correction will be.
In the situation where the information is 'prepared' outside of the database and imported into the database, the data should be imported into a temporary area, validated and only then added to production tables. An obvious and easy step in this process is to import EVERTHING as text, have the validation checks include the checks on non-text fields to be compatible with the data type of the final/destination fields. Often this can be ccomplished bby a set of queries which also be used as the basis for generating the table of 'BadRecords' which are refered to the originating entity for correction/clarification and subsequent addition to the database (after another round of validation).
While adoption of this approach may cause a brief interruption in the immediate processing, it has - at least for me - returned the time invested with HUGE interest in later work.
For me, this has become a standard. I do this as a regular part of all of my data import processes, even the "one-time" processes.
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
- Thread starter
- #10
th3856 -- The date/time format is YYYYMMDD. I have used SPECS from the ADVANCED button on the import wizard. I have set the date format to YMD. Checked off the box with 4 digit years. Have unchecked and check the leading zeros in dates. And validation? That's a good laugh. All that opens is an expression builder w/ a bunch of functions that I shouldn't need to use for this nonsense. This is excessive programming for a simple item that Access should be able to figure out on it's own. What function do I need to use to convert the TEXT file (not EXCEL) fields so that I can import the date fields.
(NOTE: Foxpro has no problem w/this sort of thing, which I would gladly use instead, except that I've built this massive interface in Access, and don't want to abandon).
(NOTE: Foxpro has no problem w/this sort of thing, which I would gladly use instead, except that I've built this massive interface in Access, and don't want to abandon).
tlbroadbent
MIS
In the advanced settings there is one more box to set. Make sure you delete the date delimiter value. If your date format is yyymmdd then you don't want a delimiter.
And I wouldn't worry about validation if you know the source of your data. Access will validate and kick out invalid records when importing.
Importing is actually a very simple and easy process in Access. I think maybe you got off to a poor start and have become frustrated. Hopefully, as you become acquainted with the procedures and read about the functions you'll become more comfortable with it.
Good luck. Terry
And I wouldn't worry about validation if you know the source of your data. Access will validate and kick out invalid records when importing.
Importing is actually a very simple and easy process in Access. I think maybe you got off to a poor start and have become frustrated. Hopefully, as you become acquainted with the procedures and read about the functions you'll become more comfortable with it.
Good luck. Terry
- Thread starter
- #12
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question