Jump to content

Hi community, I am looking for some suggestions regarding a ...

Martin Yergeau

Recommended Posts

Hi community,

I am looking for some suggestions regarding a data load that I have to develop.

Actually, the load is done by a BI programmer that receive an Excel file from the user.

This Excel file contain 37 columns and as many rows as the user placed in.

Each rows are different by one column value or more.

In a sense its like if 34 of the 37 columns are keys.

The programmer has to perform a quick structure validation, remove possible blank rows and then use SQL Import/Export Wizard to load the data into a SQL table.

The data loaded in the SQL table is then used somewhere else in BI.

The objective is to transfer the load/validation process on users side.

Meaning that the above process must not rely anymore on IT.

I know that MODIFY can be used but it may be too much painful for user if they have to enter each row one by one when with Excel they can do copy/paste as many time they want to then only change a column value.

I have defined a mdf on the Excel. So I thought that the user may be able to save the Excel file at a specific location and then have an app that will read from the mdf, validate and store the data in the sql table.

I think that this second option is the best one but I am wondering if you have any other suggestion to share


Link to comment
Share on other sites

In my experience there seem to be a 1001 ways to corrupt an Excelfile. Perhaps its easier to store the data in csv so you can do some regex expressions in bash on it (do you have Linux)

Then I would create a synonym for the file (CREATE SYNONYM FOR) not sure if that works with CSV. Once you have a master for the upload you can check with



If the dataprofile matches your main file.

After that you can append the data to your main file.

Link to comment
Share on other sites

100% agree with you Frans that having a user corrupting the file is possible in 1001 ways.

We dont have Linux available.

The thing is that I need to find a simple way for the user (which, to be honest will always be the same guy until he quit or get retired )

So, having him inputting in an Excel file seems to be the simplest way.

Better than asking to then same his Excel file in csv format : and how to be sure that he will use the good csv format !

But I keep your suggestion in mind


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...