question

cmapowers avatar image
cmapowers asked

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? Thanks
ascii
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
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/][1] . 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][2] 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. [1]: http://www.sqlservercentral.com/articles/OpenDataSource/61552/ [2]: http://support.microsoft.com/kb/316005 [3]: http://www.simple-talk.com/sql/database-administration/python-for-the-sql-server-dba/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image Jeff Moden commented ·
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.
1 Like 1 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
The Tally Table Split did not come to mind off hand, but it would work admirably as well. Thanks for adding it.
0 Likes 0 ·
cmapowers avatar image
cmapowers answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.