|
|
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
[Back to original message]
|