Post reply

Importing multiple Excel files with different formats

  • November 17, 2016 at 11:01 pm

    #315921

    Hi SQL experts.

    I have come accross a ugly scenario of a datamigration task where it involves multiple Excel files in different formats and containing different sheets.

    What I would like to do is to import each file and sheet into it's own table. Just because I have about 70 files, I am trying to avoid doing it one by one using SSIS. I have done some reading but cannot seem to find a solution without creating a mapping. What I would like to do is create each table based on file and sheet on the fly and store each column as varchar(max). Later on I will convert to the appropriate data type.

    The idea is to massage the data using SQL rather than Excel. It will be much quicker for me to do it this way.

    Any ideas or help is greatly appreciated.

    Thanks!

  • November 17, 2016 at 11:55 pm

    #1913296

    Is there any kind of consistency in the Excel files? Do the contents of Sheet1 always go to a specific table? If the sheet sequence and column sequences are consisent, you could probably use VBA to automate the cleaning and transformation. If you could munge the data enough so that it's not identifiable and upload a couple of examples, I might be able to work out a way of doing it. I get the feeling that it's going to be ugly. (read ugly VBA to loop columns).

    If you dumped the column names into a table, you could then map only once. (Still ugly, but better than doing 70+ manually).

  • November 27, 2016 at 9:02 pm

    #1914651

    Hi,

    Apologies for the late reply, I was on leave.

    Basically each file has a different header. At this point I cannot import straight into the destination table as I need to do some cleansing.

    What I want to achieve is to import each file into each table, but want to create the table dynamically for each file on the fly. I then want to name the table based on the file name.

    This way I can work through each table individually. I am looking for a reusable process regardless of table structure.

    I want to avoid to have to create a mapping for each file, otherwise I will be doing that forever.

    Cheers!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply