Read all data as text from SSIS Excel data source

Question

Hi everyone.

I am using an Excel data source in SSIS with the connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyFolder\MyData.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=NO;IMEX=1";

In column L I have the values: 16,100 for row 11

17,310 for row 23

16,100 for row 30

All the other columns are NULL. When I preview the data in the Excel connection manager, it shows data as:

16100

17310

16110

it means that the driver removes the comma (,), I do not want it to do that. All I want is to treat all the data as text even if the values are numeric. I have ImportMixedTypes=Text in all keys in Windows registry.

I tried IMEX=1 in Excel connection string, TypeGuessRows = 0 and ImportMixedTypes=Text in Windows registry.

All I want is to treat all the data as text even if the values are numeric.

Regards,

Answer 1

Hi @Geraldo Peralta,

As far as I know, you could not do that via Excel.

You could copy the data to txt files and then use flat file source.

In that way, you could keep the comma in your data.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @Geraldo Peralta,

MS Excel doesn't have notion of the data types. So, it has two things: raw data, and how to present it.

In your case, raw data is 16100, Excel presentation settings show it as 16,100, with a comma

Back to SQL Server and SSIS.

When you run it, it correctly brings raw data from MS Excel, it has nothing to do with the MS Excel presentation settings, it has no access to them.

I hope it makes a little bit more clear what is going on.