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

[Martin Yergeau]

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

Regards,

[NYCBabak .]

I like the approach if and only if the user knows s/he cant modify the data structure. In other words, if they go into the Excel file and change Column names, width, order or any of the objects your MFD has defined, then your process will break.

[Martin Yergeau]

Agree Babak. I just not mentioned that I thought of having a protected Excel template for user data entry

[Franciscus van Dortmont]

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

PROFDATA CSVFILE,BRIEF

HOLD AS CSVSTATS

If the dataprofile matches your main file.

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

[Martin Yergeau]

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

Tks

[NYCBabak .]

Id say if its only one user this person can be trained not to break things. Excel is really the easiest way to fill out a bunch of rows. Id say go with your suggested plan.

[Martin Yergeau]

I dont want to keep this thread open for too long and since no other option than the Excel file seems to be the best approach, it has confirmed my thought.

I already have the habit to put strong controls around the things given to users, this one will just need little more

Thanks