Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

Importing an excel file with a problematic date column

Hi,
I have an excel file (attached ) with one column.
the problem is that this column have defferent formatting for a date.
this what i`m getting from external apps as export.

now i`d like to import this file into access.
but when i do that. it doesn`t recognize the column as a date , but as i text.which i don`t want to.
that`s because of the problem with the format.
how can i import the column as a date (fixing the problem through the importing process in access) , if one may help out , maybe using a vba code . or somethen:}
thnx in regard :}
DateProblem.xlsx
0
drtopserv
Asked:
drtopserv
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
import the excel file to a temp table.
use a query to format the dates imported to the temp table and append the result to the destination table.

what date format do you have in your destination table?
0
 
drtopservAuthor Commented:
dd/mm/yyyy
but how can i use a query to format it?
may a sample?
0
 
Rey Obrero (Capricorn1)Commented:
select cdate(format([datefield],"dd/mm/yyyy"))
from temptable
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
drtopservAuthor Commented:
but did you see the column in the excel file, it has 2 formats togother in same column
0
 
Rey Obrero (Capricorn1)Commented:
yes i did.. try my suggestion so you can see the result after running the query.

you can then convert the query into an append query.



what is your regional setting?

try this query and see which is more likely to return the correct value

SELECT Sheet2.Started, CDate(Format([Started],"dd/mm/yyyy")) AS Expr1, DateValue([Started]) AS Expr2
FROM Sheet2;
0
 
drtopservAuthor Commented:
I LOVE YOU man!! heheh
it works!!! thnx alot..
plz may u have alook at my open Q:ID: 28158731
0
 
drtopservAuthor Commented:
btw, in excel i couldn`t use :CDate or DateValue (didn`t work for me:} )
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now