rickpaulos
New Member
- Joined
- Feb 3, 2012
- Messages
- 6
Input data is a text csv file from a third party vendor (where individuals enter their own data 
).
The text file is comma separated but only text fields with spaces are in double quotes.
2 date fields are in the yyyy-mm-dd format. (update_date, dob)
1 date field is "yyyy-mm-dd hh:mm:ss" (timestamp, mostly blank)
No 3rd party vendor formating options are available for exporting the csv file.
I am trying to import into an Access database where all fields in my table are text (except the autonumber) using the default wizard settings and it reports "Type Conversion Failure" on one date field (dob) but not another date field (update_date).
My guess is the 3rd date/time field (timestamp) is imported okay because it's in double quotes due to the space between the date & time parts.
I used the Advanced settings in the import wizard to change the date order to ymd, date delimiter to -, leading zeros in dates, 4 digit years, and double quote as text delimiter, which all appear to get applied to every field in the table, to get it to import. The dates are going into text fields so why is MS even checking? Text into text and it still checks for valid date formats?
All fields in my existing Access table are defined as text x 255 columns to cover what ever junk the end users typed in via the 3rd party software. I'd rather deal with the junk after it's imported than have to go back and edit the csv file.
I did check the regional settings on my windows 7 computer (mdy) but the last thing I want is the personal settings on someone's computer (my own included) to determine if this one program works or not. If I change the regional settings, it will surely break some other project.
Some of the other non-date fields have dates entered by the users. Another reason to shut off MS validity checks during the import to text.
Is there any way to turn off the checking? Yeah, yeah, bill gates crew knows how to use my data better than I do so I'm just wrong to question them. grrr.
Is there a practical way to import text into access tables without using the wizard?
rant off.
rick
).The text file is comma separated but only text fields with spaces are in double quotes.
2 date fields are in the yyyy-mm-dd format. (update_date, dob)
1 date field is "yyyy-mm-dd hh:mm:ss" (timestamp, mostly blank)
No 3rd party vendor formating options are available for exporting the csv file.
I am trying to import into an Access database where all fields in my table are text (except the autonumber) using the default wizard settings and it reports "Type Conversion Failure" on one date field (dob) but not another date field (update_date).
My guess is the 3rd date/time field (timestamp) is imported okay because it's in double quotes due to the space between the date & time parts.
I used the Advanced settings in the import wizard to change the date order to ymd, date delimiter to -, leading zeros in dates, 4 digit years, and double quote as text delimiter, which all appear to get applied to every field in the table, to get it to import. The dates are going into text fields so why is MS even checking? Text into text and it still checks for valid date formats?
All fields in my existing Access table are defined as text x 255 columns to cover what ever junk the end users typed in via the 3rd party software. I'd rather deal with the junk after it's imported than have to go back and edit the csv file.
I did check the regional settings on my windows 7 computer (mdy) but the last thing I want is the personal settings on someone's computer (my own included) to determine if this one program works or not. If I change the regional settings, it will surely break some other project.
Some of the other non-date fields have dates entered by the users. Another reason to shut off MS validity checks during the import to text.
Is there any way to turn off the checking? Yeah, yeah, bill gates crew knows how to use my data better than I do so I'm just wrong to question them. grrr.
Is there a practical way to import text into access tables without using the wizard?
rant off.
rick
