You are here: Re: Import flat file into SQL Server 2005 Express « MsSQL Server « IT news, forums, messages
Re: Import flat file into SQL Server 2005 Express

Posted by Bernard Peek on 01/06/07 22:10

In message <Xns98B096E86FA00Yazorman@127.0.0.1>, Erland Sommarskog
<esquel@sommarskog.se> writes
> (cabrenner@optonline.net) writes:
>> I am new to SQL Server, and migrating part of an Access application to
>> SSE. I am trying to insert a comma delimited file into SSE 2005. I am
>> able to run a BULK INSERT statement on a simple file, specifying the
>> field (,) and row (\n) terminators. I can also do the same with a
>> format file.
>>
>> Here is the problem. My csv file has 185 columns, with a mixture of
>> datatypes. Sometimes, a text field will contain the field delimiter as
>> part of the string. In this case (and only in this case) there will be
>> double quotes around the string to indicate that the comma is part of
>> the field, and not a delimiter.
>
>So a file could look like this:
>
> 2,34,Enter Sandman,Pat Boone
> 9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
> 8,981,"Rebel, Rebel",David Bowie
>
>There is now way to get BULK INSERT to handle this file in that shape.
>If I were faced with this file, I would write Perl script that replaced
>the commas outside the "" with a different delimiter and then removed the
>"". And it would not be trivial.
>
>Most other people would probably try to write a package in Integration
>Services, but I have never used Integration Services myself. And for your
>part - SQL Express does not come with Integration Services, I believe.

Two things to add, both useful options if the amount of data is small.
First, the import filters in MS Access are better than those in SQL
Server. If the data will fit into an Access table that might just do the
trick. Second, spreadsheets have more flexible parsing options than
databases. It may be possible to load the data into a spreadsheet. That
allows different algorithms to be applied to different rows.

Lastly, text files can be opened and read by VBA code in any of the
office languages, or any of the .NET languages. Either could be used,
but writing code to cope with all of the possible options may take time.


--
Bernard Peek
back in search of cognoscenti

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация