convert ACII file to SQL Server

What is the best data type to store ASCII file from a different platform? Is CONVERT function is best to use?

more ▼

asked Jun 29, 2010 at 02:20 PM in Default

cmapowers gravatar image

21 3 3 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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][3] to do that, but Perl and C# also serve very admirably and just about any modern language will get the job done.

[3]: http://www.simple-talk.com/sql/database-administration/python-for-the-sql-server-dba/
more ▼

answered Jun 29, 2010 at 03:03 PM

TimothyAWiseman gravatar image

15.5k 20 23 32

Nicely done and properly cited, Timothy! +1 I'm also a bit biased in saying that some Tally Table Split solutions along with a good ol' CrossTab can also solve some "odd" formats quite nicely. Perl script at my old company was taking 40 minutes just to prep 1 file for import. Tally Table Splitter and CrossTabs did 8 files in less than 2 minutes.
Jun 29, 2010 at 08:52 PM Jeff Moden
The Tally Table Split did not come to mind off hand, but it would work admirably as well. Thanks for adding it.
Jun 29, 2010 at 09:45 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Jun 29, 2010 at 10:41 PM

cmapowers gravatar image

21 3 3 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 29, 2010 at 02:20 PM

Seen: 508 times

Last Updated: Jun 29, 2010 at 02:20 PM