Converting Excel Macro to CSV file with double quotes

[shaswat]

Hello All,

I have a requirement where I have a excel macro file with many columns in the sheet. And in few columns it is formula based so could you please suggest how can I convert the excel macro file to a csv file with the values covered with double quotes.

Another excel macro I have doesn't have any formulas in the sheet, and I want that excel macro to be also converted into a csv file with the values covered with double quotes.

Now my last question is how to select the specific columns from the converted csv file and place them in a customized order.
Example: the order of the columns is : 1,2,3,4,5,6,7,8,9
But I want the order as: 6,2,1,9,6,8,3,4
and you can observe that I have skipped the 5,7 columns.

Please guys help me out...!!

[foxidrive]

shaswat,

If you provide the files and data and show what you need with examples, then it makes it simpler to know what you want to do and also to provide reliable code that will work in your exact situation.

[MattW76]

Your first 2 questions sound like they would be better done in Excel using VBA. Sure I can write batch code that calls VBScript to do it, but it isn't the best tool for the job. I suggest posting it in the Excel VBA secton of http://www.stackoverflow.com. Now, to answer your last question, this can be easily done in batch. It's basically the same as your previous question with more variables and tokens.

Code: Select all

@echo off
cd /d "%~dp0"
for /f "tokens=1-9 delims=," %%a in (extract.csv) do (
   echo "%%f","%%b","%%a","%%i","%%a","%%h","%%c","%%d">>New.Csv
)

If you have any columns that don't need the double quote, just remove them in the echo statement.

[shaswat]

shaswat,

If you provide the files and data and show what you need with examples, then it makes it simpler to know what you want to do and also to provide reliable code that will work in your exact situation.

Hi Guys,

Sorry for the delayed reply. I was busy in this task and tried something. It can change the .xlsm file to .CSV file but the output is complete garbage. I am attaching the code code and the input values and the garbage output that I got. Please guys help in out in solving this .

VBS Script to convert the .xlsm file to .CSV file

Code: Select all

'launch Excel and open file
Set xlObj = CreateObject("Excel.Application")

xlObj.Workbooks.Open("C:\test\excel.xlsm")
    
xlObj.Activeworkbook.SaveAs "C:\test\excelC.csv"
xlObj.Activeworkbook.Close

xlObj.Quit

Batch Script to call the VBS Script

Code: Select all

:Start
   @Echo off
   CScript testexcel.vbs
pause
:End

My Input Excel file format is: Microsoft Excel Macro-Enabled Worksheet (.xlsm)
My Output file format requirement is : .CSV

Input file values:
Name Brand Name Date
Shaswat L 1/0/1900

But after converting it to .CSV file the output I am getting is complete Garbage.
Output:
PK   ! •ÕôEµ
b  [Content_Types].xml ¢(  ÌUÉnÛ0½è?¼"´(ŠÂrizl$ý€19¶XS$Á™¸ößw$'éǪÍE‚HÍ[¸¼™^lºP­±O±Qgz¢*Œ69—úvû¹þ *bˆBŠØ¨-’º˜½~5½Ýf¤Jª#5ªe΍!Ûb¤SÆ(3‹T:`ù,K“Á®`‰æ|2yolŠŒ‘kî1Ôlú p¸ºÚÈðNÉÜGU]îþë©9oE¨YG÷IoÑ%{× ´¦\µˆÜ‹ÆrƒÌbŒ”ÙËY0Ðq¤÷®´T¨õ™Þˆõ'ú™§]Ý×}•í(Þau
…¿@'ÞÍ&˜©¬æ)­ôa~i:ªqc1èÁ¿îÀ–taPæÀÇ…˜†J2Ãëì(ÿÜòûÑ;€ÔqþBt¼}!:Þý',·Íð|þÑ`Fñ6 ØítŒ¹…‚î†%?–'ð;öˆÁ^¶ruO¼¸#üë9\—ô-ëñd–øÙ¥±þUÖ⾸‘ÀàLÒ
oî!€ûê:öøÁ¥¡<{5±ïXÝn3tÈÙO ÿÿ PK   ! µU0#ô L  _rels/.rels ¢(  ¬’MOÃ0†ïHü‡È÷ÕݐBKwAH»!T~€Iܵ£$Ý¿'TƒG½~üÊÛÝ<êÈ!öâ4¬‹;#¶w­†—úqu*&r–Fq¬áÄvÕõÕö™GJy(v½*«¸¨¡KÉß#FÓñD±Ï.W ¥†=™ZÆMYÞbø®
ÕBSí­†°·7 ê“Ï›×–¦é
?ˆ9LìÒ™ÈsbgÙ®|Èl!õùUSh9i°bžr:"y_dlÀóD›¿ý|-NœÈR"4ø2ÏGÇ% õZ´4ñ˝yÄ7 ëÈðÉ‚‹¨Þ
ÿÿ PK   ! ×êDm?
`  
xl/_rels/workbook.xml.rels ¢
(
¼”MOÃ0†ïHü‡*wš¶ãCkw !í†`ü€,u?X›Tqôßc©Ý¤.\ª^"ÙQÞ÷‘{½ù©+ïK­bú
ó@I–*ÙÇîåæžyh…JE¥Ĭd›äújý•°ô‹²ATƬ°¶yäe
µ@_7 è&Ó¦–B“óFȃȁGA°âæTƒ%gšÞ6™Ù¦ä¿krþ_[gY)áY˯”±àRTò©¥"Qar°1ëS>‘2>±˜â[› v€èSÈ»›…ænJ,„ôÝj8@giL4se"L83Lè‚YMÚ&ÛV4yý¯Å.vÙßNioižapïBޝÎ,gîÇÒU‡0u)FY_êšÿ퐱ÝqÜ‹W£?AžÌíó÷´Th{𳽘ü ÿÿ PK   ! !…+è †  xl/workbook.xml¤WQs¢H~¿ªû„óÕ hô´Ô-L¨Uq
“ËÕVQ#Œq*À¸0n’ººÿ~= Y‚{%‹/âô×_÷×ÝÃO¯Q(|ÇIJh<åë–(àا
‰ŸFâʝ6ÿ…”¡8@!ñH|éøiüûoÚ<¯)} NGâ–±Ý@’R‹#”^ÓŽáΆ&b°Lž¤t—`¤[ŒYJJ«Õ•"Db1G$U0èfC|¬Sá˜å úé–ìÒ#ZäW‹Pò¼ß5}í bMBÂÞ2PQˆüùÓ­CûU¾9"Ãßèˆø Mé†]””“<‰WnI²œ‡<nHˆïó´h·[ ˆ{ E!D)3Âp0»°¤/øǐHö»Éž„pWîtĹù?=UÑ{F¿ÛìíN³#÷Õ¦jÜL›J¿;i«=£×ïõþ¥ñ»xˤ`înIúpPU¼Aû¹ ç‘(¸T:ŠÒå5d8‰Ã¨qÐñÒÌgØÚ–‚΂¿íI‚¡¼¸ ã!ü"€Öé±­°O‘¨
¾2ÈÐׂ<èTû_ù<> ÌIäÿËÁŽ‡¼øï ~I¤“/…×ôe$*Pçí¸’o`õ’Ýz
ÛB2ûm®_¾w‡ÉÓ–Äþ"oH’2‡w
ÀˆòùŽ]´æ+N®à;ë'à]…8«#Óâ’$Ê)Û6¡Z:Y3¨²- m—ì'f sÌ¢½º´f3Ë›™‹ÏN
ˆÁ(ø)DF«qGC>C ßÈ—í—·…'!äÿsÔÉâ?
EJbðò‡V‡Dx¯a]{SÂKUG­QŠySù(ÌÒË3Þò”€$âøHüª¡6”Aã¶ÑJàŸyÑMÇõÚ†§Y³‚ý¶1½ªfÛ¹ÀVÕK~ZUÊêm}Êê—²ÛIU·»¾[M»ÀöþÛ¿ëÛêò¶j9ÍjÕ4놫š3϶ŠEÝTµ7Ža»†Îœzu=ïՏ|~Ò󓞨ÜÆóZq~A-[õSe_<UÓ¬ÕÂÇï³ÿª1iÜT›?@W]£dÝ­n=5g®ÁûZ7<¢·
¥ŒR‚©8‡y,6ÖÒ°Õ…^"tWƒÐé#§»ª­Tàt¯ÎVå™5å8ù˜¿ÂÇÑÜGÏZÌK¢÷ª©åÌʃɨêÝYÔ/u÷‚©à~®ï÷/§ž­¥yË5`~~èK¹Bkére/-§Ü–òù®6-O[^³òYAOgoŠóòÃÓ0+ÔRDç›ìlciÙ®ç>.Ë1oî¥jÏ=Õ÷é>f|º'3†ó.YVtë`ŸFpêYqøVé”\<.©Žš>ÿ΂?mÊpÀ—³·tü„ÿ ÿÿ PK   ! 6]p Í,  xl/worksheets/sheet4.xmlœZMoã6½è0t_[Ô§eÄYìv»èZÝ~œYŽ…Ø–+)Éæß—‹¶8œYxzIløiÞpøø4y÷þÛa?{©»¾ië@ÍÃ`V«vÓ×Á_~~·fýP7å¾=Öëà­îƒ÷÷?þp÷ÚvOý®®‡™Žpì×ÁnN«Å¢¯võ¡ìçí©>ê_¶mw(ýµ{\ô§®.7pÑa¿ˆÂ0[Êæœ#¬º[b´ÛmSÕŸÚêùP‡s®Þ—ƒÎ¿ß5§ÞF;T·„;”ÝÓóé]ÕN:ÄC³o†7ÌÕêËã±íʇ½÷7•”•
_¼ð‡¦êÚ¾ÝsnqNÔs±(:ÒýÔá÷n¦‹]ÿV4ÇWSÎ$XÜßm=:3%³®Þ®ƒjõq™ࢿ›úµŸ|ž™¹xhÛ'ó×Í:Møz_W¦*³Rÿ{©ª÷ûuð³2óù/D5ŸuÈÅ%æô³ÿæO§¹©·åó~ø£}ý¥nwƒKªëaʲڼ}ªûJχ¦žG©‰Zµ{Bÿ#,]Ïòüm6ÃÎ\=ÏUXĹŽÒo¦ÆJ‹¯zî‡öðψ#cDcýŒ'ó4c¥)/14ÓCÝŸ“áwãÅ6§,^&—˜Å\%a6‰X@‘ÎãR}*‡òþ®k_gZ±š¢?•Fÿj¥£ë¡a°X5N'×ëIz¹ï/ºîÕù8BtnH”\0Íz¡Öan§6`D¥ˆ{Ä8ÜÍ­1·s0æÎ÷ˆq¸—4·ž«Û¹
sˆ{ÄL¹ãæ6B»yº
ï xl/printerSettings/printerSettings1.binPK
-    ! ¢Õ¼”   —ñ docProps/app.xmlPK
-    ! ¬ Ä‚k
  aõ docProps/core.xmlPK
-    ! »SûŸ Ñ  ø xl/calcChain.xmlPK    Ðø


I really don't know how to do this. Guys please help me out.

[shaswat]

Your first 2 questions sound like they would be better done in Excel using VBA. Sure I can write batch code that calls VBScript to do it, but it isn't the best tool for the job. I suggest posting it in the Excel VBA secton of http://www.stackoverflow.com

Hi Matt,

Please find the detailed post by me for the task I am stuck with. I am following the approach with a batch script calling a VBA script. But somehow I am not getting the desired output. Please help in resolving this.

Thanks,
Shaswat

[foxidrive]

But after converting it to .CSV file the output I am getting is complete Garbage.
Output:
PK   ! •ÕôEµ
b  [Content_Types].xml ¢(

That PK at the start shows that it is a ZIP file format, which is used by modern Office apps but with different extensions.

If you provide a downloadable sample .xlsm file then I'll take a squiz.

[ShadowThief]

You're very close, but you're forgetting to include the critical FileFormat parameter.

Code: Select all

Set xlObj = CreateObject("Excel.Application")
Set wb = xlObj.workbooks.Open("C:\test\excel.xlsm")
wb.SaveAs "C:\test\excelC.csv", 6
wb.Close
xlObj.Quit

Where 6 is the XlFileFormat number for CSV, as shown here: https://msdn.microsoft.com/en-us/librar ... 98017.aspx (interestingly, using the string value "xlCSV" doesn't work).

[shaswat]

You're very close, but you're forgetting to include the critical FileFormat parameter.

Thanks a lot for your help. Now I can get the output in the CSV file format, but along with the values I am getting the excel header, that I don't want to come in my CSV file.
Current Output in CSV format:

Code: Select all

DA,AQ,AD,,SL,,MD
BD Date,Number,Dealer Name,Sub Name,Side,Deal Date,Maturity Date
10/16/2014,587621,ASDF HGT KLM,,L,,1/0/1900

Desired output in CSV format:

Code: Select all

10/16/2014,587621,ASDF HGT KLM,,L,,1/0/1900

Could you please help me in removing the first 2 lines from my CSV file using batch script. That will really help me in achieving what I want.

[foxidrive]

Code: Select all

find "/" < "filein.csv" > "fileout.csv"

[shaswat]

Code: Select all

find "/" < "filein.csv" > "fileout.csv"

Thanks a lot foxidrive. Its working absolutely fine for me, by deleting the 1st two lines of my CSV file. Now I am very near to my goal. Thank you all guys for your help, support and time contribution.

I need one more help from your end. Could you please help me out in understanding the above code. How does it works, how it deleted the 1st two lines only?

Regards,
Shaswat

[ShadowThief]

It doesn't delete the first two lines exactly, it just searches filein.csv for any line that has a "/" in it and outputs that line to fileout.csv. In this case, it simply happened to skip thee first two lines.

[Squashman]

It doesn't delete the first two lines exactly, it just searches filein.csv for any line that has a "/" in it and outputs that line to fileout.csv. In this case, it simply happened to skip thee first two lines.

Depending on the amount of lines in the file a simple fix would be to use the MORE command.

Code: Select all

more +2 file1.csv >file1new.csv

Otherwise a simple FOR /F command with the SKIP option will handle that just fine.

[Squashman]

It doesn't delete the first two lines exactly, it just searches filein.csv for any line that has a "/" in it and outputs that line to fileout.csv. In this case, it simply happened to skip thee first two lines.

Or we could just do it in your vbscript

Code: Select all

Set xlObj = CreateObject("Excel.Application")
Set wb = xlObj.workbooks.Open("C:\test\excel.xlsm")
wksht = wb.Worksheets(1)
wksht.Rows("1:2").Delete()
wb.SaveAs "C:\test\excelC.csv", 6
wb.Close
xlObj.Quit

[shaswat]

Thanks a lot ..!! I really don't belong to batch script world and got some urgent issue and upgrade related to this. You guys helped me a lot in resolving my issues. And currently I'm in learning phase of Batch Script, and that's the reason I'm unable to understand some simple changes. Sorry for troubling you all with my silly questions.