Solved! Excel extraction (pattern)

[nicoputter97]

Hi Guys,

I have a large number of excel file that I want to take and import into SQL...

Now normally I would just do a simple Bulk Insert but the sheets are not all the same... But they do have a pattern to them. The pattern is that the information is in a 10 column wide grid then a empty column then again the 10 column grid with the info I want to export into my table... Now I don't know if I am over thinking it and there is a simple way to do this looking me in the face and I just can't see it...

As I said I have a large amount of sheets and I do not want to do this manual extract per sheet...

I have made a simple .xlsx to .csv converter, that all the data I work with can be easily imported and I can just use FIELDTERMINATOR = ',' to get the information from the files. Now I do not want all the information on this sheet only the information in this 10 column wide grid, but like I said I think I am overthinking it and there is a simple way to this but I just can't see it.

Sorry if this a bit messy still new to this platform...

 

[Ralston18]

With respect to any given sheet:

The objective being to import only the second 10 column wide grid into SQL?

Or the first 10 column gird at the start and the second 10 column grid appended thereafter?

Or all 10 column wide grids from all sheets into one target table?

Likely to be a number of ways to do so. E.g.:

https/support.office.com/en-us/article/consolidate-data-in-multiple-worksheets-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b

https/www.extendoffice.com/documents/excel/5017-excel-collect-data-from-multiple-sheets.html

https/www.pcworld.com/article/3239706/software/create-excel-reports-from-multiple-spreadsheets-with-multi-file-pivot-tables.html

Consider that you may need to have some interim tables to massage the data from each sheet if all are not identically structured.

Python may be useful. Google "use python to export spreadsheet from excel"

I looked at several of the resulting links - good chance that a couple of those links may be useful as well depending on the details within your spreadsheet and the sheets themselves.