How to read excel File as it is displayed using OLEDB RRS feed

  • Question

  • Hello, 

    Is there a  way to read all the columns in the excel file as text?

    I am reading bunch of excel files each one having many sheets. I am using OLEDB to connect to excel files and using Select * from Sheet to get the data from each sheet. The problem is that if the format of all the rows in a column is same ( Ex: column 1 has all values like $123.24 ) when i read the data its only getting 123.24 and not along with the $ sign. But if my column has mixed data types then it will read every thing as text Since i have IMEX = 1 and importmixedtypes = text. 

    Can we get the formatting intact when loading to sql server? If oledb is not a way and only interop is a way then can somebody tell me how to loop through each folder and get all the excel files and read each sheet in each file and prepare a data table for all the files. 

    My aim is to create one data table for all the excel files and use that to play around in the database. 

    I know i can post my code here but my code works perfectly fine. I need to know how i can preserve the formatting. 

    I know in VBA we have something like .value and .value2. But with OLEDB connection i am reading the whole excel sheet at a time. So i need to do something before i read or after to get displayed value rather than stored value i read is the question. 

    Also like importmixedtypes = text do we have any option to provide in the connection string to read all the columns as text no matter what the datatype is?


    Thursday, December 13, 2012 11:52 PM


All replies