Openrowset command & removing unwanted characters

Transact-SQL
1

Folks I'm wondering can you help me here. I have inherited a system that populates a table from Excel using the OPENROWSET command. The table in question is called: tbl_raw_data and then the loaded data is moved on elsewhere...
The excel speasheet can and does change column names and number of rows over time.

The code is something like this:

SET @varSQL = 'SELECT * INTO TBL_RAW_DATA FROM OPENROWSET('''+@driver+''',' + @varConnectionString + ', ''SELECT * FROM ' + @varSheetName + ''')'
EXEC (@varSQL)

I'm new to openrowset command and am wondering is there a "nice" way of altering the command above to parse out carriage returns or line-feed characters ( char(13) & char(10) respectively) as their presence messing things up later on. Lets assume they cannot be removed from the Excel before load.

I'm open to suggestion here but ideally looking for something quick & easy. I know I can probably parse thru the table after population and remove the offending characters & re-write but that could be time consuming....hence looking for nice alternatives :slight_smile:

Thanks in advance for any suggestions...
J.

2

Your options are:
1.) Do it in Excel before import (perhaps via Powershell using the ImportExcel library).
2.) Do it during the OPENROWSET() using REPLACE on every column.
3.) Do it after the import, also using REPLACE on every column.

3

Thank you SqlHippo for the suggestions. Option 1 and 2 are unlikely as for 1). may differing users have differing locations for the excel spreadsheets and for 2). as implied the column names are essentially unknown before hand. I think therefore option 3). is the best way forward and I'm looking into that and making progress. Yes, it will add a little latency to the load but the benefit should outweigh the expense.
Thanks again,
J.

4

If you know how to create dynamic SQL, you can load the data, then pull the column names from INFORMATION_SCHEMA.COLUMNS to generate the REPLACE statements for char/varchar columns.