locked
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?

    Thanks

    Thursday, December 13, 2012 11:52 PM

Answers

All replies