Data mapping for external sources (CSV) 2

[1DMF]

Hello,

I'm about to embark on a new commissions system.

This might be the wrong forum so I apologise if that's the case.

I need to create a table for storing all the data records, obviously, but the format (CSV) being sent to us by the various providers will not contain the same field names or perhaps even data, each provider has their own system, there own layout.

All will be providing this in a CSV format, so the file type for importing is the same.

Obviously the problem I face is the data mapping for each provider and there could be hundreds!

So i want to somehow store a 'schema' is that the right word? for the data mapping.

I was wondering if there was an industry standard way in wich you do this, some form of template method already designed for this.

Or I was going to create a template table, which had all the same column names that our actual data would have, plus a few extra for record keys and provider ID.

but all the columns are defined as text and then you could enter under each column the name of the column for that particular providers spreadsheet (CSV) file.

That way you could maintain a map which could easily be changed, added to and amended as DB fields are added to the master table.

I beleive this is what SQL does to store your table names and column names for your database (its own sys files).

Is this the correct way to do this if an exisiting method doesn't exist for simple DB mapping?

I was wondering if the import functionality in MS access allows easy creation of import templates, and how easy it would be to maintain them.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[misscrf]

Maybe look into making your backend SQL and then create a DTS package for each client? That way you can just create a client folder for each client and plop their csv in there every periodic time it comes. The DTS package can be set up on schedule with specific transformation settings.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

I was wondering about DTS, never done it before , so will be a learning curve.

Schedule is no good, need the data imported immediately the CSV is sent to us, not at some scheduled time.

But from what I read you can send DTS a message, so I'll look into it and see what i can come up with.

Cheers,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[misscrf]

Definitely. You might re-post a DTS specific question into the SQL programming forum if you get stuck on that part. DTS packages are pretty cool. I use them all the time to import and export data from and to csv's. My thought is that you would want to create a DTS package for each client and run it when you need, as you have data.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

Thanks

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

Further investigation shows DTS is rubbish.

No way to populate table columns that the source file doesn't have data for.

Trying to loop a folder to pick up multiple CSV is a nightmare just look at all the code that needs to be written..

http://www.sqldts.com/246.aspx

It's easier and quicker to do a directory lookup, loop and run transferspreadsheet method from VBA, than mess about with DTS.

Oh well , thanks for the input, shame it isn't a viable option.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[lameid]

Transferspreadsheet and Transfertext should be about equal for your purposes...

Since your field names are going to be consistent, like Field1, field2 when you import, you should be able to map the field number to a destination field name... then it is a simple matter to loop through a recordset to build the Sql statement to append the rows to an appropriate table.

 

[lameid]

Interesting, I posted and saw thread700-1574468...
Looks like Duane had the same idea for a related problem

 

[1DMF]

lol - yup, everything is coded and working sweet!

No needed for DTS, and much easier for non-techy staff to work with.





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!