UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Criteria For Importing Mis-formatted Data, Access 2010    
 
   
bakersburg9
post Mar 6 2018, 04:56 PM
Post#1



Posts: 5,141
Joined: 2-November 04
From: Downey, CA


This isn't really a "how-to" question so much - I have cases where I have data that I'm importing from Excel where 2 non-consecutive columns that I'm appending to a table in access with date formatting for these fields, and it will import the data which has numbers instead of dates as a date in the first column, then won't import anything for the 2nd column - the funny thing is, there's no difference between the two columns - both formatted as number on the Excel side, and both columns formatted as DATE on the Access side - so Access basically gets to the first column and converts the numbers to dates, and imports nothing from the second column - any idea why this happens ? Just curious.... it causes an import error . . .
Go to the top of the page
 
haresfur
post Mar 6 2018, 06:07 PM
Post#2



Posts: 265
Joined: 4-April 12
From: Bendigo, Australia


Maybe show us an example of the spreadsheet. Sometimes the import seems to get confused if there is text or null in the first spreadsheet row.

--------------------
-- Evan
Go to the top of the page
 
tina t
post Mar 6 2018, 06:19 PM
Post#3



Posts: 5,412
Joined: 11-November 10
From: SoCal, USA


QUOTE
...columns that I'm appending to a table in access with date formatting for these fields, and it will import the data which has numbers instead of dates as a date in the first column, then won't import anything for the 2nd column...

hello Steve, i'm a little concerned with your use of the word formatting here, because the date format in an Access table field is not really relevant. as long as the data type of the field is Date/Time, Access will handle the values as dates with whatever formatting you choose to apply in the user interface. the value actually stored in an Access Date/Time field is a Double, but handled differently by Access than a Double value stored as such in a field with Number data type.

Access may be able to figure out what you need, and give you the date values you're expecting to see when you import the first column - even though it's numbers - but you'll want to double check carefully to be sure. as for the second column in the worksheet, have you tried changing the data type of that column to Date/Time in Excel, before you import into Access?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 03:38 AM