Create Table - DateTime format

  • Thread starter Thread starter Van T. Dinh
  • Start date Start date
V

Van T. Dinh

The format for the DateTime Field in the newly created
Table is not important. The ISO format yyyy-mm-dd
hh:mm:ss is fine also.

Describe your "importing" process and post code if you use
code.

HTH
Van T. Dinh
MVP (Access)
 
I am using the Wizard. I am importing a comma delimited file into an existing
empty table. After the import the table's datetime fields are blank. The
dates in the comma delimited are yyyy-mm-dd hh:mm:ss.

Brenda
 
Use A2K2 Import Wizard, I don't have any problems
importing date values of formay yyyy-mm-dd hh:mm:ss from
CSV into Access. My normal DateTime format is entirely
different to the above.

You probably haven't set the Options available in
the "Advanced" button in the first Import Wizard Dialog.
In the Date, Time & Number pane, you need to set:

* Date order to "YMD"
* Date delimiter to "-"
* Time delimiter to ":"
* 4-digit year: checked
* Leading zeros in Dates: checked

Once you set the above options, Access will be able to
recognise the (date/time) text in your CSV file as dates.

HTH
Van T. Dinh
MVP (Access)
 
I don't think you can import CSV with that format into a DateTime Field.
Possibly you need to import that into a Text Field in a temporary Table and
use a Append Query with text manipulation to get rid of the ".ssssssss" part
and into the DateTime of your destination Table.

I am confused what you are trying to do: You initial scenario is you create
Table then use the Import Wizard to import CSV with format "yyyy-mm-dd
hh:mm:ss" to this already-created Table. Now your question is about date
format "yyyy-mm-dd hh:mm:ss.ssssssss" being imported into a new Table????
 
I am just trying to get something to work. So now I have tried many different
things. I have several txt files in comma delimited format with "" around
text fields. I can not get the file to import and obviously my access is
limited. My first attempt to get the data loaded to a table was to create
the table in a query and then import the data. When that did not work then I
tried letting the Import Wizard create the table thinking that would import
the records. Just trying to get the text file imported any way possible.

The timestamp does not have "". Do you think I can still import it as text
and then manipulate it? These files are really large, like 608mb. I know
foxpro well and could write a foxpro program to strip the dates of the
..sssssssss. But I was trying to do it in Access.

So, a different question. Ccan you specify the length of a text field in the
Import to new table? Just curious that I can not find it as an option.

Thanks

Brenda
 
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)


Brenda said:
I am just trying to get something to work. So now I have tried many different
things. I have several txt files in comma delimited format with "" around
text fields. I can not get the file to import and obviously my access is
limited. My first attempt to get the data loaded to a table was to create
the table in a query and then import the data. When that did not work then I
tried letting the Import Wizard create the table thinking that would import
the records. Just trying to get the text file imported any way possible.
Click to expand...

The double-quotes will be a problem. I don't think you can have
double-quotes for some Text Field values and no double-quotes for some other
Text Field values.



The timestamp does not have "". Do you think I can still import it as text
and then manipulate it? These files are really large, like 608mb. I know
foxpro well and could write a foxpro program to strip the dates of the
.sssssssss. But I was trying to do it in Access.
Click to expand...

Try just linking the CSV file to Access rather than importing. Access will
guess the data types and see what it comes up with the date/time text in the
format "yyyy-mm-dd hh:mm:ss.ssssssss". If it comes up with DateTime or /
Text data type, you can then use Create Table Query (for a new Table) or
Append Query (to an existing Table) with String manipulations if require to
create these Records in the final destination Table.


So, a different question. Ccan you specify the length of a text field in the
Import to new table? Just curious that I can not find it as an option.
Click to expand...

I don't think so. OTOH, I always create the Table Structure first before I
import.
 
Back
Top