sql server - How to convert a group of xls 2 tab delimited files using ssis? -

-

how convert group of xls files tab delimited files using ssis ?

got script doing on google search.please sugest me how achieve using ssis

    dim objfso, objfile, objfiletsv     dim strline, strnewline, strnewtext     dim filenamelength, linelength, newfilename, linepos, quote, quotecount, totalfilesconverted      objfso = createobject("scripting.filesystemobject")     strcurpath = objfso.getabsolutepathname(".")     totalfilesconverted = 0      each objfile in objfso.getfolder(strcurpath).files         if ucase(right(objfile.name, 4)) = ".csv"             filenamelength = len(objfile.name) - 4             newfilename = left(objfile.name, filenamelength) & ".tsv"             objfile = objfso.opentextfile(objfile, 1)              until objfile.atendofstream                 strline = objfile.readline                 linelength = len(strline)                 linepos = 1                 strnewline = ""                 quote = false                 quotecount = 0                  while linepos <= linelength                     if mid(strline, linepos, 1) = "," , not quote                         strnewline = strnewline + vbtab                         quote = false                     elseif mid(strline, linepos, 1) = chr(34)                         quotecount = quotecount + 1                         if quotecount = 2 , linepos <> linelength                             if mid(strline, linepos, 2) = chr(34) & chr(34)                                 strnewline = strnewline + chr(34)                                 linepos = linepos + 1                                 quote = true                                 quotecount = 1                             else                                 quote = false                                 quotecount = 0                             end if                         else                             quote = true                         end if                     else                         strnewline = strnewline + mid(strline, linepos, 1)                     end if                     linepos = linepos + 1                 loop                 strnewtext = strnewtext & strnewline & vbcrlf             loop             objfile.close()              objfiletsv = objfso.createtextfile(newfilename)             objfiletsv.writeline(strnewtext)             totalfilesconverted = totalfilesconverted + 1             strnewtext = ""             objfiletsv.close()          end if     next      msgbox(cstr(totalfilesconverted) + " files converted csv tsv.")

it has been understood ,this can used in script task..but let me have clues

assuming there no data transformation involved, simplest way create data flow task excel source , flat file destination. should set column delimiter field "tab {t}" in flat file connection manager.

as have multiple excel files in input, can use foreach loop above process each file.


Comments

Post a Comment

Popular posts from this blog

c++ - Creating new partition disk winapi -

-
i'am trying create new partition , mount volume new, thought createfile let me this, code : lpctstr lpfilename=l"\\\\.\\device\\harddisk0\\partition3"; handle handl=createfile( lpfilename, generic_read | generic_write, file_share_read | file_share_write, null, create_always, file_attribute_normal, null ); if (handl==invalid_handle_value) { qdebug()<<"handl invalid"<<" error"<<getlasterror();} bool success = definedosdevice(ddd_raw_target_path,l"i:",l"\\device\\harddisk0\\partition3"); if(!success) qdebug()<<" definedosdevice failed "<<getlasterror(); bflag = getvolumenameforvolumemountpoint( l"i:\\", // input volume mount point or directory /** u in dir
Read more

Android Prevent Bluetooth Pairing Dialog -

-
i'm developing internal application uses bluetooth printing. want bluetooth pairing occur without user input. have managed working trapping android.bluetooth.device.action.pairing_request broadcast. in broadcast receiver call setpin method, , pairing works ok, bluetoothpairingdialog displayed second or two, disappears - see link below. https://github.com/android/platform_packages_apps_settings/blob/master/src/com/android/settings/bluetooth/bluetoothpairingdialog.java since broadcast non-ordered, can't call abortbroadcast() , , wondering if there other way prevent pairing dialog appearing. can hook window manager in way? i haven't been able come way without modifying sdk. if you're oem, it's easy (i'm on 4.3): in packages/apps/settings/androidmanifest.xml, comment intent filter pairing dialog: <activity android:name=".bluetooth.bluetoothpairingdialog" android:label="@string/bluetooth_pairing_request"
Read more

VBA function to include CDATA -

-
i have following function include cdata: function cdatasection() dim objdom domdocument dim objkmlrootelement ikmldomelement dim objkmlelement ikmldomelement dim cdata ikmldomcdatasection set objdom = new domdocument set objkmlrootelement = objdom.createelement("balloonstyle") objdom.appendchild objkmlrootelement set objkmlelement = objdom.createelement("text") objkmlrootelement.appendchild objkmlelement set cdata = objdom.createcdatasection("text") cdata.data = "<![cdata[<b>latitude = $[latitude]</b>?]]>;" end function when run above, getting error "user defined data type not found" function. yes ripster says find library reference. vba window tools>references for list of relevant references see... http://msdn.microsoft.com/en-us/library/windows/desktop/ms763701%28v=vs.85%29.aspx however, if have hard time tracking down reference, replace dim sta
Read more