DosTips.com

A Forum all about DOS Batch
https://www.dostips.com/forum/

Converting Excel Macro to CSV file with double quotes

https://www.dostips.com/forum/viewtopic.php?f=3&t=6634

Page 1 of 1

Converting Excel Macro to CSV file with double quotes

Posted: 30 Aug 2015 07:43
by 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...!!

Re: Converting Excel Macro to CSV file with double quotes

Posted: 30 Aug 2015 08:49
by 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.

Re: Converting Excel Macro to CSV file with double quotes

Posted: 31 Aug 2015 08:08
by 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.

Re: Converting Excel Macro to CSV file with double quotes

Posted: 15 Sep 2015 08:01
by shaswat
foxidrive wrote: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.

Re: Converting Excel Macro to CSV file with double quotes

Posted: 15 Sep 2015 08:04
by shaswat
MattW76 wrote: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

Re: Converting Excel Macro to CSV file with double quotes

Posted: 15 Sep 2015 19:00
by foxidrive
shaswat wrote: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.

Re: Converting Excel Macro to CSV file with double quotes

Posted: 15 Sep 2015 19:05
by 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).

Re: Converting Excel Macro to CSV file with double quotes

Posted: 16 Sep 2015 03:42
by shaswat
ShadowThief wrote: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.

Re: Converting Excel Macro to CSV file with double quotes

Posted: 16 Sep 2015 04:26
by foxidrive

Code: Select all

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

Re: Converting Excel Macro to CSV file with double quotes

Posted: 16 Sep 2015 08:34
by shaswat
foxidrive wrote:

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

Re: Converting Excel Macro to CSV file with double quotes

Posted: 16 Sep 2015 09:15
by 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.

Re: Converting Excel Macro to CSV file with double quotes

Posted: 16 Sep 2015 09:37
by Squashman
ShadowThief wrote: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.

Re: Converting Excel Macro to CSV file with double quotes

Posted: 16 Sep 2015 10:15
by Squashman
ShadowThief wrote: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

Re: Converting Excel Macro to CSV file with double quotes

Posted: 16 Sep 2015 10:46
by shaswat
Squashman wrote:
ShadowThief wrote:

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.
All times are UTC-06:00
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/