none
date field format in excel export to mssql

    Question

  • The plan is to export data now in an excel spreadsheet, to then build/create/bulk load and create a database of this data.

    A few of the fields in the excel spreadsheet contain dates. A few of the fields in the excel spreadsheet are empty ( like blank ). Many of the fields contain 'text' of varying lengths.

    At this time, the principle question is about the datefields defined in the excel spreadsheet and exporting these dates into the mssql database. Once these dates have been 'moved into' the database, there will be 'some analysis' performed with date, range of dates, etc., etc..

    So, the question remains 'what format' to define the dates in the excel spreadsheet so as to properly export the date into the database?

    Thursday, February 16, 2012 4:57 PM

Answers

  • It sounds like you're talking about a one-time load of a database.  If so, the initial tool you should look at is the Import/Export Wizard in SQL Server.  This is a "front end" for SQL Server Integration Services that does some of the fiddly work for you, at the expense of getting your fingers into the machinery to make it work in a specific way and/or faster/better/different.  Try it out - it's a very low time commitment to try on a few tables.  If it seems to work - well or mostly well - then you can take what it generates and alter/improve it.  That's because the Wizard generates an SSIS package, which you can then edit in BIDS.

    If you're talking about something else - the part of your description that's a "square peg" is what sounds like the dynamic nature of your process.  SSIS itself will not read the contents of the file, figure out the columns and data types, and load it.  SSIS needs "static" (unchanging) metadata - so you have to create a package that "loads table A", and feed it the same format of data every time.

    The Wizard does the job of reading the contents of the file, figuring out the columns and data types (with your help), then builds that SSIS package which it then runs.


    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by Eileen Zhao Tuesday, February 21, 2012 7:02 AM
    • Marked as answer by Eileen Zhao Friday, February 24, 2012 8:38 AM
    Monday, February 20, 2012 10:37 PM

All replies

  • It doesn't matter with SSIS.  When you construct your package, SSIS will use the Excel Provider to read the spreadsheet, and it will read through your data to "guess" what data type it is.  So as long as your first eight rows (by default) have representative data in them (dates in date columns, strings in string columns, empty cells are allowed) then you should be mostly OK.  If you have mixed data types or other weirdness, then you will have some hoops to jump through to get the Excel Provider to see the data the way you think it should.

    If you're using bulk load, then you should use text/CSV.  (Heck, you should use CSVs with SSIS too!  Much more robust.  This advice applies to SSIS loads from CSVs as well.)  Dates should be saved to text as strings in the yyyy-MM-dd SQL/ISO standard format.  DateTimes should be saved as yyyy-MM-ddThh:mm:ss.sss format.


    Todd McDermid's Blog Talk to me now on

    Thursday, February 16, 2012 5:05 PM
  • Thanks much.

    This is 'the first' streight answer to my question. Have searched around the Forums and have not found a simple, streight answer to moving data from one source to a database.

    Thursday, February 16, 2012 10:19 PM
  • Not yet ready/prepaired 'to make' the data move.

    There are a few details I wish to know before the data is moved into the database.

    The 'first top, two' rows of the Excel spreadsheet contain text data definind the contend in each column.

    The first 'column' of the spreadsheed contain 'numeric values' numbering 1 to n with n being that number of rows in the spreadsheet.

    The objective is to build a table where the first two, top rows of the spreadsheet are used to define the data in the table while rows 3 to n in the spreadsheet then become the data in the database.

    Then of course, another important question is about the 'recieving database'? Is it, will it be necessary to do aby 'prep work' to the recieving database or will everything get worked out with the transfer, Excel spreadsheet to database.

     

    Saturday, February 18, 2012 9:42 PM
  • It sounds like you're talking about a one-time load of a database.  If so, the initial tool you should look at is the Import/Export Wizard in SQL Server.  This is a "front end" for SQL Server Integration Services that does some of the fiddly work for you, at the expense of getting your fingers into the machinery to make it work in a specific way and/or faster/better/different.  Try it out - it's a very low time commitment to try on a few tables.  If it seems to work - well or mostly well - then you can take what it generates and alter/improve it.  That's because the Wizard generates an SSIS package, which you can then edit in BIDS.

    If you're talking about something else - the part of your description that's a "square peg" is what sounds like the dynamic nature of your process.  SSIS itself will not read the contents of the file, figure out the columns and data types, and load it.  SSIS needs "static" (unchanging) metadata - so you have to create a package that "loads table A", and feed it the same format of data every time.

    The Wizard does the job of reading the contents of the file, figuring out the columns and data types (with your help), then builds that SSIS package which it then runs.


    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by Eileen Zhao Tuesday, February 21, 2012 7:02 AM
    • Marked as answer by Eileen Zhao Friday, February 24, 2012 8:38 AM
    Monday, February 20, 2012 10:37 PM