What is the best data type to store ASCII file from a different platform? Is CONVERT function is best to use?
asked Jun 29 '10 at 02:20 PM in Default
I am not certain I fully understand your question, so this may be a bit generic but hopefully it will help.
First, it depends on how the ASCII file is formatted, and second you will always have multiple options.
If you are dealing with a CSV or tab-separated-values or something roughly along those lines, then SSIS is probably the easiest way to go, but bulk insert is probably the fastest (depending on details) and there are many other options to look at. If you choose to use SSIS and you have a standard CSV file, then you can simply use the wizard and it will walk you through the process.
If you are dealing with a fixed width file, then SSIS is probably still an excellent option, but you will need to spend some time configuring it for that file.
With either fixed width or separated values you can consider open data source. This may help you get started exploring that option: http://www.sqlservercentral.com/articles/OpenDataSource/61552/ . Of course, I must admit I am a bit biased about how useful that article is since I wrote it.
If you are importing an XML into SQL Server then you have lots of options depending on the details, but this Microsoft article should get you started: http://support.microsoft.com/kb/316005
Finally, if you are parsing something more exotic which is not formatted in one of the standard structured data formats or perhaps is actually unstructured data, then you are probably best off writing a custom tool to parse it as needed and pass it to SQL. I personally always reach for Python to do that, but Perl and C# also serve very admirably and just about any modern language will get the job done.
It took me a while to understand what Tally Table does. It is really a good way to process data a lot faster. Thanks both of you. That was great help.
answered Jun 29 '10 at 10:41 PM