Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import Excel binary files into SQL Db using SSIS


Import Excel binary files into SQL Db using SSIS

Author
Message
mcr132
mcr132
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 99
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
Dirt McStain
Dirt McStain
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 138
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
riz.adil
riz.adil
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 43
Silly question but.. how do you make the connection manager point to any generic excel file?
herladygeekedness
herladygeekedness
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 813
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.
Dirt McStain
Dirt McStain
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 138
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
riz.adil
riz.adil
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 43
Aaaaah .. quite subtle... thanks everyone Smile
Make an Impact
Make an Impact
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 363
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?
Make an Impact
Make an Impact
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 363
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
Dirt McStain
Dirt McStain
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 138
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
Make an Impact
Make an Impact
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 363
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search