March 3, 2009 at 8:48 am
I'm in the middle of a data migration. We have a SQL Server 2000 install, and I'm moving data from that to Excel spreadsheets and another team is importing the data.
Early in our project, we agreed on the column and data structure that the receiving team is expecting, and have basically been using spreadsheets to transfer the data the whole time. My process essentially is to run a set of queries, and copy-paste those query results into my spreadsheet template.
I've realized I must be a horrible Excel newb - midway through I've discovered that Excel is wrecking half my data with it's formatting. Datetimes are messed up, character strings that are all numbers have leading zeros stripped off and commas placed within, long integers are formatted in scientific notation, etc. I've caught most as I go, but it seems like I keep finding more here and there. Formatting everything to text screws up the dates fields it seems, so it's a tedious job altogether.
I'll take my case up with Microsoft eventually, both on why the friggin' Excel mangles everything up, and why DTS (I'm assuming that's what they're using to import?) keeps the formatting in the first place. Sigh....anyway...
I'm looking for any suggestions on a reliable way to get data from a query into a spreadsheet. I've tried seeing if we could go to generating csv files and copying-pasting text output into a text file, but they aren't going for it. I was thinking something with DTS, but I'm managing my SQL2000 instance using SQL2005 Management Studio, and that doesn't seem to work. Analysis Services? Can that work with SQL2000? Can I at least have the spreadsheet created with the correct file formatting?
Any help would be greatly appreciated!
March 3, 2009 at 8:56 am
For this project i would use SSIS, since you have SQL sever 2005 then you should have a copy of BIDS. This will allow you to create SSIS packages to export from your 2000 database.
Also you mention that they do not want to use CSV files, is there a reason for this? as this would save a lot of headaches involved with Excel processing.
If i were doing this then I would import directly from 2000 --> 2005 without the excel step . You can import into staging tables until the new databases are built.
March 3, 2009 at 9:08 am
steveb (3/3/2009)
Also you mention that they do not want to use CSV files, is there a reason for this? as this would save a lot of headaches involved with Excel processing.If i were doing this then I would import directly from 2000 --> 2005 without the excel step . You can import into staging tables until the new databases are built.
Um, yeah...I'm not sure the group I'm working with is so much on the competent side. We started with Excel files, so we're sticking with them :-/
And yes, doing a cross import from 2000 --> 2005 using SSIS would have been awesome....but again, that's a little beyond the team here. Gahhhh.
I did find BIDS, and am seeing if I can clean things up with that a bit.
Transporting tons of data via spreadsheets (even trying OpenOffice didn't really help) sucks....
March 3, 2009 at 1:36 pm
Alright, I've got a related question then.
How do you convert sql datetime values to Excel values? It looks like Excel only supports DT_DATE, but when I set columns to that it strips off the time from the SQL field.
I've seen mentioned to use the Data Conversion object, and also the Derived Column, but didn't have much luck with either of those. Any tips?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply