The problem is simply. Your using a data source that can be anything, you want MS to magically clean up the data for you. It won't happen. The only reliable way to do something like this is to import the data into some sort of character format, and then write the code yourself for handeling the data issues your particular data source has. You can't force the excel user to not to sent char data in a column you expect to recieve as int. MS trys to make complex things look easy by taking shortcuts. If you happen along clean enough data that fits the logic MS builds into their products then lucky you. If you want it to be reliable you have to build the logic yourself and not rely on the "wizard" type of programming that only covers about 80% of the possible problems if your lucky. If your looking here for a magic answer to cleans your data by pushing a button here it won't happen.

To try to help you I will try to restate your question and then answer it.

Question: you want to know a good way to load data from MySQL to MS-SQL.

Possible solutions: DTS, bcp, flat file, etc.

My suggestion...... Extract the data from your source into a fixed field format (never use excel because it changes your data for you automatically). DTS has issues again changing your data "trying" to fix it for you. Safest most reliable solution is to send the data as character in fixed field formats. BCP the data into a single field table. Then write the SQL code to parse the data into seperate fields adding your own logic to check if the data is int vs char vs date, etc. This requires more work but can be made more reliable.

Other solution if your newer to programming is attempt to use the various "wizard" based solutions that MS provides and hope that it changes your data in a way that you want it to be changed. Sometimes it "fixes" the data sometimes it "changes" the data but it's a crap shoot. Depends on your data. If your data is already in Mysql, I assume you have some data validation where for example a date field contains valid date data. Therefore the amount of code you need to write to get it to work should be small.

Thanks a mill rob,

Although here's the truth of the issue, although you got the concepts of what I'm try to do correct, but the source is abit wrong...I may have mentioned in the earlier posts, something about MySQL, that part has been achieved...

From now on, due to major restrictions in accessing data in the company I with, I am forced to copy data from the "web output" of data which is the ONLY way I am able to retrieve the data at this point, and paste it into excel using "paste special->values", so as not to get any web formating in the excel sheet.

Afterwhich I save the data as a CSV file (comma delimited, as this is the only way thats closest to the type of CSV file I want) After having done that, I use my upload page ive designed and I've written code to do any necessary file handling, but when it comes to putting the data into the temp tables before I update another table with the new data, it tells me that I'm trying to basically copy non-numeric data into a numeric column (overflow #).

So my solution is to now import from a flat file(like i've always done) to a table with all fields as varchar or nvarchar. It now works as far as importing into the table is concerned. I now have to take bits of data from the table and convert it to numeric values and plug it into the table to be updated. The table to be updated gets updated via 8 different CSV Files so I had to create temp tables to hold the data since I don't need all the data from the temp tables.

So my thoughts were to do the follow : after importing to the temp table I copy to the destination table, but before I actually update the data I do the ffg CAST($strVal as numeric(8,2))

This doesn't work well yet, but I'm working on it. Does this give you a better pic of what I need to do?

Thanks

Ciao

Kr33


A man's dreams are an index to his greatness!

by "Web output" are you saying your cut and pasting from the browser into excel?

If so I would suggest you try to do this to a text file if you can come up with some sort of field delimiter.

You can do it in an excel format but there are hard to control reliability issues doing it that way. Excel has a lot of wizards for fixing your data. Problem is if the wizard doesn't do what you want you don't have a choice. It only fixes the data the one way it was setup to do.

You may want to experiment with using a cut and paste to a text file. Possibly then using a tool called "sed" to format the data into a way that will reliablily be put into another source.

There are many different ways to do what you plan before I try to give you some other ideas I need to know one more thing. Is this something you want to "code" to do programitacally, or something you plan to do manualy. For example pull up the web page, highlight what you want, then cut and paste that into excel. Then load the excel to some other source? If so your toolsets and solutions change.

To answer your question rob, consider the following:

[u]THE MANUAL PART</u>
1. Highlight, copy and paste special (Values) from web output to excel sheet.
2. save as CSV (Comma Delimted) file

[u]THE PROGRAMMING PART</u>
1. Via upload page, import the CSV file (BULK INSERT with format File) into a table with all columns of type varchar.
2. Programmatically copy the relevant data to cells & rows in the target table, but convert/cast the varchar value to the desired type.

The reason for doing the programming part in this way is to avoid any upload errors and to handle the type conversion myself, thereby, hopfully, solving the problem i've been having, where I was getting a type conversion error and that the source and destination column types don't match.

Hope this was of help.

Thanks for you help.

Ciao
_____________________________________________




A man's dreams are an index to his greatness!

This can be good and it can be bad. Make sure you understand the ramifications of what you do. Excel does a lot of "auto fixing" in the process you describe. This allows you to get the data into the database you want but there are problems with that. Your basically using a wizard to fix your data problem. Although this is convenient wizards have set logic for "fixing" data and the fix it does may not be the correct fix. I would suggest try to narrow down your data by loading it in pieces to find out which row is giving you problems. Then copy that row to excel as you described and see if it "fixes" the data they way you want fixed. I find it frequently does things in ways I don't want so I prefer to process data row by row then flag failed rows as failures. Then go back see why they failed and add code that fixes them the way I want to fix them not the way the wizard wants to fix them. Either method is valid it depends on your much time you have an how important accuracy and knowledge of what the problem(s) is(are) for your needs.

Thanks for that rob,

I understand exactly what you are saying, although the data I will be using is not as sensitive as to say that its a must to check which row has been giving problems, I have done that check but although everything seems fine, i understand that there could be hidden characters which may also be there and could be causing the problem.

The way I see it, since i'm importing from a flat file, and there isn't truely a way of telling whether the data is text or number for sure, like in a programming language such as c# or java where you can specifically say that, that data is an int or char.

In that being said, my theory behind doing the mentioned method of putting the data into the tables, is that it is a sure way of getting the data into the table in the correct data type.

Despite possible, problems which I don't think would arise in a way that renders the application useless. Since the manual part of the importing of the data has a template I follow strictly.

Hope this makes some sense and seems feasible.

Thanks alot for all you help.

Ciao

A man's dreams are an index to his greatness!