none
SQL query into an excel file ignores cells with dashes

    Question

  • We have some software that allows us to parse an excel spreadsheet as a database, which then formats the information onto printable labels. In the spreadsheet, there is a column of numbers (each number being between 3 and 6 digits long). Each of those numbers are successfully placed onto the labels except for about 3 of those numbers that happen to have 5 digits, a dash, and then another digit (XXXXX-X). This causes the software to not successfully read those particular cells, consequently, leave the area of the label (where those numbers should be placed) blank. Seeing that the software utilizes SQL to select the information from the spreadsheet, is there a query element that allows us to include data that includes a dash in the cell?

    I hope this makes sense. Let me know if you have any thoughts on this.

    Thank you,

    Joshua

    Monday, July 08, 2013 6:00 PM

Answers

All replies

  • I cleanse the date before doing the parse. you can find a feature in excel 'Text to columns', select the column where you have the extra dash, select Delimited and click - for Other: 

    you can do the rest. dont know if it solves your problem.

    Monday, July 08, 2013 6:10 PM
  • Hi mashdots,

    First, I want to confirm what’s the data type in SQL Server table you use to store the data with dashes. Dash is invalid in numeric data column, but we can store them with a nvarchar column, please modify the column mapping when importing data from Excel to SQL Server. For more detail information, you can refer to the following link:

    Review Data Type Mapping (SQL Server Import and Export Wizard)
    http://msdn.microsoft.com/en-us/library/bb895363.aspx


    Allen Li
    TechNet Community Support

    Wednesday, July 10, 2013 8:07 AM
    Moderator