0

I have over two hundred individual files I need to load into a sql server database, and unfortunately they are all coming my way as excel csv files. Excel csv files are formatted corectly, but they use an UTF-8 or UTF-16 character format (I forget which), which trips up sql server when you try and bulk insert them. I have fiddled around with different arguments for my bulk insert but I run into other problems. The simplest solution has been to simply use the clipboard to copy them into another file, because the clip board always copies as plain text.

What I would really like to do is use a command line tool to convert all the files into plain text ascii character format in one pass. I am using a windows 7 operating system. Thank you.

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
user3538411
  • 338
  • 4
  • 15
  • Possible duplicate of [Best way to convert text files between character sets?](http://stackoverflow.com/questions/64860/best-way-to-convert-text-files-between-character-sets) – Eric J. Jan 07 '16 at 16:23
  • It is likely not the most efficient method, but I believe you could use a SSIS job for this task. – TPhe Jan 07 '16 at 16:25
  • Look into using using WSH (Windows scripting host) to iterate over files in the directory and export/save them to the format you desire. There are scripts out there that should point you in the right direction. Something like this VBA will do it; just call it from your Excel object in WSH after you open the file, etc.: `ActiveWorkbook.SaveAs Filename:="c:\MyFile.csv", FileFormat:=xlCSV, CreateBackup:=False` – Marc Jan 07 '16 at 17:12
  • I'd just use powershell to `Get-Content` and then `Out-File` specifying the desired encoding. – Eric J. Price Jan 07 '16 at 20:52

0 Answers0