0

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?

I use this script to perform my import from excel:

select * 
into SQLServerTable 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;HDR=YES', 
'SELECT * FROM [Sheet1$]')

What I would like to do is add logic to the end of that script that says something like:

update sqlservertable
set filename = testing.xls

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.

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

@Pondlife This script does what I want it to do.

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$]')

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.

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$]')
Geoff Dawdy
  • 886
  • 6
  • 15
  • 42

2 Answers2

1

Assuming that your SELECT query already works, then just add the filename to the end of your column list:

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$]')

And as a standard comment, don't use SELECT * in your code.

Community
  • 1
  • 1
Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • that worked for the initial import which creates the table. What about for all the subsequent scripts which do all the appending to the table like the one below? 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$]') – Geoff Dawdy Dec 18 '12 at 00:31
0

Yes, you'd need to set up a files table with a foreign key in your existing table, though, as follows:

tbl1 files table
id | path | description

tbl2 data table
id | fileId | col1value | col2value ... | colnvalue

Hope this is clearer.

hd1
  • 33,938
  • 5
  • 80
  • 91
  • Can you provide more details on setting this up? Let's say I have a files table which lists all of the filenames that may possibly be imported into the table. What would my next step be to marry the records to their respective filenames? – Geoff Dawdy Dec 17 '12 at 04:49
  • I've edited my original question to make it more clear that I am wanting the import process to mark each row of the table with which file the record came from. I'm still not clear on how your answer would be able to specify the file for each record that is imported. – Geoff Dawdy Dec 17 '12 at 15:45
  • Each record has a file_id, mapping to files.id. In the files table, there's a column for the filename. Ergo, a join on file_id and files.id will yield the filename the record was derived from. – hd1 Dec 17 '12 at 20:51