Jump to content
Macro Express Forums

Converting .xls to .csv


Recommended Posts

Where to start?

 

ME has a "Copy File" command, in which you can copy an .xls file, changing the .xls to .csv On the surface this works. You can open the file in Excel and it looks just exactly as it should. However, if you open it in notepad, you just get a bunch of gibberish. Likewise if you try to process the .csv through an ASCII File Process, the variable just gets garbage.

 

Using the "Rename File" command gives like results.

 

I know I've used these commands switching between .csv and .txt extensions, and thought for sure I had previously used them (or some other command) to switch between .xls and .csv/.txt... but I just can't seem to do it.

 

I've even tried setting a Text variable from the .xls file, and then saving the Variable to a .csv with the same failed results.

 

Can somebody please shed some light on this for me? Is it possible, if so: how? If not... why? I mean, picking apart the contents of an .xls should be a piece of cake for Macro Express... If you manually save the file as a .csv, ME tears it apart quick and easy as you please. Shouldn't there be a way (other than building a bunch of commands duplicating the manual process) to convert an .xls to something ASCII Processable?

 

Of course, I have to ask the question: Did I miss something obvious?

Link to comment
Share on other sites

Ah, man, you're preaching to the choir here! About 80% of the data I deal with daily needs to be in .csv form, which is great because ME handles .csv files so well. And, yeah, Excel outputs some FUD when trying to save to a .csv file.

 

Here's what I do:

 

1. First, set the default program for opening .csv files as Notepad and not Excel. That prevents future corruption of .csv files with Excel's crappy conversion. (I can still open .csv files with Excel, just not by accident!)

2. With existing .xls or .csv files, open them in Excel, but save them as a tab-delimited .txt file.

3. Now turn ME loose with Text File Begin/End Process, replacing all TAB characters with commas.

 

<COMMENT Value="The tab-delimited file saved from Excel."/>
<TEXT FILE BEGIN PROCESS Filename="C:\\My_text_file.txt" Start_Record="1" Process_All="TRUE" Records="1" Variable="Record"/>
<COMMENT Value="You can copy the \"invisible\" tab in the
text file, paste it into the \"Replace\" field, and replace with a comma.  Toggle \"Replace All Instances\"."/>
<VARIABLE MODIFY STRING Option="\x0F" Destination="Record" ToReplace="	" ReplaceWith="," All="TRUE" IgnoreCase="FALSE"/>
<VARIABLE MODIFY STRING Option="\x12" Destination="Record" Filename="C:\\My_converted_file.txt" CRLF="TRUE"/>
<TEXT FILE END PROCESS/>

 

4. Once all records are processed, ME can rename the converted file to "My_converted_file.csv", and I'm done.

 

Alternately, I occasionally need my .csv files with quotes around data with spaces between the commas. I do the same conversion, but replace all TABs with \",\" and then prepend and append an additional quotation mark to each record before it gets written to the converted file.

 

Clear as mud? :unsure:

Link to comment
Share on other sites

Hi Jim,

 

So I guess I'm stuck with the manual Save As option, huh?

 

In the situation I'm dealing with the source is .xls (non-negotiable), and when all is said and done, the results must be .xls (also non-negotiable). The .csv or .txt are just for the sake of processing.

 

It's not really all that difficult to make the change, but it would certainly be easier to give somebody a macro and tell them: "Press Ctrl+Shift+\ and the macro will do all the work." Than to give them the macro and tell them that they need to first save the Excel document as a Comma Delimited Text file, and then run the macro. Oh yeah, and then save the .csv back as an .xls.

 

Oh well, I guess we have to live with limitations in our lives :)

Link to comment
Share on other sites

Sorry my workaround doesn't work around your needs, Steve. :) There's always hope that Kevin'll weigh in and show us what yoyos we are and how easy it is to do...

 

I have a vague recollection of some sort of Excel-Macro Express 'solution' being in the works, looking for testers, something like that. Anyone else remember anything like that, or am I having a Monday morning hallucination? Wonder what's happening there?

Link to comment
Share on other sites

I have a vague recollection of some sort of Excel-Macro Express 'solution' being in the works, looking for testers, something like that. Anyone else remember anything like that, or am I having a Monday morning hallucination? Wonder what's happening there?

 

I do remember something along those lines. <Fingers crossed>

Link to comment
Share on other sites

You can not change a files format by simply changing the file name and the extension is simply part of that file name which associates a file to an application. I believe Joe is working on some tools that will allow one to directly access Excel data and you could use that to reach in and export. But don't forget CSV is a dumb text file so there will be some things to consider. For instance CSV does not support multiple worksheets so how are you going to deal with that? I have many macros that deal with Excel data and usually what I do is ask the user to highlight the data and copy it to thier clipboard where it is in TSV (Tab Separated Value) format. It's just like CSV except no pesky quote marks and uses tabs instead of commas. I chew thru them in memory to do my thing and usually output to a file or put the results back on the clipboard for the user. But an easier way for you is to take the Clipboard contents, save it to a file, and use ASCII File Process to do your thing. Also you can probably find a simple utility to do this to command with MEP. I once wrote a very complex macro to convert XML files to TSV but when I got large data sets it was too slow. But then I found a free little program to do it for me that required no install and was lightening fast. I'm sure you could find the same for CSV.

Link to comment
Share on other sites

I have many macros that deal with Excel data and usually what I do is ask the user to highlight the data and copy it to thier clipboard where it is in TSV (Tab Separated Value) format. It's just like CSV except no pesky quote marks and uses tabs instead of commas. I chew thru them in memory to do my thing and usually output to a file or put the results back on the clipboard for the user. But an easier way for you is to take the Clipboard contents, save it to a file, and use ASCII File Process to do your thing.

Hey Cory,

 

These are things that I've done in the past for personal projects that needed me to yank info from an Excel workbook. I was just looking for a way for somebody to process information with minimal action on their part, and as behind-the-scenes as possible.

 

Like I said, it's not hugely difficult to take a few extra steps before running the macro, but actually having the macro take those steps is kind of the point. Of course, I could just have the macro do an <Alt>fac<Tab><Enter> (with possible delays just to be safe), and then reverse the process at the end of the macro. I just wanted something a little more elegant (and sneaky).

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...