TRansferText

[Andy]

Using MS Access 2003, I am importing a csv file, using File->Import. I
set the TIME_HM field to be text.

This field has a ":" as part of the data.

For example, I might have:

0000:28
10:12:00
1092:15:00

In the above list, the first two are read and stored as they appear
above. The third item is not. It is truncated to "1092:15". The Time
delimeter is set to ":". I cannot change the input stream.

How do I prevent the truncation?


Thanks,
Andy

 

[Clifford Bass]

Hi Andy,

What is the size of the TIME_HM field? If less than 10, make it at
least 10.

Clifford Bass

 

[Andy]

Field size is 50.

Hi Andy,

What is the size of the TIME_HM field? If less than 10, make it at
least 10.

Clifford Bass

:

 

[Andy]

I mis-stated the problem example.

Source data Transferred As
0000:12 0000:12
01:15 01:15
12:09:05 12:09
1092:15:00 1092:15

Date from the second ":" to the end is truncated.

 

[Clifford Bass]

Hi Andy,

It imports fine for me into both Access 2003 and Access 2007. Both
into a new table, with the field being set to text and into an existing table
with the field predefined as text, 50 characters. I am not sure why you
originally said the time delimiter is set to : since that is not relevant
with text fields. Are you using an import specification? Are you importing
into a new table or an existing table? Is it being imported into an Access
table or a linked table in some other type of database?

Clifford Bass

 

[Andy]

Hi Clifford,

I've been using Access for 15 years and have never run into this before.
I must be doing something wrong that's really simple...just haven't
found it yet!

I am not using a specification. I tried importing to an existing table
and a new table (not a linked table). There are other fields being
imported, as datetime fields that are in the General Date format, that
read in w/out any problems.

Thanks for your help.

Andy

 

[Clifford Bass]

Hi Andy,

Curious. It makes me wonder if there is something odd about the
incoming data. Maybe an invisible character What happens if you just link
to the CSV file and view the data in table view? Does it show as whole or
truncated? Is the data split over two columns?

Clifford Bass

 

[Andy]

Clifford,

I loaded the source file in excel and made some discoveries.

The TIME_HM column's cells were formatted as "General". However, the
cell that had "1092:15:00", had a custom format of "[h]:mm:ss", which is
elapsed hours, minutes, and seconds. The elapsed hours format allows
values greater than 23. When I clicked on the cell, the display area
above the header line displayed the cell contents as "02/14/1900 12:15:00".

I suspect that is the problem.


Andy

 

[Clifford Bass]

Hi Andy,

Maybe. Maybe not. Dates/Times are stored as real numbers centered
around 12/30/1899; with that being the zero point. Date are the integer
portion and times are the fractional portion. So that date/time makes sense.
What happens with the 12:09:05 value when you do the same thing?

Double-checking: the source of the data is a text, comma-delimited file?

Clifford Bass