Import csv file into database
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
I'm afraid I'd have to see the csv file AND the database to know what's going on. Sorry!
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
I just stripped down the database to upload, and tested the code and of course it works! I'm going to try to figure out what is keeping this field from coming in on the page that its being imported from before wasting your time.
Thanks!
Thanks!
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
Hi Hans,
This one will be the death of me. Here is the stripped down version. I'm using a txt file instead of cvs file and I changed the import specs. I'm still running into issues with the date field. Sometimes it works and most times it imports date_ds as blank with import errors. I don't know where to look.
Thanks!
Leesha
This one will be the death of me. Here is the stripped down version. I'm using a txt file instead of cvs file and I changed the import specs. I'm still running into issues with the date field. Sometimes it works and most times it imports date_ds as blank with import errors. I don't know where to look.
Thanks!
Leesha
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
The text file contains dates in yyyy/mm/dd format, but the import specification that you use has US date format MDY - see screenshot.
If you change MDY to YMD, the import will run without errors.
If you change MDY to YMD, the import will run without errors.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
Hi Hans,
You can't imagine how many times I tried to get this to work and couldn't "see" the format issue. Well I'll never forget this one! Once the info is imported, how do I go about changing the format to mdy as that is how the user will want to visualize it.
Thanks!
Leesha
You can't imagine how many times I tried to get this to work and couldn't "see" the format issue. Well I'll never forget this one! Once the info is imported, how do I go about changing the format to mdy as that is how the user will want to visualize it.
Thanks!
Leesha
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
You can set the Format property of the DATE_DS field in the table any way you want.
The Format property of a date/time field is just the way it is displayed, the underlying value is independent of the format. So whether you display a date as 11/18/13 or as November 18, 2013 or as 18-Nov-2013 does not matter. Regardless of the format, Access stores this date as the number 41596 (the number of days since December 30, 1899).
But a date in a text file is just a text string - there is no underlying value. So in a text file, the format does matter, and you have to tell Access how to interpret the text strings when you import them.
The Format property of a date/time field is just the way it is displayed, the underlying value is independent of the format. So whether you display a date as 11/18/13 or as November 18, 2013 or as 18-Nov-2013 does not matter. Regardless of the format, Access stores this date as the number 41596 (the number of days since December 30, 1899).
But a date in a text file is just a text string - there is no underlying value. So in a text file, the format does matter, and you have to tell Access how to interpret the text strings when you import them.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
Awesome! Thanks!