Using Excel to generate SQL - problem handling date field

Question

I have an excel file filled with data my customer wants placed into a database. I've generated a column along the left of the data that looks like this (obviously changed the field names for here):

="INSERT INTO TBL_CUSTOMER
                      (FIELD, FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, CUSTOMER_DATE_REGISTERED)
VALUES     ('"&D2&"','"&E2&"','"&F2&"','"&G2&"','"&H2&"','"&I2&"','"&J2&"','"&K2&"','"&L2&"','"&M2&"','"&N2&"','"&O2&"','"&P2&"','"&Q2&"','"&R2&"','"&S2&"','"&T2&"','"&U2&"','"&V2&"','"&W2&"','"&X2&"','"&Y2&"','"&Z2&"','"&AA2&"','"&AB2&"','"&AC2&"')"

Everything works perfectly but the problem is that the last item (Cell AC2) is a date in the format 25/12/2010 08:45:45. Excel is reading this as 40537.3651041667.

Any idea how to make it appear correctly?

Hope you guys can help!

Edit: Nomatted what i set the cell format to for AC2 I still get that random number pulled into the query

Rich Andrews · Accepted Answer · 2011-08-24 20:35:02Z

14

Try the TEXT() function...

TEXT(AC2, "dd/mm/yyy hh:mm:ss")

This returns a string from a value in the specified format

="INSERT INTO TBL_CUSTOMER
                      (FIELD, FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, CUSTOMER_DATE_REGISTERED)
VALUES 
    ('"&D2&"','"&E2&"','"&F2&"','"&G2&"','"&H2&"','"&I2&"','"&J2&"','"&K2&"','"&L2&"','"&M2&"','"&N2&"','"&O2&"','"&P2&"','"&Q2&"','"&R2&"','"&S2&"','"&T2&"','"&U2&"','"&V2&"','"&W2&"','"&X2&"','"&Y2&"','"&Z2&"','"&AA2&"','"&AB2&"',
'"&TEXT(AC2, "dd/mm/yyy hh:mm:ss")&"')"

edited Aug 24, 2011 at 20:35

answered Jan 18, 2011 at 11:43

Rich Andrews

4,1883 gold badges38 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gudwlk Over a year ago

Grate solution. Reduced 70% of manual work. Same can be applied to Time columns as well,

RichardTheKiwi · Accepted Answer · 2011-01-18 11:46:27Z

0

Use the TEXT function with a region-agnostic format

TEXT(AC2, "yyyymmdd hh:mm:ss")

or

TEXT(AC2, "yyyy-mm-ddThh:mm:ss")

answered Jan 18, 2011 at 11:46

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Comments

Manuel Phetla · Accepted Answer · 2019-01-14 09:30:01Z

0

Struggled with this for a while. Use semicolon instead of coma.

(Office16)

TEXT(AC2; "dd/mm/yyy hh:mm:ss")

Instead of

TEXT(AC2, "dd/mm/yyy hh:mm:ss")

answered Jan 14, 2019 at 9:30

Manuel Phetla

111 silver badge3 bronze badges

Collectives™ on Stack Overflow

Using Excel to generate SQL - problem handling date field

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related