 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
FriesWithMustard
Joined: 15 Aug 2006
Posts: 5
|
 Posted: Tue Aug 15, 2006 11:46 pm Post subject: Automating Excel Text Import Wizard |
 |
|
Hi!
Anybody out there ever try loading a CSV file into Excel using the Text Import Wizard? My file actually isn't CSV -- it's separated by the vertical bar character ('|'). When I load it using:
| Code: |
SendInput ^o
sleep 200
SendInput MyWiddleFile.BSV `r
|
It auto-magically opens the Text Import Wizard, which is fine except for I can't find a way to access the controls (i.e. the "Delimited" radiobutton, and the "Delimiters" checkboxes, etc.). Active Window doesn't see them (even when I click right on them!?!), it only reports the name and class of the Excel main window. All it shows for what is under the cursor, is the color -- which helps me NOT! 
So any ideas would be greatly appreciated 
_________________
I came to this planet for it's cheeses and because of Ali Larter |
|
| Back to top |
|
 |
BoBo
Guest
|
| Posted: Wed Aug 16, 2006 7:17 am Post subject: |
|
|
| Code: | FileRead, FileContent, MyWiddleFile.BSV
StringReplace, FileContent, FileContent, |, `;, All
FileAppend, %FileContent%, MyWiddleFile.csv
Run, excel "MyWiddleFile.csv",, max |
Not tested. |
|
| Back to top |
|
|
PhiLho
Joined: 27 Dec 2005
Posts: 6721
Location: France (near Paris)
|
| Posted: Wed Aug 16, 2006 2:54 pm Post subject: |
|
|
Oh well, another great Microsoft software, where they don't follow their own stuff... You didn't gave the Excel version. On mine, Excel 2002 SP3, I see the dialog, but not the controls inside. But you still can use the Alt+letter shortcuts. You might not see them because they are desactivated by default (another great idea by MS), but they should appear if you press Alt.
BoBo's solution would work only if there are no ; in the file to import...
_________________
 vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2") |
|
| Back to top |
|
|
FriesWithMustard
Joined: 15 Aug 2006
Posts: 5
|
| Posted: Wed Aug 16, 2006 6:14 pm Post subject: |
|
|
Oops, forgot about the version -- it's Excel 2003.
Yup -- I considered using COMMAs to separate the fields (I'm generating the file myself using iSQL and a Stored Procedure). I'm using the BAR character because the data has COMMAs in it. I tried the SEMI-COLON, but, though Excel did bypass the Text Import Wizard, it didn't use the SEMI-COLON as a delimiter [but, Bobo, great idea, just the same ] .
I also had already successfully used the Alt+letter shortcuts for the first page of the wizard [excellent suggestion, though, PhiLho ], but on the second page one has to deal with CheckBoxes and I haven't figured out how to determine initial state on those suckas  (any suggestions welcome)
I agree, Microsoft is really annoying in its lack of conformance to CUI (like when I try to use Ctrl+Tab to select MS Word instances).
Now I'm looking at using XML (unless any of you have any other ideas)
Thank you for your RAPID RESPONSEs 
_________________
I came to this planet for it's cheeses and because of Ali Larter |
|
| Back to top |
|
|
ahklerner
Joined: 26 Jun 2006
Posts: 1249
Location: USA
|
| Posted: Wed Aug 16, 2006 6:31 pm Post subject: |
|
|
@ FriesWithMustard
after replacing the "|" with "`;"
it needs to be saved as a txt file and opened with excel as such. then the import wizard should show.
send hotkey for delimeted
send alt t to uncheck tab
next
send alt m to check semicolon tab
finish
should work, not tested.
actually cant you just select other and the put the | in there? (on second page) |
|
| Back to top |
|
|
ahklerner
Joined: 26 Jun 2006
Posts: 1249
Location: USA
|
| Posted: Wed Aug 16, 2006 6:42 pm Post subject: |
|
|
| Quote: | | but on the second page one has to deal with CheckBoxes and I haven't figured out how to determine initial state on those suckas (any suggestions welcome) |
ImageSearch |
|
| Back to top |
|
|
FriesWithMustard
Joined: 15 Aug 2006
Posts: 5
|
| Posted: Wed Aug 16, 2006 7:00 pm Post subject: |
|
|
| ahklerner wrote: | @ FriesWithMustard
after replacing the "|" with "`;"
it needs to be saved as a txt file and opened with excel as such. then the import wizard should show.
send hotkey for delimeted
send alt t to uncheck tab
next
send alt m to check semicolon tab
finish
should work, not tested.
actually cant you just select other and the put the | in there? (on second page) |
Yes, but will the checkbox states always be the same? I need for this thing to be 'fool proof' because it's going to be used by 'operations personnel' who have no knowledge of Programming, or Excel, and have limited imaginations
So, I like to leave as little to fate as possible -IYKWIM
_________________
I came to this planet for it's cheeses and because of Ali Larter |
|
| Back to top |
|
|
ahklerner
Joined: 26 Jun 2006
Posts: 1249
Location: USA
|
| Posted: Wed Aug 16, 2006 7:19 pm Post subject: |
|
|
with the import wizard open,
check all boxes
do a print screen
open paint, paste there
crop out the check boxes (with the text)
save each as a bmp
use ImageSearch to look for each picture you just made 1 at a time.
if it finds the image then the box is checked.
send the appropriate hotkey to check or uncheck accordingly.
hope this is clear
Edit: not tested, may not work, as the checkboxes are not graphics....Afterthoughts...
Last edited by ahklerner on Wed Aug 16, 2006 7:23 pm; edited 1 time in total |
|
| Back to top |
|
|
ahklerner
Joined: 26 Jun 2006
Posts: 1249
Location: USA
|
| Posted: Wed Aug 16, 2006 7:20 pm Post subject: |
|
|
| Quote: | | 'operations personnel' |
I.E. - Write with crayons, just caught that....Deal with it every day... |
|
| Back to top |
|
|
PhiLho
Joined: 27 Dec 2005
Posts: 6721
Location: France (near Paris)
|
| Posted: Thu Aug 17, 2006 8:21 am Post subject: |
|
|
| FriesWithMustard wrote: | | I also had already successfully used the Alt+letter shortcuts for the first page of the wizard [excellent suggestion, though, PhiLho ], but on the second page one has to deal with CheckBoxes and I haven't figured out how to determine initial state on those suckas (any suggestions welcome) |
I couldn't remember, so I experimented: Alt+letter to put the focus on the checkbox. As you know, Space toggles the state (as does the Alt+letter), but + sets the checkbox while - unsets it... Good to know.
_________________
vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2") |
|
| Back to top |
|
|
FriesWithMustard
Joined: 15 Aug 2006
Posts: 5
|
| Posted: Wed Aug 23, 2006 5:49 pm Post subject: SpreadSheetML in conjunction with AutoHotKey to format Excel |
|
|
Thank you all for your excellent help. I was able to use XML to solve this problem (or more exactly: SpreadSheetML). Now, when I want to format some SQL output into an Excel spreadsheet, all I have to do is manually load it into Excel, "pretty it up", save it as an "XML Spreadsheet", chop up the XML file into "templates" and use AHK to re-assemble it, inserting the SQL data into the proper places (i.e. between the <Cell><Data> ... </Data></Cell> tags [case sensitive, BTW]), load it into Excel, then save it as an XLS document.
A few tricks:
* Set the ss:ExpandedRowCount attribute (in the <Table> tag) to some large number (greater than the number of Rows that you ever expect to occur) -- I usually set it to 32000
* I made the suggestion, above, to "chop up the XML file into 'templates'" – by that I mean into something like a header section, a body section, and a footer section. Then, replace the mutable fields with some kind of replacement key. For example, in the header section there are the following four fields:
| Code: |
<Created>2006-08-20T22:08:22Z</Created>
<LastSaved>2006-09-16T01:14:52Z</LastSaved>
|
Replace the Date and Time with Replacement Keys:
| Code: |
<Created>#DATE_CREATED#T#TIME_CREATED#Z</Created>
<LastSaved>#DATE_LAST_SAVED#T#TIME_LAST_SAVED#Z</LastSaved> |
In your AHK script use the following to insert the proper dates:
| Code: |
StringReplace, xml_Header, xml_Header, #DATE_CREATED#, %A_YYYY%-%A_MM%-%A_DD%
StringReplace, xml_Header, xml_Header, #TIME_CREATED#, %A_Hour%:%A_Min%:%A_Sec%
StringReplace, xml_Header, xml_Header, #DATE_LAST_SAVED#, %A_YYYY%-%A_MM%-%A_DD%
StringReplace, xml_Header, xml_Header, #TIME_LAST_SAVED#, %A_Hour%:%A_Min%:%A_Sec% |
* In the Body Section use a series of replacement keys to mark the areas where the column data goes:
| Code: |
<Cell><Data ss:Type="String">#Field1#</Data></Cell>
<Cell><Data ss:Type="Number">#Field2#</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="DateTime">#Field3#</Data></Cell>
|
Then you can once again use StringReplace in a loop to insert the actual data into the XML.
In cases where the number of cells is the same for every row (most cases, I suspect) the following is far simpler:
| Code: |
<Cell><Data ss:Type="String">|</Data></Cell>
<Cell><Data ss:Type="Number">|</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="DateTime">|</Data></Cell>
|
Just use StringSplit with the '|' char to split it into bracketing XML tags and insert the datum. Here's an example:
| Code: |
;; Read XML Worksheet Header Template and Append to Output XML File
FileRead, Contents, %A_ScriptDir%\%FilenameOfHeaderTemplate%
if not ErrorLevel ; Successfully loaded.
{
FileAppend, %Contents%, %A_ScriptDir%\%XMLOutFilename%
}
else
{
MsgBox, Unable to load Header Template.
ExitApp
}
;; Read XML Table Cells Template
FileRead, Contents, %A_ScriptDir%\%FilenameOfCellsTemplate%
StringSplit, Cells, Contents, `n,`r
;; Build the Table Rows Portion of the XML Output File filling the Cells
;; With Live Data acquired from the SQL Query
test4header := true
i := 0
Loop, Read, %A_ScriptDir%\%SqlOutFile%, %A_ScriptDir%\%XMLOutFilename%
{
++i
;; Skip lines until the last of the header is found (i.e. the ASCII underline
;; made up of dashes ['----'])
if test4header
{
ifInString, A_LoopReadLine, ----
{
;; The last of the header has been found, so turn off this test
test4header := false
continue
}
continue
}
;; Watch for the last line and when reached, exit from this loop. The last line
;; will be a report of the return status. The return status is usually equal to
;; zero, but the number is omitted (below) in case it is something else. [This is
;; not a test of the Return Status, it is a test for the End of Data]
ifInString, A_LoopReadLine, (return status =
{
break
}
;; Start an XML Row
FileAppend, %A_Space%%A_Space%%A_Space%<Row>`r`n
;; Lop off the leading and trailing '|' chars (so that the parser doesn't
;; interpret them as 'columns').
StringMid, Row, A_LoopReadLine, 2, StrLen(A_LoopReadLine) - 2
j := 1
Loop, parse, Row, `|
{
;; Get the next cell of XML Cell/Data tags
Cell := Cells%j%
;; This is where the Cell Template line is split into opening and closing
;; XML tag brackets. The '|' character is the delimiter
;; (i.e. the opening tag: <Cell><Data> and the closing tag: </Data></Cell>)
StringSplit, CellHalves, Cell, `|
;; See below for the definition of the TrimWhiteSpace function
fieldValFromSql := TrimWhiteSpace(A_LoopField)
FileAppend, %CellHalves1%
FileAppend, %fieldValFromSql%
FileAppend, %CellHalves2%`r`n
++j
}
;; End the XML Row
FileAppend, %A_Space%%A_Space%%A_Space%</Row>`r`n
}
FileRead, Contents, %A_ScriptDir%\%FilenameOfHeaderTemplate%
if not ErrorLevel ; Successfully loaded.
{
FileAppend, %Contents%, %A_ScriptDir%\%XMLOutFilename%
}
else
{
MsgBox, Unable to load Footer Template
ExitApp
}
|
Note: Though this is a simple solution, it’s a rather inefficient, so it may not be appropriate for large amounts of data.
* Trim off the leading and trailing "field separator" characters before feeding a row to the parser (i.e. Loop, Parse, or StringSplit). When the "-s" modifier is used with iSQL the fields are delimited by whatever character is used (i.e. -s "|" creates something like this: "|field1|field2|…|fieldn|". Notice that the delimiter character is placed at the beginning and at the end of each line. The AHK parser generates an empty element for both the leading delimiter character and for the trailing delimiter character. Ultimately, this would lead to a leading and trailing empty column in the SpreadSheet. I used the following to trim these off:
| Code: |
StringMid, Row, A_LoopReadLine, 2, StrLen(A_LoopReadLine) - 2
|
* If there are any 'datetime' fields in the data, they will need to be formatted like this for Excel to accept them: 'yyyy-mm-dd' with leading zeros on the month and day (e.g. 2006-08-02). Here's an example of how to format the date in SQL:
| Code: |
datename(year,@theDate)+'-'+right('0'+convert(varchar(2),datepart(month,@theDate)),2)+'-'+right('0'+datename(day,@theDate),2)
|
The "T00:00:00.000" part can be left off.
* 'Number' fields must have no whitespace before or after the number. Use the following AHK code to trim whitespace from the datum before inserting it in the XML document:
| Code: |
TrimWhiteSpace(theString)
{
AutoTrim, On
theString = %theString%
return theString
}
|
BTW: The technical definition of 'whitespace' is: characters whose printed representation is blank: space, tab, and newline, so, technically my "TrimWhiteSpace" function is incomplete since it doesn't trim the Newline character. But for the above application it works great
* Remove any Dollar Signs ('$') from Currency data (Excel wants to add its own and complains if there is already one there). I use the following to do this:
| Code: |
StringReplace, OneRowOf_SQL_Data, OneRowOf_SQL_Data, $, %A_Space%
|
I realize this is not a SpreadSheetML or a SQL forum, so if anyone is attempting this and is stumped, it is probably poor ‘etiquette’ to post your question here, so send me a message instead. It is likely that I have encountered whatever has you stymied, and I’ll help you if I can 
_________________
I came to this planet for it's cheeses and because of Ali Larter |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
