Best way for importing Statements

[silentwolf]

Hi guys,

I am new to SQL Server so please be patient with me lol )

In the past I did all my work in Access and VBA however I do like to migrate to SQL Server and am learing at present.

I need to import Statements into my application for the moment I am using C#.

What I am not sure of is what is the best way of importing Data to SQL Server.

1. I download those Statements from my Bank in CSV file format.
2. Those Statements need to be updated, some string need to be deleted as they are unnessesary
3. Some other Data need to be updated and and put into new fields


As I am from Austria we have those nice (German Umlaute) Ä,Ö,Ü and so on..

So my question is as there are so many different ways of doing stuff how would you go about it?

Would you use CSV to import into SQL Server
Or would you Change the CSV To Excel Xls or Xlsx format and work with that format

I was able to have it all worked out with VBA but with C# and SQL Server I am new and get problems with Encouding those (German Umlaute)

It would be very helpful if someone could get me into the right path so I am not running in Circles.

Many thanks for your help in advance

Albert

 

[Minty]

I have done this on a larger scale for a client.

You can take the CSV import it into a temporary access table, manipulate it then upload the corrected data directly into your SQL table.
You can automate almost all of this apart from the corrections you are making.

Link Access to your SQL Server and you can link all the tables, views, etc. and update delete and query all the data exactly as you would from an Access table.

 

[silentwolf]

Hi,

thanks for your reply!
Ok so you are using Access as a in between step so to say?

Can you run in a C# Application or better automate from a C# Application Access as well?
So automate it from within the C# Application?

Thanks

 

[Minty]

I use Access to do the whole process.
All in VBA. No need for the C#.

I browse for the file(s) from a form, and then automatically import all the data to a local staging table.
I then present the data on a form for checking, editing and marking any records I want to ignore.
I then append the records to the main data table in SQL .

If you need to edit the data I can see no advantage to using C# to build a form or otherwise to do that it would take ages compared to creating an access form to do the same.

 

[Isaac]

Hi,

thanks for your reply!
Ok so you are using Access as a in between step so to say?

Can you run in a C# Application or better automate from a C# Application Access as well?
So automate it from within the C# Application?

Thanks

If you are dead set on using c #, you can use the SQL server import export wizard to start getting used to the dialogues, and or download visual studio data tools and create an ssis package... And then you can schedule it, if you have access to sql agent.

I am not suggesting that you are necessarily going to need any c # at all, but since you seem to want to incorporate it for some reason, you will find a script task tool as one of the many tasks available on the ssis pane, you can play with that and create some c # in there if you want. It will accept a c #.net or vb.net

I agree with Minty for sure. Don't worry about transforming the data prior to its arrival in SQL server. Just manipulate it after that point.
People get way too caught up in transforming data within the ssis package prior to its arrival in the table, when there is usually no reason at all to do so. Unless you are dealing with very huge volumes of data, in which case it might be more efficient to filter out data that you don't want using ssis conditional split rather than waiting for an insert query to finish later and deleting data.

 

[silentwolf]

Hello,

many thanks to both of you for your suggestions and advice!
I will look into further and try your suggestions!

Cheers!
Albert

 

[Pat Hartman]

It is not clear what your final objective is. Are you replacing the Access application with C# in addition to converting the Jet/ACE tables to SQL Server? So, in the end there will be no Access in the application at all?

 

[silentwolf]

Hi Pat,
well yes it was a goal for me as I like to have it more as a real application rather then a access application.
But ofcourse there is alot to do much more as with access so hope that I am not taking to long to go that way.

SQL Server is pretty straigth forward and not a big problem getting a database up and running but to have it all in a working enviroment with C# as the language I guess there is alot to do.

I am just at the beginning of "development" so I am of course open for any suggestions.
As you guys have alot more experience in it.

I know that with "Runtime" installed on someones Computer you can run an Access Application without having Access installed but as I said I would like to create a more "Proper" Application with perhaps better reusability.

 

[Pat Hartman]

I like to have it more as a real application rather then a access application.

Access is a RAD tool. It is not a toy. The reason it is so effective as a development platform is because of what it does for you. It has three major limitations.
1. It can't directly be run over the internet. You can do it indirectly using Citrix and RDP but it can never be run anonymously like a Web app because there is a certain level of structure that is required in order to support the environment.
2. It is very difficult to support multiple developers. So, that limits the scope of the applications you develop. If it can be handled by one developer, fine, if you need more, there is a lot of overhead keeping everything properly tied together and tested. Even if you had one person on the team you could designate as the "build" master, it would be a difficult coordination task. It was far easier for me managing teams of a dozen programmers in the mainframe environment because we were working with a compiled language, had a real source code management tool, and we could easily substitute "stubs" that returned a valid value if we had issues with certain parts of the code base.
3. Since it is not compiled, the Access FE tends to be vulnerable to being cracked so people tend to not sell Access products because they cannot control their intellectual property but this is not a problem for internal use applications.

Notice that there is NO internal limitation on the number of concurrent users. That is because once you convert the BE from Jet/ACE to SQL Server or some other RDBMS, your concurrent user count is controlled by the number of seat licenses you own for your RDBMS. So as long as you have a rational way of distributing FE versions, you could technically support thousands of concurrent users if your SQL Server setup was sufficiently robust. All the limits people talk about are strictly caused by using Jet/ACE as your BE. They have a hard limit of 255 concurrent users and a soft limit of ~ 50. My largest installed Access FE had a user base of 125 but the concurrent user count rarely ran over ~ 40. My widest user base was San Francisco to Paris. The app was hosted in Farmington, CT (suburb of Hartford) and anyone not local used Citrix. In both cases the BE was SQL Server.

I'm sure you can create a fabulous app using C# but first you have to learn your tool. How much time and money are you or your employer willing to invest in rebuilding the Access application from scratch? I would estimate the development cost of a non trivial Access app vs some web platform at about 1-6 at best. Maybe as bad as 1-10. What you can do in 1 month will take the web team 10 months. I once made a proposal to replicate a process that was currently embedded in an Access app for wider use through out the state. They are such a PITA to work with I estimated the price at $250,000 and six months because I was going to need a part-time person to help and as I said, coordination is a real problem and that increases the time and so cost of development. They laughed at me and awarded the contract to a web firm for $3 MILLION dollars and a two year time frame. On top of that, the agencies that would be using the new app would need to incur hundreds of thousands of dollars for up front hardware costs plus tens of thousands of annual costs. It ended up taking 8 people over three years to deliver. Silly me. The sad part was it was my money the state wasted on that development effort along with the huge annual costs and total inflexibility of the end product because the schema was not properly normalized. That seems to be a pattern. Of the four Access apps I have built that were later deemed mission critical and so warranted "professional" upgrading, not one of the upgrade teams deigned to use my properly normalized schema. They all flattened the schema for some reason that is unclear to me and the four teams took 8 - 12 times the length of time I took to develop from scratch and they were all looking at a functioning application with a clearly defined spec and working code. The more people they threw at the problem, the more man-months they took to complete their version. It was like watching the "Mythical Man-Month" in operation. Stated another way if one woman can make a baby in 9 months, why can't 9 "persons" make that same baby in 1 month.

So, the bottom line is, make sure you are abandoning Access for the right reasons.

PS, when you are importing statements, it is important to ensure that the are imported consecutively and only once each. You need to log the files and using the names of the files ensure that they are imported in calendar order, not duplicated, and not any gaps.

 

[GPGeorge]

Hi Pat,
well yes it was a goal for me as I like to have it more as a real application rather then a access application.
But ofcourse there is alot to do much more as with access so hope that I am not taking to long to go that way.

SQL Server is pretty straigth forward and not a big problem getting a database up and running but to have it all in a working enviroment with C# as the language I guess there is alot to do.

I am just at the beginning of "development" so I am of course open for any suggestions.
As you guys have alot more experience in it.

I know that with "Runtime" installed on someones Computer you can run an Access Application without having Access installed but as I said I would like to create a more "Proper" Application with perhaps better reusability.

You'll put a lot of people's backs up, by throwing terms like "real application" and "Proper" application into the discussion for a variety of reasons. Pat's explained one of them. The fact of that matter is that a lot of the problems people encounter with amateur built Access databases is that the amateurs built crappy databases. They would have built crappy databases regardless of the tools they used. They're amateurs.

Okay, that's off my chest.

My favorite story along the lines of Pat's is one of the first half dozen Access relational database applications I created as an "interim" solution while IT was deciding IF they could create the application needed, and IF they had resources (developers and budget) to build it, and then if they eventually approved it, after months of development it would be ready. I was given the task of creating that Access solution to tide them over until the "real application" was available. It took me two or three weeks, but due mostly to beginner's luck, I had recruited three team leaders as my Subject Matter Experts. I relied on them both for low level details and for corrections to the high level specs upper management gave me. Turns out the team leads actually knew what their people did every day. Months later, IT held a preview of the "Proper" application. The first question anyone asked was "How do you do Task A in this application?" Answer, "You can't do that, we didn't provide that function." One of the Team Leads responded, "Why not, George's Access database does."

I got more satisfaction out of that than many later, larger project launches.

In other words, look to the development skills you bring to the table to get the job done. Magic bullets -- in the form of "real and Proper Applications" are rare and hard to acquire.

 

[silentwolf]

Hi,

thanks guys for your input!
I will keep you posted )

 

[sonic8]

As I am from Austria we have those nice (German Umlaute) Ä,Ö,Ü and so on..

Make sure you use the correct encoding/code page when reading or importing the file. Then this shouldn't be a problem at all.

Would you use CSV to import into SQL Server
Or would you Change the CSV To Excel Xls or Xlsx format and work with that format

Don't use Excel. CSV is just fine, if you got CSV files already.


If you got the CSV on your local computer...
If you are using Access the project seems to be straight-foward. Core steps:

1. Link the target SQL Server table to an Access DB.
2. Create an import spec for your CSV.
3. Import the CSV into the linked table.
4. Done.

If you are using C# it's not that difficult either, but it is certainly more effort.

1. Create a data structure for an import spec, mapping the CSV column names or indexes to SQL Server column names.
2. Write code read/write your import spec from/to disk.
3. Read the CSV file and for each line
   1. Split the data from the file.
   2. Use the import spec to map the CSV data to an .Net SqlCommand object
   3. Execute the command

 

[silentwolf]

Hi Sonic8,
thank you for the answer.
Well I read up on encoding UTF-8 or UTF-7 was what I did find in that regards but also that you need to know what encoding has been used
in the first place... Not sure if that is correct as I have found it.

So if it is true that I need to know what encoding has been used in the first place well then I am in trouble ..

What do you mean in regards of Inport Spec?

Would you have a small example of how you do it perhabs?

In regards to C# well I am not to sure if I am going that way as I do understand that it does take alot longer... Maybe I leave it for the time beeing and get it working with Access instead.

Thanks
Albert

 

[sonic8]

So if it is true that I need to know what encoding has been used in the first place well then I am in trouble ..

You can check the file for a byte order mark by opening it in a hex editor.
If there is none present, you can find the correct one by trial and error. With 99.9% certainty it's either UTF-8 or ANSI ISO-8859-1.

What do you mean in regards of Inport Spec?

Your program needs to know which CSV column to put into which database column and how the data should be interpreted (data type), as with the Import Specs in Access. This can be either hard-coded into the application, or better, be stored in a configuration file.

Unfortunately, the only relevant code I find right away, is a hot mess of hard-coded processing rules which totally obscure the core functionality. - So, no suitable example here.

 

[silentwolf]

Hi Sonic8,

Thanks for your explaination..

I will look further into it.
Unfortunatelly at the moment a short amout of time for it but will stay on it.

Cheers