Results 1 to 5 of 5

Thread: Excel to MS SQL

  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Question Unanswered: Excel to MS SQL

    Hai all,
    I want to export the data from Excel sheet to SQL database. In the Excel sheet one column contains the date value and non date like 0 and blank. I want to transfer this to my table by changing the format to dd/mm/yyyy.

    If I open and format the column to date, and if I export then in the table i am getting different formats ( based on the client machines Date format , The column in the table is Varchar becaust the Excel sheet column will contain date and other non date like 0 , blank etc )

    Now how can I export the Excel column that contains date , 0 and blanks to the table in the database with date format dd/mm/yyyy and null for non date values?

    Thanks
    Narayanaswamy

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Excel to MS SQL

    You should import your data as VARCHAR in a temporary table, and transform it in a second step.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    4

    Thumbs down

    Hai ,
    I tried that also, but the problem is
    If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
    If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

    But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)


    Can you help me in solving this problem?

    Thanks

    Narayanaswamy

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Yeah, Excel is great, but not for storing data.

    I would take another approach, and would include some VBA code into your workbooks to transform your sheets first into the proper format before exporting them. Your could also consider to export them by VBA.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Narayanaswamy
    Hai ,
    I tried that also, but the problem is
    If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
    If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

    But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)


    Can you help me in solving this problem?

    Thanks

    Narayanaswamy
    I did have the same problem with importing excel data to MSSQL.
    After some fighting I just imported data to Access and then to MSSQL.
    It was needed to do not often.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •