August 21, 2006 at 9:46 am
Mornin All -
I'm having some trouble and need advice. I've got an Excel file that I'm trying to import into a table. While the field in excel is "Time Format" I have not had any luck at converting it to Text. I need a conversion that will take the format 5:30 AM (Excel Time) to 0530 (ms sql varchar or text)
The excel file looks like this:
| Location | Company | Row_Date | Hour_N | calls Offer | calls handled |
| Tj | Comp | 07/10/06 | 5:30 AM | 1 | 1 |
| Tj | Comp | 07/10/06 | 6:00 AM | 200 | 196 |
The Hour_N field is a time field in Excel. I want it to be a text field in my Table. so instead of coming as 5:30 AM in my sql table I want it to be 0530 as text . At this point it I don't care if I make the conversion in excel or in the DTS Package. I'm just running out time and hoping somebody has had run across this situation. Thanks for your help!
August 21, 2006 at 10:35 am
I assume you're using a data transformation task to import the contents of the Excel file.
Use a DateTime String transformation. Open your transformation task and go to the Transformations tab. Select the line connecting the time field in Excel to the field in your destination table and delete it. Drag & drop the time field name from Source to Destination and select "DateTime String" in the Create New Transformation dialog that appears. When the Transformation Options window opens, click the Propeties button. In the Source date format box, enter hh:mm tt. In the Destination date format box, enter HHmm.
August 21, 2006 at 12:08 pm
Erik,
Great! Worked Perfectly
- Much appreciated, Rick
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply