Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Import Excel binary files into SQL Db using SSIS Expand / Collapse
Author
Message
Posted Tuesday, May 4, 2010 5:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:13 PM
Points: 1, Visits: 95
Hello,
I have a several .xlsb (MS Excel binary) files with data that I need to import into a SQL db using SSIS.
The Data Import wizard / SSIS data flow task is not letting me use the .xlsb files. Is there any way this can be done?
I am using Excel 2010 /2007 files and SQL 2008

Thanks,
mcr132
Post #915746
Posted Friday, June 4, 2010 2:04 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
To connect to an .xlsb (or .xlsm) file, you must first set your Excel Connection Manager to point to any generic .xlsx file on your computer (create it if you have to), then go to the ExcelFilePath in the Properties window of the Excel Connection Manager and manually change the path to the location of the .xlsb file.

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #933050
Posted Tuesday, March 13, 2012 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:23 AM
Points: 3, Visits: 34
Silly question but.. how do you make the connection manager point to any generic excel file?
Post #1266115
Posted Tuesday, March 13, 2012 11:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 300, Visits: 810
Just select it like a normal one. to be clear, the idea is to select an existing file to start with, then go change to the one you really want. I think "random" threw you off.
Post #1266146
Posted Wednesday, March 14, 2012 3:23 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
That's exactly right. The connection manager file selection window won't let you complete the action unless you select a valid Excel file. This can be any Excel xlsx file. Once it accepts this, you're free to edit the file path in the Propeties window to the file that you actually need. Excel Sources or Destinations in you Data Flow still recognize xlsm and xlsb formats even though the connection manager won't let you select them. It's a pretty silly and pointless restriction on Microsoft's part, especially when it's so easy to bypass it.

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #1267116
Posted Thursday, March 15, 2012 3:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:23 AM
Points: 3, Visits: 34
Aaaaah .. quite subtle... thanks everyone :)
Post #1267905
Posted Thursday, May 23, 2013 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 12:35 PM
Points: 7, Visits: 246
I tried same thing to load xlsb file, but when i open in excel source I can't see any data. Can you help me out plz?
Post #1456046
Posted Thursday, May 23, 2013 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 12:35 PM
Points: 7, Visits: 246
I am downloading xlsb file from web to a databse folder using script task. I have to use that xlsb file to populate one of our table on daily refresh and load. I have tried your technique, but no luck. I don't see any sheets or data in the excel source. I have downloaded xlsb file as xlsx and I can not able to open and use it. Right now I am downloading xlsb file as xls and then open and saving the file as xlsx.When I save the xls file as xlsx the file size dramtically 3 times increased (in my case 20 MB to 66 MB). Using OLEDB Access database connection connecting to the xlsx file and populating the table. This is cumbersome. Can you suggest me a handy one for this task? Database: SQL server 2005 OS: Microsoft windows 2008, Service Pack 2, 64 bit. ETL Tool: SSIS 2005
Post #1456050
Posted Monday, May 27, 2013 5:23 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
I'm not sure you can do this with XLSB. XLSX and XLSM are basically the same - XML-based files. XLSB is a binary format, which is closer to the old XLS format than anything else. Simply changing XLSB to XLSX will not work. You could try changing it to XLS and try to connect to the file that way but, I suspect this won't work either. I'm afraid that you'll probably need to use a script task to programatically open the file in Excel and save it in a different format.

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #1457158
Posted Tuesday, May 28, 2013 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 12:35 PM
Points: 7, Visits: 246
Thanks Dirt. There is no MS office istalled on prod Serever. Can I still use the script task? Can you please suggest me a reference for the script task to open excel file and save it in different format.
Post #1457424
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse