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

 

[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.

 

[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