March 21, 2011 at 6:15 am
Hi all,
I'm dealing with a client who recieves an email subscription via SQL Server (2008) Reporting Services, giving him a daily Excel spreadsheet. The four columns should be of data type integer, integer, date, integer - the SQL code used in the report is correctly generating this output. However, when he subsequently attempts to import his daily spreadsheet into an Access DB for his own use, his tables are creating with types number (but in double format, which he is having to manually change to "long integer") and the date column is coming through as text. Something's clearly being lost in translation, but I'm unsure if it's the export from SRS to Excel, or the import from Excel to Access, or both.
Any advice on the text box formatting in SRS to help me get to the end result of long integer and date in Access would be very much appreciated. TIA.
March 21, 2011 at 7:58 am
It's most likely the difference in data formats and data sampling. For example the standard data format in SQL would not be recognized as a date in Excel and then Access would pickup the fact that the cell is stored as text and import the same way. If this is a requirment then you would have to format the data in the report to ensure that translation occurs correctly. That means formatting the date as short form of simply "03/21/11" Even then though you may get some less than expected results. You willl find that SQL and Excel even though they are both Microsoft products tend to not play very well together.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 22, 2011 at 3:43 am
Thanks for that, Dan. I've had another quick chat with the client, and what he's doing is as follows: taking the excel sheet generated in SSRS, running a macro to import the sheet into a new table in his Access DB, and then running an update query to overwrite the old data in his DB with the new data. I'm now wondering if, given what you said about the packages not playing nicely together, the answer might be to try to convert the data types within Access itself, and not do it externally. Does that make sense?
March 22, 2011 at 7:36 am
If the access is an business necessity it might be best to setup a small database and simply store the data from the report. Then using an ODBC he could simply link to the SQL table from access with the format needed. he could also use an ODBC in excel to link directly to the data. There are alot of overall options but my recommendation would always be that if anything he is doing is even slightly mission critical that the data not be stored in access. then can use access as the front end if the are comfortable with it but link to SQL and grab the data from the source.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 22, 2011 at 8:13 am
I'd be much happier if that were the case, too, but sadly the client is external to the company and can't be given direct access to the SQL data.
March 23, 2011 at 6:35 am
Here are a couple of ideas:
1. Instead of importing the data, create a linked table from Access to Excel.
2. Import the data into a staging table that has all the columns as text fields, and then use a query to convert the data types into the correct ones.
3. Save the data as a CSV instead of XLS, and create an import specification that assigns the proper data types.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply