December 5, 2014 at 2:31 pm
Hello there!
I'm having a bit of an issue with a project I'm working on presently. Originally, I was supposed to be importing Excel files placed into a folder with unpredictable names, but consistent format. That was easy enough, and that's handled.
However, the project has now been expanded; I need to also import CSV files, still with unpredictable names (easy), but both the Excel and CSV files can now have unpredictable columns.
For example, I was originally told I'd be working with columns A,B,C,D,E, but now I'm working with files that can have that format, or A,B,F,G,C,D,E, or any variation as such.
For the Excel files, I can easily just do a SELECT * INTO... with a staging table and OPENROWSET, so that's no problem. The CSVs, however, I'm not so lucky with; I can't seem to use OPENROWSET for it. I could use BULK INSERT, but I can't use it in the form of a SELECT * INTO...
Compounding this is that there can be typos in the column headers, which I also just ran into 😀
Am I missing something here? Is there a means of easily adapting to the varying column header names and positions? If so, please let me know! Thanks for having a look 🙂
- 😀
December 5, 2014 at 3:02 pm
DOH! I think I was staring at the problem for way too long :-P. I missed an obvious solution; my BULK INSERT was skipping the first row, since it was the header row. Instead of skipping it, I can include it, use some numbering to identify it, split the row by delimiter, and then create the table with the split-out headers.
Sometimes, a step away for a bit is all you need 😀
- 😀
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply