// // This is a quick and dirty JavaScript program that copies // values from a CSV file and pastes them into another program. // I used it to copy values from a CSV and paste them into Quickbooks. // This will need some significant tweaking for it to work according // for your specific needs, but this is the skeleton of the program. // Written by Zsolt Nagy-Perge // December 2022, Pensacola, Fla. // (This code was tested on Windows 11.) // try { SELF = WScript.ScriptFullName; WshShell = new ActiveXObject("WScript.Shell"); ShellApp = new ActiveXObject("Shell.Application"); } catch (e) { ALERT("ERROR\n\nPasteFromCSV.js cannot access the file system."); EXIT(0); } CURDIR = cut(SELF, "\\", 0x10010); LINES = ReadFile(CURDIR + "\\PASTE.CSV").split("\n"); TOPASTE = ""; for (i = 0; i < LINES.length; i++) { ITEMS = (LINES[i]+",,,").split(","); if (ITEMS[0].length == 0) break; for (k = 0; k < 3; k++) { WORD = Trim(tr(ITEMS[k], "0123456789., &+-/ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz")); TOPASTE += ((WORD.length) ? WORD : "") + ","; } TOPASTE += "\n"; } TOPASTE = TOPASTE.split("\n"); if (TOPASTE < 1) EXIT(0); ALERT("PLEASE CLOSE ALL OTHER WINDOWS EXCEPT QUICKBOOKS AND THIS.\nCLICK ON THE Account text box in QuickBooks, then click OK on this window.\n\nYou are about to paste these values:\n\n" + TOPASTE.join("\n")); WAIT(0.5); PRESS("%{TAB}"); // PRESS ALT+TAB to focus on QuickBooks for (i = 0; i < LINES.length; i++) { ITEMS = (LINES[i]+",,,").split(","); if (ITEMS[0].length == 0) EXIT(0); for (k = 0; k < 3; k++) { WORD = Trim(tr(ITEMS[k], "0123456789., &+-/ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz")); if (WORD.length) TYPE(WORD); PRESS("{TAB}"); // Go to next field } PRESS("{TAB}"); // Go to next line } ALERT("DONE"); EXIT(0); ////////////////////////////////////////////////// // This function splits string into two parts // along the first occurrence of substring. // Returns the first part if CMD is 0x10. // Returns the second part if CMD is 1. // If substring is not found, returns the original // string if CMD is 0x100. Ignores case when // CMD is 0x1000. Starts searching from end of // string when CMD is 0x10000. // function cut(STR, SUB, CMD) { if (typeof(CMD) === "undefined") CMD = 0x111; STR += ""; SUB += ""; var P; if (CMD & 0x1000) P = (CMD & 0x10000) ? STR.toUpperCase().lastIndexOf(SUB) : STR.toUpperCase().indexOf(SUB); else P = (CMD & 0x10000) ? STR.lastIndexOf(SUB) : STR.indexOf(SUB); $a = $b = ""; if (P < 0) return (CMD & 256) ? STR : ""; $a = STR.substr(0, P); $b = STR.slice(P + SUB.length); return (CMD & 16 ? $a : '') + (CMD & 1 ? $b : ''); } ////////////////////////////////////////////////// // v2021.3.15 // This function removes all characters from STR // that do not appear anywhere in CHARSET, forcing // string to be only made up of the given characters. // Example: tr("cabbage cake", " abc") --> "cabba ca" // [This function has not been optimized yet.] // Usage: STRING = tr(STRING, STRING) // function tr(STR, CHARSET) { STR += ""; STR = STR.split(""); for (var i = 0; i < STR.length; i++) if (CHARSET.indexOf(STR[i]) < 0) STR[i] = ""; return STR.join(""); } ////////////////////////////////////////////////// // // This function removes whitespace before and after // text T and returns a new string. // Usage: STRING = Trim(STRING) // function Trim(T) { T += ""; var i, j, x; for (x = i = j = 0, i--; x < T.length; x++) if (T.charCodeAt(x) > 32) { if (i < 0) i = x; j = x + 1; } return T.slice(i, j); } ////////////////////////////////////////////////// // v2021.4.11 // Creates and overwrites a file in binary mode. // Returns 0 on success or 1 if an error occurred. // Usage: INTEGER = CreateFile(FILENAME, STRING) // function CreateFile(FILENAME, STRING) { var LEN = STRING.length; var ASCII_Format = 0, UNICODE_Format = 1, CreateNew = 1, DoNotCreateNew = 0; try { var FSO = new ActiveXObject("Scripting.FileSystemObject"); var F = FSO.CreateTextFile(FILENAME, CreateNew, ASCII_Format); FILE.Write(ConvertFileString(STRING, 'WRITE')); FILE.Close(); FILE = FSO.GetFile(FILENAME); // Double-check what we have done. return (FILE.Size == LEN) ? 0 : 1 } catch (e) {} return 1; } ////////////////////////////////////////////////// // v2022.7.30 // Reads an entire binary file or part of a file and // returns its contents as a string. // // The second argument (START) moves the file pointer // before reading. The third argument (LENGTH) tells // how many bytes to read. When LENGTH is zero, // it reads the entire file. // // Usage: STRING = ReadFile(FILENAME, [START, [LENGTH]]) // function ReadFile(FILENAME, START, LENGTH) { START |= 0; LENGTH |= 0; var FSO, FILE, SIZE, DATA = ''; try { FSO = new ActiveXObject('Scripting.FileSystemObject'); if (!FSO.FileExists(FILENAME)) return ''; SIZE = FSO.GetFile(FILENAME).Size; if (START < 0) START = 0; if (START >= SIZE) return ''; if (LENGTH <= 0) LENGTH = SIZE; // Usage: FileObject = FSO.OpenTextFile(FILENAME, MODE, [CREATE, [FORMAT]]) // MODE : 1=Reading 2=Writing 8=Appending // FORMAT : 0=Open as ASCII -1=Open as Unicode // CREATE : 0=Don't create file 1=Create file if it doesn't exist yet FILE = FSO.OpenTextFile(FILENAME, 1, 0, 0); if (START) FILE.Skip(START); DATA = FILE.Read(LENGTH); // Read all the data we need. FILE.Close(); // Close file. } catch (e) {} return ConvertFileString(DATA, 'READ'); } ////////////////////////////////////////////////// // v2022.7.30 // The FSO.OpenTextFile() and FileObject.Read() // functions are specifically designed to work with // text files and will produce "garbage" when // trying to read binary files. // // In reality, what happens is certain bytes are // converted into 16-bit characters. And in order to // get normal data that makes sense, we have to // convert these 16-bit characters back to normal // 8-bit ASCII character set. // This conversion has to occur both after we read // binary files and before we write them. // // The MODE argument must contain the words // "READ" OR "WRITE": // // Trying to write certain binary characters to a // file using the FileObject.Write() will throw an // error unless the characters are first converted. // So, the MODE argument should be "WRITE" when // preparing a binary string for writing. // // After a string is read from a binary file, it needs // to be converted back into 8-bit characters. The MODE // argument needs contain the word "READ" // when we want to do that. // // Usage: STRING = ConvertFileString(STRING, MODE) // function ConvertFileString(STR, MODE) { MODE += ''; var ASCII_Table = ".\x8c\x8a\x9a\x9f\x8e\x9e\x9c\x83\x88\x98\x96\x97\x91\x92\x82\x93\x94\x84\x86\x87\x95\x85\x89\x8b\x9b\x80\x99", i, c, Unicode_Table = "\u0152\u0160\u0161\u0178\u017D\u017E\u0153\u0192\u02C6\u02DC\u2013\u2014\u2018\u2019\u201A\u201C\u201D\u201E\u2020\u2021\u2022\u2026\u2030\u2039\u203A\u20AC\u2122"; STR = STR.split(''); // Convert string to an array if (MODE.indexOf('READ') >= 0) { for (i = 0; i < STR.length; i++) if (STR[i].charCodeAt(0) > 255) STR[i] = ASCII_Table.charAt(Unicode_Table.indexOf(STR[i].charAt(0)) + 1); } else for (i = 0; i < STR.length; i++) { c = STR[i].charCodeAt(0); if (c > 127 && c < 160) if ((c = ASCII_Table.indexOf(STR[i].charAt(0))) > 0) STR[i] = Unicode_Table.charAt(c - 1); } return STR.join(''); } function EXIT(E) { if (typeof(E) === "undefined") E = 0; WScript.Quit(E); } function PRESS(KEYCODE) { WAIT(0.1); WshShell.SendKeys(KEYCODE); } function TYPE(TEXT) { for (var i = 0; i < TEXT.length; i++) WshShell.SendKeys("{" + TEXT.charAt(i) + "}"); } function ALERT(MSG) { WScript.Echo(MSG); } function WAIT(MS) { MS *= 500; WScript.Sleep(MS); } //////////////////////////////////////////////////