StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIdentifying a record by filename after importing data from Excel into a SQL DB table
text
Body
copied!<p>I'm still fairly new to writing SQL scripts. I have a script which imports numerous excel files (possibly reaching 1000+). Some of these Excel spreadsheets have only one row and some have more than 50 rows. I'm importing all of these excel spreadsheets into one table and would like to be able to add a column which identifies which Excel spreadsheet, by filename, the record came from. So rows 1 through 10 may come from ExcelSpreadsheetA.xlsx and rows 11 through 15 may have come from ExcelSpreadsheetB.xlsx. Would I be able to set this identification up during the import process? </p> <p>I use this script to perform my import from excel:</p> <pre><code>select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') </code></pre> <p>What I would like to do is add logic to the end of that script that says something like:</p> <pre><code>update sqlservertable set filename = testing.xls </code></pre> <p>But I want it to set the filename attribute for just the records that had been imported from that specific filename. Ideally I would like the final table to look similar to the one below.</p> <pre><code>Col1 Col2 Col3 Filename ===== ===== ===== ========= 1 A B export1.xlsx 2 C D export1.xlsx 3 E F export1.xlsx 4 G H export5.xlsx 5 I J export8.xlsx 6 K L export8.xlsx </code></pre> <p>@Pondlife This script does what I want it to do.</p> <pre><code>select *, 'file1.xls' as 'Filename' into dbo.SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') </code></pre> <p>However, now I need to be able to get a subsequent import into that same database, here is the script I'm using but it's erroring out on me.</p> <pre><code>INSERT INTO dbo.SQLServerTable Select *, 'File2.xlsx' as 'FileName' FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\Convert\Converted\File2.xlsx;HDR=YES', 'SELECT * FROM [Worksheet$]') </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload